SQL Geeks Indonesia

explore - brainstorm - share
See also: Other Geeks@INDC

News

where is the news

Community Web Site

SQL Server Indonesia Community Monthly Meeting
Rekan Sekalian,
 
Kami mengundang kehadiran rekan-rekan dalam SQLServer-Indo monthly meeting yang akan diadakan pada:
 
Kamis, 27 Oktober 2011
Jam: 19.00 - 21.00
Tempat; Auditorium Microsoft Indonesia, Gedung BEJ Lantai 18 - Jakarta
 
Ada 2 topik yang akan dishare dalam pertemuan ini:
 
Master Data Services di SQL Server 2008 R2
Pembicara: Hendra Eka Putra, MVP  (TBC)
 
SQL Server 2008 Guide for SharePoint Administrator
Membahas teknik maintenance dan tuning database SQL Server yang digunakan sebagai backend SharePoint.
Pembicara; Choirul Amri, Moderator SQLServer-Indo
 
Untuk reservasi/pendaftaran, silakan kirim email ke sqlserverindo at live dot com.
 
Note: Ada beberapa godies dan doorprize menarik di acara tersebut, pastikan anda hadir untuk mendapat hadiah utama Windows 7 Home Premium!
 
 
//salam
Moderator SQLServer-Indo


Share this post: | | | |
Selamat Tinggal 2010, Selamat datang 2011

Tidak terasa waktu terus berjalan, penghujung tahun 2010 telah tiba dan fajar 2011 segera menjelang.

sudah banyak hal dan kejadian yang kita alami selama tahun 2010 ini, baik yang sifatnya suka maupun duka. yang memperkaya pengalaman hidup kita hingga kita semakin kuat dan tegar dalam mengarungi kehidupan ini.

dalam dunia IT sendiri banyak pengalaman baru yang bisa saya timba, dengan terlibat dalam team baru dengan sekala project baru. membuat hidup jadi lebih hidup

pengalaman adalah guru yang terbaik, setiap langkah pasti mengandung arti. dan meninggalkan jejak bagi pengalaman hidup kita

semoga tahun 2011 menjadi awal yang lebih baik lagi, semakin maju dengan semangat baru dan kesuksesan baru

 

Share this post: | | | |
Posted: Dec 31 2010, 11:15 PM by kiki | with 1 comment(s)
Filed under:
Selamat Datang SQL Server 'Denali'

Code Name 'DENALI' merupakan versi yang palin mutakhir dari SQL Server. dengan menunjukkan beberapa enhancement yang lebih mutakhir dari terdahulunya menyebabkan DENALI menjanjikan kelebihan dan sisi kompetitifnya dengan pesaing yang ada di pasaran

dari referensi yang bisa kita baca di MSDN, jelas sekali enhancement yang dimiliki Denali ini, enhancement ini dilakukan di 3 area penting dalam system database SQL Server yaitu pada

- Proses Instalasi

- Database Engine

- Intergration Service

berikut referensi link yang bisa di jadikan acuan untuk mengetahui lebih jauh ke handalan SQL Server DENALI Ini 

Share this post: | | | |
Posted: Dec 17 2010, 06:25 PM by kiki | with 2 comment(s)
Filed under:
The Role of a Project Manager (from http://www.tenstep.com)

Here is some of good articles for Project Manager, it is usefull to open your mind about this role

this story can explain why people would rather choose to other role then become a project manager

"A new employee in the company mailroom noticed an older man sitting in the corner, sorting mail, weighing packages, adding postage and doing other simple jobs. He asked his supervisor who the man was.

That's Joe." the supervisor said. "He has been with the company for 35 years and is getting close to retirement."

"Really." the new employee replied. "And he's been in the mailroom the whole time?"

"No, he left a number of years ago. But he asked for a transfer back - after spending several years as a project manager." 

Some of key thing to become good project manager from tenstep.com.

In general, the project manager is responsible for the overall success of the project. In some companies, this person might be called a Project Coordinator, or a Team Leader, however, the key aspect is that the person is responsible for ensuring the success of the project.

What does it take for the project to be a success? If you follow the TenStep Project Management Process® (www.TenStep.com), or a similar approach, you first must define the project and build the schedule. This is where the project manager's responsibilities start. If the project begins and you find out later that you are not clear on scope, the project manager is the one who is accountable. If your project is executing a poor schedule, the project manager is accountable.

The work around defining the project means that you understand and gain agreement on the overall objectives, scope, risk, approach, budget, etc. It also includes defining or adopting the specific project management procedures that will be used to manage the project. 

This does not mean that the project manager must do all this work themselves. There may be an entire team of people helping to create the Project Charter and schedule. However, if something does not go right, the project manager is accountable.

Once the project starts, the project manager must successfully manage and control the work, including:

  • Identifying, tracking managing and resolving project issues

  • Proactively disseminating project information to all stakeholders

  • Identifying, managing and mitigating project risk

  • Ensuring that the solution is of acceptable quality

  • Proactively managing scope to ensure that only what was agreed to is delivered, unless changes are approved through scope management

  • Defining and collecting metrics to give a sense for how the project is progressing and whether the deliverables produced are acceptable

  • Managing the overall schedule to ensure work is assigned and completed on time and within budget

and I have learn about all usefull things in here http://www.lifecyclestep.com/open/407.1TheRoleoftheProjectManager.htm

 

Share this post: | | | |
Posted: Jul 20 2010, 04:22 PM by kiki | with 3 comment(s)
Filed under:
SQL Server Base Practice - Kumpulan Link yang bermanfaat

Kadang kita kesulitan untuk mendapatkan referensi dan best practice untuk SQL server di internet, setelah beberapa waktu mencoba mencari link yang berguna berupa artikel yang menarik dan berguna di internet berikut ini beberapa link yang bisa di jadikan acuan untuk best practise SQL Server

Share this post: | | | |
Posted: Jun 24 2010, 03:40 PM by kiki | with 1 comment(s) |
Filed under:
DICARI : Db Developer... ada yang minat ?

Sudah beberapa minggu ini, saya mencari kiri kanan teman yang berminat untuk bergabung di team sebagai Db Developer. sempat beberapa waktu lalu interview orang untuk posisi ini. namun sepertinya masih belum sesuai dengan harapan. setelah meminta beberapa kali meminta pada resource manager di kantor dan ternyata tidak mendapatkan hasil yang memuaskan, akhirnya saya putuskan untuk membuka kesempatan ini langsung ke teman teman di komunitas SQL Indonesia

Posisi DB developer ini sendiri sangat menarik, skillnya tidak terlalu developer dan tidak terlalu DB, sehingga masih memungkinkan mixing skill antara dev dan db. berikut job desc yang saya cari

Database Developer
Required Skill
- Having Min 2 years experience in Developing Database using SQL Server
- Must be familiar with Data Modeling/Relational Database Design
- Experience creating and implementing physical databases from Logical Data Models
- Experience in analyzing and writing complex queries and stored procedures (SQL/TSQL)
- Good Experience with large databases and/or data warehousing
- Good experience in database development  skill (i.e. normalization, referential integrity, stored procedure, Trigger)
- Some experience as an SQL DBA
- Must have strong skills in Microsoft SQL Server 2005/2008 including. 
         SQL Server Integrated Services (SSIS)
         SQL Server Analysis Services (SSAS) 
         SQL Server Reporting Services (SSRS)
- Familiar with Microsoft Database project in SQL Server 2005/2008
- Strong interpersonal skills to resolve problems in a professional manner, lead working groups, negotiate and create consensus
 
bagi teman teman yang berminat dengan kesempatan ini silahkan kirim email ke kiki.rizki.noviandi@live.com, mudah mudahan kita berjodoh

Share this post: | | | |
Posted: May 17 2010, 12:13 PM by kiki | with no comments
Filed under:
Menjadi pembicara di Mixed Community Lauch Visual Studio 2010 Faculty Summit UGM

Kamis besok (13 Mai 2010) saya berkesempatan untuk mengisi acara Visual Studio 2010 faculty summit di UGM, kesempatan sengat berharga ini selain bisa memberikan sharing pada Komunitas IT dan Akademisi di UGM mengenai SQL Server 2008 R2 sekaligus menjadi pembicara ke tiga setelah Luke Loban (Senior Program Manager F#) dari Microsoft Corp.

Visual Studio 2010 Faculty Summit – Universitas Gadjah Mada

13.00 - 14.30 Introduction to F# and Parallel Programming
Luke Hoban, Senior Program Manager F#, Visual Studio 2010, Microsoft Corp
 
14.30 - 15.15 Introducing Windows Phone 7 Development
Erick Kurniawan, Microsoft Most Valueable Professional
 
15.15 - 16.00 Business Intelligence with SQL Server 2008 R2 and Visual Studio 2010
Kiki Noviandi, Microsoft Most Valueable Professional

Acara ini merupakan bagian dari rangkaian acara event Community Lauch Visual Studio 2010 di sejumlah kota di tanah air, berikut jadwal Mixed Community Lauch Visual Studio 2010 seperti tercantum pada  web site Microsoft.com http://msdn.microsoft.com/id-id/ff629034.aspx

Rangkaian Jadwal Event

Visual Studio 2010 Cianjur Launch

Waktu: Sabtu 23 April 2010, 08.30 s/d 16.30
Tempat: Bale Rancage,Cianjur
Pembicara: Narenda Wicaksono, Aris Lesmana, Ronald Rajagukguk, Agus Kurniawan, Rully Yulian, Agam Fuja Kurniawan, Tutang MM, Fistman Marpaung


Visual Studio 2010 Sukabumi Launch

Waktu: Sabtu 8 Mei 2010, 08.30 s/d 16.30
Tempat: Aula Akademi Citra Buana Indonesia
Pembicara: Narenda Wicaksono, Aris Lesmana, Ronald Rajagukguk, Agus Kurniawan, Rully Yulian, Agam Fuja Kurniawan, Tutang MM, Fistman Marpaung, Andri Yadi


Microsoft ISV Innovation Day(Jakarta Launch)

Waktu: Selasa 11 Mei 2010, 08.30 s/d 17.00
Tempat: Balai Kartini, Jl. Gatot Subroto, Jakarta
Innovation Day 2010 kali ini diadakan bersamaan dengan pelaksanaan final kompetisi lokal Imagine Cup 2010 (kategori Software Design). Untuk agenda lebih lengkap silakan kunjungi situs resmi ISV Innovation Day 2010 berikut.


Visual Studio 2010 Faculty Summit – Universitas Indonesia

Waktu: Rabu 12 Mei 2010, 13.30 s/d 16.30
Tempat: Universitas Indonesia, Depok
Pembicara: Luke Hoban, Narenda Wicaksono, Agus Kurniawan, Ronald Rajagukguk


Visual Studio 2010 - Yogyakarta Community Launch

Waktu: Kamis 13 Mei 2010, 08.30 s/d 13.00
Tempat: Auditorium MM UGM, Jalan Kaliurang km 4 Yogyakarta
Pembicara: Luke Hoban, Ridi Ferdiana, Risman Adnan, Narenda Wicaksono, Ronald Rajagukguk


Visual Studio 2010 Faculty Summit – Universitas Gadjah Mada

Waktu: Kamis 13 Mei 2010, 13.00 s/d 16.00
Tempat: Auditorium MM UGM, Jalan Kaliurang km 4 Yogyakarta
Pembicara: Luke Hoban, Erick Kurniawan, Kiki Noviandi


Visual Studio 2010 - Bali Community Launch

Waktu: Sabtu 15 Mei 2010, 08.00 s/d 16.30
Tempat: STIKOM Bali - Convention Center
Pembicara: Luke Hoban, Risman Adnan, Ronald Rajagukguk


Visual Studio 2010 - Surabaya Launch

Waktu: Senin 17 Mei 2010, 08.30 s/d 13.30
Tempat: Restauran Agis, Jalan Wisma Pagesangan 197, Surabaya
Pembicara: Luke Hoban, John Muljana, Theodorus Mitsutama, Narenda Wicaksono, Ronald Rajagukguk, PT IAO


Visual Studio 2010 - Bandung Community Launch

Waktu: Selasa 18 Mei 2010, 08.00 s/d 13.00
Tempat: Ruangan Mas Soeharto, Wahana Bhakti Pos Lantai 8, Jalan Riau Bandung
Pembicara: Luke Hoban, Narenda Wicaksono, Firstman Marpaung, Ronald Rajagukguk


Visual Studio 2010 Faculty Summit – Bandung

Waktu: Selasa 18 Mei 2010, 13.00 s/d 16.30
Tempat: Ruangan Mas Soeharto, Wahana Bhakti Pos Lantai 8, Jalan Riau Bandung
Pembicara: Luke Hoban, Rully Yulian, Andri Yadi


Visual Studio 2010 - Medan Community Launch

Waktu: Sabtu 5 Juni 2010, 08.00 s/d 15.00
Tempat: Convention Hall Uni Plaza, Jl. M.T. Haryono - Medan
Pembicara: Ronald Rajagukguk, Raymond Engelbert, Arfan Arlanda

Share this post: | | | |
MSF Agile - memanfaatkan metodology dan tools

Beberapa hari ini, saya kembali turun gunung dalam team Software development dengan menerapkan konsep MSF Agile. metoda ini memungkinkan kita bisa melakukan rapid application developmen dengan menggunakan pola deployment secara iteratif yang tidak sama dengan pendekatan water fall.

fase - fase yang terdapat di MSF agile hampir sama dengan methoda software development pada umumnya, namun yang membedakan adalah dalam hal pemanfaatan agile methodology yang memungkinkan software di buat secara modular tanpa harus menunggu proses requirement dan design secara lengkap

fase2 pada msf diantaranya

1. Envisioning, kegiatan envisioning berhubungan dengan mengumpulkan infomasi fisi dari user dan harapan mereka atas sistem yang akan di buat dan menuangkan nya dalam bentuk requirement document yang biasa di sebut juga sebagai vision document

2. Planning, kegiatan pada fase ini adalah melakukan proses perencanaan dan desain dari semua kebutuhan user kedalam spesifikasi pekerjaan yang akan kita kerjakan yang di sebut juga sebagai Functional Spec. banyak cara untuk menuangkan notasi FS ini pada umumnya kita menjelaskan FS ini dengan cara naratif dan di bantu dengan tools pemodelan seperti UML. dari factional spec ini akan di terjemahkan menjadi technical spec yang lebih menitik beratkan bagaimana menjawab kebutuhan user ini dalam bahasa teknis seperti Object Model, Relation Model dan Software architecture Model

3. Developing, Pada tahap ini Dokument Technical spec akan mulai dikerjakan oleh team development. sesuai dengan spesifikasi teknis yang telah di tentukan

4. Stabilizing, tester dan QA akan melakukan proses testing secara intensif dengan menggunakan mekanisme daily build yang memungkinkan setiap error dan kesalahan dapat di deteksi dan di perbaiki sedari dini. proses Bug Finding dan Bug Fixing akan jalan terus menerus sehingga proses stabilizing selesai dan bug trend yang di hasilkan semakin minim

5. Deploy, setelah lolos dan berhasil mencapai Bug Free aplikasi akan di deploy ke mesin UAT atau production untuk di coba oleh user

Semua step ini bisa bekerja dengan sangat efektif jika kita menggunakan tools yang bisa membantu proses ini, dan microsoft memberikan kemudahan untuk MSF Agile ini dengan menggunakan Visual Studio team system.

Sebelumnya saya pernah menggunakan VSTS 2005, dan menurut saya sangat membantu dalam implementasi Software development. menggunakan VSTS 2008 merupakan pengalaman menarik, karena semakin banyak hal baru yang bisa saya temukan khususnya dalam membantu proses implementasi Software development dengan MSF Agile.

pengalaman saya dengan VSTS 2008 ini tentu saja akan saya share di tempat ini, khususnya yang kaitannya dengan penggunaanya untuk membantu team database.

nantikan pengalaman saya di blog ini

Share this post: | | | |
Database Trouble Shooting, how to recover suspect database in SQL
One of Basic knowladge to understand and doing trouble shooting in SQL Server platform is understanding Database Status, each status is store on master.dbo.sysdatabases object. and the database status in SQL Server defined as
State Definition
ONLINE Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
OFFLINE Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
RESTORING One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
RECOVERING Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.
RECOVERY PENDING SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
SUSPECT At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.
EMERGENCY User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.
 

When the database status is suspected, it might be from trouble of Infra structure or hardware that impacted to SQL Server database performance.

Here are some step by step to recover database that have been suspended by the system

1.       Look in the SQL Server Logs in Enterprise Manager(EM), There should be multiple logs starting with Current and then followed by 6 or more archived logs.

2.       Review all recent Error Log(s). There WILL be an indication here as to why the database has been marked suspect.

3.       When the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server.

4.       To change the database status we will us the following store procedure: sp_resetstatus.
The steps are as follows:

 

5.       Change database status to allow update

        USE master
        GO
        sp_configure 'allow updates', 1
        GO
        RECONFIGURE WITH OVERRIDE
        GO
   
        sp_resetstatus
        GO
        -- Example: sp_resetstatus ‘Washington’

6.       After the procedure is run, immediately disable updates to the system tables:

        sp_configure 'allow updates', 0
        GO
        RECONFIGURE WITH OVERRIDE
        GO


If database is rolling back to suspect than you should change db status to emergency mode, you have to extract all data out from database using BULK Copy or DTS, Note that the data may be corrupt or transactionally inconsistent

1.     Issue the following command to put the database into emergency mode:

   a.    USE master
        GO
        sp_configure 'allow updates', 1
        GO
        RECONFIGURE WITH OVERRIDE
        GO
 
   b.    UPDATE master..sysdatabases
        SET status = 32768
        WHERE name = 'DatabaseName'

2.     Stop and Restart ALL SQL Server Services.

We are now ready to pull whatever data we can out of the tables in the corrupt database. Remember, some tables may be corrupt, thus you may have to play with various T-SQL statements to get the data out. First try DTS

Share this post: | | | |
Posted: Feb 27 2010, 01:05 PM by kiki | with 1 comment(s)
Filed under: ,
SQL Server Monthly Meeting - Feb 10

 SQL Server Indo Monthly Meeting - Feb 2010

Share this post: | | | |
Database Unit testing with SQL Server 2005

As you seen on my previous articles in my blog,  Unit testing is very importan in software development. But people still not aware about this because developer commonly only focus on how to develop the application and deprioritize the quality of code it self. Unit testing will make sure system behaviour and response from every parameter input that given by user will compliant with the function spesification.

To help developer doing unit testing in stabilizing phase, Visual Studio team system for database already have capabilty to create and develop module for unit testing.

The framework in Microsoft Visual Studio 2005 Team Edition for Database Professionals (DB Pro) for database unit testing, however, offers some important value-add features above and beyond those just mentioned. The first of these is automatic generation of SQL script stubs of unit tests for stored procedures, functions, and triggers. This feature saves you the hassle of developing the boilerplate code that you see earlier in this paper.

Secondly, DB Pro provides a set of built-in test conditions to help you verify your test results. These test conditions perform the most common validation that you would want to verify, including the rows returned, scalar values, and execution time. You can easily configure these test conditions through the user interface.

And, most importantly, DB Pro offers a way for you to set the database state by using the data-generation feature to populate the database with test data before running your database tests. The beauty of the data-generation functionality is that you can repeatedly generate the same test data based on a seed value, making it repeatable and thus very applicable for unit testing.

We will explore each of these features in this paper and how best to leverage them in implementing your process for database unit testing.

Authoring Your First Database Unit Test

To get you started, the following simple walkthrough shows how you can create a database unit test for the CustOrderHist stored procedure in the Northwind database.

  1. Ensure that Team Edition for Database Professionals is installed on your computer.
  2. Open Microsoft Visual Studio.
  3. On the Test menu, click New Test.
  4. In the Add New Test dialog box, click Database Unit Test, and pick the type of test project that you want to create (either C# or Visual Basic).

    Figure 1. Add New Test dialog box

  5. Specify a name for the new test project.

    Figure 2. New Test Project dialog box

  6. Specify the database connection against which to run the test, and click OK.

    Figure 3. Database test configuration

    The Database Unit Test Designer appears.

    Click here for larger image

    Figure 4. Database Unit Test Designer (Click on the picture for a larger image)

  7. Add a test method by clicking the plus sign (+) on the top of the designer.

    Figure 5. Adding new database unit test

  8. Name the new test, and click OK.

    Figure 6. Naming new database unit test

  9. Add the following T-SQL to the main editor window in the designer:
    DECLARE @CustomerId nchar(5)
    SELECT @CustomerId = 'EASTC'
    EXEC dbo.CustOrderHist @CustomerId
    
  10. Click the inconclusive test condition in the Test Conditions panel, in the bottom half of the designer. Click the red "x" button to delete the test condition.

    Figure 7. Test Conditions panel

  11. Add a row-count test condition by clicking Row Count in the Test Conditions list and clicking the + button.

    Figure 8. Adding row-count test condition

  12. In the Properties window, set the number of expected rows to 19.

    Figure 9. Configuring test-condition properties

  13. On the Test menu, point to Windows, and click Test View.

    Figure 10. Running test from Test View

  14. Right-click the test, and click Run Selection.
  15. Review the results in the Test Results window.

    Figure 11. Viewing test results

And your test passed!

You have just successfully created your first database unit test. Let's now drill into the details of the various phases of database unit testing.

source : http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx#dtbunttsttedp_topic1

Share this post: | | | |
Database Unit Testing, is it sound familiar for you?

have you heard about Database Unit Testing?

Unit testing is a well-understood concept in application development, but the database community has not yet embraced the advantages and strategies of this approach. Therefore, I'd like to start by exploring the fundamental tenets of the unit-testing methodology. Unit testing provides a structured and automated way of testing individual components of a system. Unit tests are most often authored by the developer of the component that is being tested. Each unit test tests a specific module of the code in an isolated fashion to ensure that the component behaves as expected.

How does this all relate to database development? The direct analog of application unit tests in the database world are tests of a database's programmability objects. These objects include, for example, a database's stored procedures, functions, and triggers.

What might a unit test for a stored procedure look like? Let's say that you are trying to test the CustOrderHist stored procedure in the Northwind database. The stored procedure should return the order history for a given customer ID. To test this behavior, you can imagine writing a SQL script that executed the stored procedure and then verified whether the expected number of rows was returned. Such a script might resemble the following:

DECLARE @CustomerId nchar(5)
SELECT @CustomerId = 'EASTC'
EXEC dbo.CustOrderHist @CustomerId

IF (@@ROWCOUNT <> 19)
RAISERROR('Actual Rowcount not equal to expected 19',11,1)

As a methodology, unit testing has many advantages over manual, ad-hoc testing and debugging. By developing database unit tests, you can create a collection of tests and run them during development to ensure that your features work as you expect. Because each unit test focuses specifically on an individual method, you can more easily determine the source of a failure for a failing unit test. Therefore, database unit tests help you determine the sources of bugs in your code.

Such a collection of tests is very useful for regression testing. As you implement new features, you can rerun existing tests to ensure that existing functionality has not been broken. Such a regression test suite facilitates database changes, because you can now make changes knowing the implications of those changes.

Unit tests, in addition, serve as documentation for users of the methods under test. Developers can quickly review unit tests to determine exactly how particular components should be consumed.

Database unit testing is not limited merely to testing the database's programmability objects. You might want to author the four classes of tests that this section describes.

Feature Tests

The first and likely most prevalent class of database unit test is a feature test. In my mind, feature tests test the core features—or APIs, if you will—of your database from the database consumer's perspective. Testing a database's programmability objects is the mainline scenario here. So, testing all the stored procedures, functions, and triggers inside your database constitute feature tests in my mind. To test a stored procedure, you would execute the stored procedure and verify that either the expected results were returned or the appropriate behavior occurred. However, you can test more than just these types of objects. You can imagine wanting to ensure that a view, for example, return the appropriate calculation from a computed column. As you can see, the possibilities in this realm are large.

Schema Tests

One of the most critical aspects of a database is its schema, and testing to ensure that it behaves as expected is another important class of database unit tests. Here, you will often want to ensure that a view returns the expected set of columns of the appropriate data type in the appropriate order. You might want to ensure that your database does, in fact, contain the 1,000 tables that you expect.

Security Tests

In today's day and age, the security of the data that is stored within the database is critical. Thus, another important class of database unit tests are those that test the database security. Here, you will want to ensure that particular users exist in your database and that they are assigned the appropriate permissions. You will often want to create negative tests that attempt to retrieve data from restricted tables or views and ensure that the access is appropriately denied.

Stock-Data Tests

Many databases contain stock data, or seed data. This data changes infrequently and is often used as lookup data for applications or end users. ZIP codes and their associated cities and states are great examples of this kind of data. Therefore, it is useful to create tests to ensure that your stock data does, in fact, exist in your database.

 source : http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx#dtbunttsttedp_topic1

Share this post: | | | |
Connection String (connect to Excel File)

Sambil mengingat ngingat jaman dulu ketika memulai pemrogram dengan mengkases database. ternyata hal yang paling penting dan sering dilupakan adalah ketika kita akan melakukan koneksi kesebuah data source (database server). Ada banyak connection provider yang harus kita hapalkan Dengan cara penulisan yang spesifik dalam satu parameter yang orang kenal dengan CONNECTION STRING.

Connection StringSekarang ini orang lebih mudah melakukan koneksi kepada sebuah data source karena hampir sebagian besar Development tools menyediakan fasilitas untuk koneksi ke database yang kita inginkan, namun pekerjaan ini akan menjadi susah ketika kita di paksa untuk melakukannya dengan scripting seperti di VB Script, atau Java Script. sehingga kita perlu mengingat cara penulisan connection providernya, security model yang di gunakan dan mekanisme koneksinya

Berikut saya coba tuliskan kumpulan connection string yang biasanya paling sering di cari berdasarkan connection providernya mudah-mudahan bisa membantu kita dalam mengakses data source dari berbagai jenis database

Connection String Untuk MS Excel 2007

Pada bagian pertama ini akan di jelaskan beberapa metoda penulisan connection string untuk mengakses data di Microsoft Excel 2007, dengan menggunakan ACE OLE DB Connection. metoda ini lebih cepat dari pada menggunakan office component library

A. XLSX File  

XLSX file adalah nama extention file untuk MS Excel 2007, untuk mengkases MS Excel 2007 file bisa menggunakan  connection string berikut ini :

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

dengan nilai HDR = Yes jika menghendaki kolom pertama sebagai nama field dari data yang kita akses di file excel tersebut

 

B. Mengakses data Excel File dalam format Text

Kita bisa memperlakukan semua data dalam XLSX file tadi sebagai data dalam format text dan mengabaikan format dari setiap cell yang ada, hal ini dilakukan jika dalam file excel yang kita miliki memiliki type data yang berbeda beda dalam setiap cell nya, berikut contoh connection stringnya

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Parameter IMEX = 1 menyatakan bahwa semua data yang diambil akan di anggap sebagai Text

 

 C. Mengakses XLsb File

MS Excel 2007 memiliki file dengan format dan extention baru *.XLSB, yang menyimpan data dalam Office Open XML binary format. data disimpan dalam format binary sehingga berbeda dengan format XLSX, format XLSB ini  bukan merupakan text sehingga tidak bisa diview dan dari sisi performace jauh lebih baik dari XLSX. berikut connection stringnya

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;Extended Properties="Excel 12.0;HDR=YES";

 

D. Mengakses XLSM File

Format extention XMSL merupakan office open XML format dengan Macro Enable, berbeda dengan connection string yang adalah pada statement Properties="Excel 12.0 Macro...

Berikut contoh penulisan connection string untuk mengakses XLSM file

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES";

 

Demikian mudah mudahan tulisan contoh connection string ini bisa memerikan wawasan tambahan untuk teman teman yang memerlukan akses ke berbagai macam database. masih banyak lagi tentang contoh dan format penulisan connection string, yang insya alloh akan saya lanjutkan dan lengkapi lagi (KN-SQLG)

 

Share this post: | | | |
Change data capture, fitur SQL 2008 yang sangat berguna...

Kemarin buka buka lagi catatan waktu ikut TPrep Project Untuk  SQL Server 2008 di Redmond Tahun yang lalu, ini saya lakukan karena memang Hari Kamis kemarin (22/10/2009) berkesempatan untuk membawakan session microsoft di Acara Metro Data Solution Day di Hotel Shangrila Jakarta. Kebetulan materi yang akan di bawakan berkaitan dengan fitur - fitur SQL yang berhubungan dengan pengolahan data dalam ukuran besan sehingga judul besar dari session yang saya adalah SQL Data Warehouse Penta Byte Data Solutions.  

Saya coba share sedikit apa yang saya presentasikan di blog saya ini, khususnya fitur-fitur yang berhungan dengan pengelolaan data yang memiliki ukuran sangat besar dengan memanfaatkan fitur fitur terbaru dari MS SQL 2008.

Change data capture adalah topik pertama yang saya sampaikan disini, dimana fitur baru di SQL Server 2008 ini memudahkan kita untuk melakukan capturing terhadap perubahan yang terjadi bada sebuah table dalam sebuah database. Dengan CDC ini memungkin semua operasi yang dilakukan pada sebuah table bisa kita tracking historynya dari operasi yang dilakukan, dari mulai data pertama kali di masukkan, perubahan terhadap data di setiap kolom hingga data terakhir yang ada pada sebuah table

untuk membuat CDC ini, kita bisa lakukan di SQL Server 2008 dengan terlebih dahulu mengaktifkan fitur CDC pada sebuah database dengan perintah berikut ini

EXEC sys.sp_cdc_enable_db;

GO

Perintah ini akan mengaktifkan fitur CDC di database yang kita akan buatkan Change Data Capture-nya, ini bisa dilihat dari adanya file tambahan pada database kita di bagian system table seperti

  • cdc.captured_columns
  • cdc.change_tables
  • cdc.ddl_history
  • cdc.index_columns
  • cdc.lsn_time_mapping

Dengan CDC ini memungkinkan kita untuk dapat melakukan pencatatan terhadap perubahan data yang terjadi tanpa harus membuat table auditlog sendiri maupun membuat mekanisme trigger untuk mencatat sejarah perubahan data baik oleh aplikasi maupun oleh DBA secara back end

Berikut contoh scipt untuk fitur change data capture di SQL Server 2008, silahkan mencoba

--set current database context
USE [AdventureWorks]


GO


--append new user column to the HumanResources.Employee Table
ALTER TABLE humanresources.employee
ADD [User] 
NVARCHAR(50)

GO

--disable any triggers
DISABLE Trigger ALL ON HumanResources.employee

GO

--enable CDC for AW
EXEC 
Sp_cdc_enable_db

GO

--enable CDC for HumanResources.Employee
EXEC 
Sp_cdc_enable_table
  humanresources ,
  employee ,
  NULL ,
  1 ,
  dbo

GO

--create sample UDF to return change data
CREATE FUNCTION [dbo].
[Udf_employee]
               (@start_time 
DATETIME,
                @end_time   
DATETIME)
RETURNS @Employee TABLE(employeeid       
INT,
                        nationalidnumber 
NVARCHAR(15),
                        contactid        
INT,
                        managerid        
INT,
                        title            
NVARCHAR(50),
                        birthdate        
DATETIME,
                        maritalstatus    
NCHAR(1),
                        gender           
NCHAR(1),
                        hiredate         
DATETIME,
                        salariedflag     
[FLAG],
                        vacationhours    
SMALLINT,
                        sickleavehours   
SMALLINT,
                        currentflag      
[FLAG],
                        rowguid          
UNIQUEIDENTIFIER,
                        [user]           
NVARCHAR(50),
                        cdc_operation    
VARCHAR(1))
AS
  BEGIN
    
--declare local variables to hold LSNs
    DECLARE  @from_lsn 
BINARY(10),
             @to_lsn   
BINARY(10)
    
    
--Map the time interval to a change data capture query range.
    IF (@start_time IS NULL)
      BEGIN
        SELECT @from_lsn = sys.
Fn_cdc_get_min_lsn('HumanResources_Employee')
      END
    ELSE
      BEGIN
        SELECT @from_lsn = sys.
Fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)
      END
    
    IF (@end_time IS NULL)
      BEGIN
        SELECT @to_lsn = sys.
Fn_cdc_get_max_lsn()
      END
    ELSE
      BEGIN
        SELECT @to_lsn = sys.
Fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)
      END
    
    
--if same then exit
    IF (@from_lsn = sys.
Fn_cdc_increment_lsn(@to_lsn))
      BEGIN
        RETURN
      END
    
    
-- Query for change data
    INSERT INTO @Employee
    SELECT employeeid,
           nationalidnumber,
           contactid,
           managerid,
           title,
           birthdate,
           maritalstatus,
           gender,
           hiredate,
           salariedflag,
           vacationhours,
           sickleavehours,
           currentflag,
           rowguid,
           [user],
           CASE __$operation
             WHEN 1
             THEN 'D'
             WHEN 2
             THEN 'I'
             WHEN 4
             THEN 'U'
             ELSE NULL
           END AS cdc_operation
    FROM   cdc.
Fn_cdc_get_net_changes_humanresources_employee(@from_lsn,@to_lsn,'all')
    
    RETURN
  END

GO

--sample DML statement
UPDATE humanresources.employee
SET    title = 'Marketing Director',
       [User] = system_user
WHERE  employeeid = 13

--get the net change of the sample DML including the User
SELECT *
FROM   [dbo].[Udf_employee](NULL,NULL)

Share this post: | | | |
TSQL Enhancement in SQL Server 2008

Meski sepertinya sudah banyak yang tahu bahwa produk terbaru dari microsoft yaitu SQL Server 2008 memiliki kehandalan dalam menangani data dalam skala enterprise, namun tidak ada salahnya kita sedikit perhatikan perubahan mendasar dalam hal Syntax TSQL yang terbaru di SQL Server 2008 ini.

Beberapa fitur yang sangat membantu developer dalam hal kemudahan melakukan scripting di database diantaranya adalah adanya type data baru dan perintah SQL baru yang banyak membantu database developer khususnya bagi mereka yang memiliki latar belakang dari programming

Berikut beberapa Enhancement dari TSQL di SQL Server 2008

A. TSQL "Delighters"

terdapat kemampuan untuk melakukan perintah berikut dalam script TSQL di SQL 2008

DECLARE @t int = 5; -- ini tidak bisa dilakukan di versi sebelumnya

INSERT dbo.myT
 VALUES (‘WA’, @t), (‘FL’, @t+1); --ini tidak bisa dilakukan di versi sebelumnya


UPDATE dbo.myT
 SET instances+=1; --ini tidak bisa dilakukan di versi sebelumnya

B. Table Value Parameter

Memungkinkan kita untuk mengirimkan para meter data berupa table, yang bisa mengurangi rountrip dalam proses pengiriman data dari aplikasi ke server, karena bisa dilakukan dalam 1 kali pemanggilan SP

create proc ins_data (@t table (a int)) as … -- Declarasi SP seperti ini tidak bisa dilakukan pada versi sebelumnya

Berikut contoh implementasi Table value paramenter

USE AdventureWorks
GO

-- Declarasikan Table Value type EmployeeTable
CREATE TYPE EmployeeTableType AS TABLE
(EmpID INT, EmpName nvarchar(100), EmpEmail nvarchar(100))

USE AdventureWorks
GO

-- Buat Procedure yang menggunakan Parameter dengan type EmployeeTableType
CREATE PROCEDURE NewEmployee(@EmployeeDetails EmployeeTableType READONLY)
As
BEGIN
  INSERT INTO dbo.Employee
  SELECT * FROM @EmployeeDetails
END

-- Simpan data di table value type

use AdventureWorks
Go
DECLARE @NewEmployees EmployeeTableType

INSERT INTO @NewEmployees
VALUES(1,'John McLean','JohnMcLean@contoso.com')

INSERT INTO @NewEmployees
VALUES(2,'Bob Smith','BobSmith@contoso.com')

INSERT INTO @NewEmployees
VALUES(3,'Ted Connery','TedConnery@contoso.com')

--- Panggil SP

EXECUTE NewEmployee @NewEmployees

C. MERGE Statement

Merge di gunakan untuk menangani proses UPSERT (UPDATE or INSERT) terhadap suatu table, perintah ini menyederhanakan perintah IF Exist yang selama ini di gunakan untuk melakukan syncronisasi data. skenario umumnya jika data sudah ada maka akan di update diantaranya

  • OLTP insert-or-update (UPSERT)
    • -> UPDATE if row exists, INSERT otherwise 
  • Synchronize two tables
    • -> INSERT/UPDATE/DELETE rows in the target table based on differences with source
  • Tracking history of slowly changing dimensions
    • -> UPDATE existing rows as not current, INSERT new data
  • Tracking inventory
    • -> INSERT new stock, UPDATE existing stock, DELETE when amount is zero 

berikut Syntax MERGE Statement

[ WITH <common_table_expression> [ ,…n ] ]
MERGE
 [ TOP (expression) [ PERCENT ] ]
 [ INTO ] <target_table> [ [ AS ] table_alias  ] [ WITH( <merge_hint> ) ]
 USING <table_source>
 ON <search_condition>
 <merge_clause> [ …n ]
 [ OUTPUT <dml_select_list> ]
 [ OPTION ( <query_hint> [ ,…n ] ) ]
;
<merge_clause>:=
{
   WHEN MATCHED [ AND <search_condition> ]
  THEN { UPDATE SET <set_clause> | DELETE }
 | WHEN [ TARGET ] NOT MATCHED [ AND <search_condition> ]
  THEN INSERT [ (column_list) ]
   { VALUES (values_list) | DEFAULT VALUES }
 | WHEN SOURCE NOT MATCHED [ AND <search_condition> ]
  THEN { UPDATE SET <set_clause> | DELETE }
}

Contoh penggunaan MERGE untuk sinkronisasi data

CREATE TABLE Original (o_pk INT PRIMARY KEY, o_name VARCHAR(10), o_number INT);
CREATE TABLE Replica (r_pk INT PRIMARY KEY, r_name VARCHAR(10), r_number INT);

CREATE PROCEDURE usp_SyncReplica  AS
 MERGE Replica
  USING Original
  ON o_pk = r_pk
  WHEN MATCHED
   AND (o_name != r_name OR o_number != r_number) THEN
   -- Row exists but data differs
   UPDATE SET r_name = o_name, r_number = o_number
  WHEN SOURCE NOT MATCHED THEN
   -- Row exists in Replica but not Original
   DELETE
   WHEN NOT MATCHED THEN
   -- Row exists in Original but not Replica    INSERT VALUES (o_pk, o_name, o_number)
  OUTPUT $action, inserted.r_pk, deleted.r_pk;

Selamat Mencoba.

(KN-MvpSQL)
 

Share this post: | | | |
Posted: Oct 10 2009, 11:56 AM by kiki | with no comments
Filed under:
More Posts Next page »