SQL Server Geeks

how well is my system performing? - DBCC SQLPERF

DBCC SQLPERF is one of the most important undocumented SQL Server performance tuning pearls that allows you to answer the question: "how well is my system performing". DBCC SQLPERF provides different kinds of statistical data which is gathered by the system at runtime. This data can be used to analyze and evaluate your system and pinpoint possible bottlenecks.

This article tries to provide a complete set of documentation; describing how to use DBCC SQLPERF, what information is provided through the different options, which shape the data is returned in and how to use/interpret the data.

DBCC SQLPERF can be called in two ways. The first way it will return a result set of statistical data, the shape and content of result set depends on the key value provided. The second way resets the statistics values (to zero) so you can start measuring again from that point onwards, until the values get reset they are accumulated since the start of the server.

Returning the gathered statistics/data:
DBCC SQLPERF(<key>) [WITH { [NO_INFOMSGS] , [TABLERESULTS] } ]

Reset the data structures used to gather the statistics/data to zero:
DBCC SQLPERF(<key>, clear) [WITH { [NO_INFOMSGS] } ]

The WITH clause helps you control the output format, there are two WITH clauses that can be applied:

• NO_INFOMSGS: which will suppress the "DBCC execution completed. If DBCC printed error messages, contact your system administrator." message

• TABLERESULTS: which will cause the output of the DBCC command to be in the form of a result set, so you can re-use it latter. The DBCC SQLPERF options always return a result set, so this option is already implicitly set. I prefer to still make it explicit, which is why you see me use it in my examples.

DBCC SQLPERF can be called with different <key> values; each key value returns content and shape of data. The allowed key values are described in the table below.

Description of the key values:

Supported SQL Server versions:

DBCC SQLPERF is only support by SQL Server 7.0 and later versions, before 7.0 the information provided by DBCC SQLPERF is only available through a DBCC extension DLL created by Microsoft Product Support Services, which laid the foundation of DBCC SQLPERF in its current format. In SQL Server 2005, most information that you can only get through DBCC SQLPERF in 2000, can be retrieved via the new dynamic management views.

It is time to secure your DB Environment

Mengapa perlu?

SQL server adalah merupakan database server yang didalamnya kita menyimpan informasi data berupa database object seperti table, view, store procedure, function trigger dll. Dalam berbagai kasus pengamanan database server kurang terperhatikan dan cendrung sangat sekali minim dilakukan

Sebagai best practice dari system keamanan suatu system kita perlu mengetahui konsep dasar dari system keaman system yaitu defence in depth yang terdiri dari komponen berikut ini:

• Policies, Procedure & Awareness
• Physical Security
• Perimeter security
• Internal Network security
• Host/Server Security
• Application Security
• Data Security

Untuk mengetahui adakah update yang tertinggal dan belum di apply gunakan MBSA sehingga kita mengetahui patch atau service pack apa saja yang belum terinstall

Matikan semua service yang tidak diperlukan seperti:

• MSSQLServer
• SQLServerAgent
• MSSQLServerADHelper
• Microsoft Search
• Microsoft DTC

Database Security Category

Pengamanan database server merupakan syarat mutlak bagi aplikasi yang digunakan untuk mengolah data production. Gambar berikut menunjukkan category security yang perlu dilakukan terhadap database server.

1. Pengamanan Network Pengamanan netowork ini bertujuan untuk mengamankan jaringan/network dari kemungkinan akses oleh orang yang tidak berhak dan bermaksud mengacaukan keamanan database server kita. Pengaman ini dapat berupa:

Restrict SQL to TCP/IP
Meliputi pengontrolan siapa saja yang bisa mengakses database server dengan menggunakan IP Secure Policy. Dimana hanya computer dengan IP terdaftar saja yang dapat mengakses database server. Dalam kasus web application maka hanya web server saja yang bisa akses database server. Akses dari computer lain yang tidak terdaftar akan di tolak

Hardening TCP/IP Stack
Proses ini dilakukan untuk memastikan TCP/IP yang digunakan secure dari kemungkinan gangguan keamanan

Restrict Port
Melakukan blocking terhadap semua port kecuali SQL Server port dan port-port yang diperlukan untuk authentication. Selain itu IP Sec harus di configure untuk membatasi akses hanya port SQL saja yang bisa di akses yaitu Port 1433 dan 1434.

2. Pengamanan System operasi

   Pengaman ini dilakukan guna memastikan bahwa data yang ada di database server dari sisi system operasi akan tersimpan dengan aman tanpa adanya gangguan terhadap keamanan data. Hal yang perlu dilakukan di level database server adalah:

• Configure SQL Server Service agar dijalankan diatas user account dengan hak aksess (Permission) yang seminim mungkin. Ini ditujukan agar jangan sampai sql service dijalankan oleh account dengan role administrator. Sehingga memiliki hak akses yang sangat tinggi.
• Delete atau disable user account yang tidak digunakan, misalnya account guest atau local account yang tidak ada hubungannya dengan applikasi yang menggunakan database maka harus dihapus atau di disable dari sql server.
• Secure  Authetication traffic, dengan cara melakukan konfigurasi windows agar dapat menggunakan windows Integrated authentication (NTLM)
• Secure File and directories, perlu diperhatikan *** tentang keamanan dalam penyimpanan data berupa file data dan directory di database server dengan cara: 
• pastikan bahwa sqlserver terinstall di SQL Service installation directories. 
• Pastikan bahwa everyone group user tidak memiliki hak akses guna mengkases sql server file (mdf file) 
• Pastikan bahwa registry key telah terkonfigurasi dengan Access Control List yang tepat. 
• Remove semua sharing folder yang tidak butuhkan 
• Remove password yang ada di log file dengan menggunakan tools KillPwd.exe 
• Remove semua tools, utilities dan SDK bahkan development tools sepertti Visual studio dari database server.

3. Login dan User Role

   Ada beberapa hal yang perlu menjadi catatan penting sebagai best practice dalam mengamankan SQL Server sebagai database server adalah sebagai berikut:

• Gunakan strong password untuk sa (system administrator) agar tidak mudah di tebak. Strong password ini bisanya terdiri dari kombinasi huruf dan angka dengan carakter khusus seperti (!@#$%^&*()) dengan panjang tidak boleh kurang dari 5 character. 
• Remove Guest Account dari user di SQL server 
• Remove BUILTIN\Administrator user dari SQL Sever 
• Jangan melakukan grand permission terhadap public role

SQL Authentication Best Practice

Untuk mengamankan akses terhadap database server kita, maka kita perlu pastikan mekanisme authentication untuk database diserver production kita memenuhi syarat sebagai berikut:  

1. Set Windows Authentication menggunakan windows only dan bukan Mixmode, dengan penjelasan sebagai berikut:
   • bahwa credential dari windows only tidak dipassing melalui network
   • security dengan menggunakan windows only akan mudah di manage
   • dengan window authentication memungkinkan credential bisa di deligate
   • menghilangkan keperluan menyimpan user id dan password di client (seperti penulisan user id dan password untuk connection string).
    
2. Secure Database object, hal ini perlu dilakukan guna memastikan bahwa setiap object database (Table, view, Store procedure, Function dan trigger) hanya diakses oleh user yang berhak saja. Khususnya jika dalam satu database server terdapat lebih dari 1 database yang sama sama digunakan sebagai data production. Adapun best practice untuk melakukan secure database objects adalah sebagai berikut :

• Remove sample database dari database server
• Restrict access terhadap store procedure, dengan cara :
• membuat SQL Login kemudian
• map login to database user
• Add database user kedalam user define DB Role kemudian grant permission terhadap DB Role Tersebut.
• Restrict cmdExec access hanya untuk sysadmin role

3. Gunakan View dan Store procedure, ini pelu dilakukan karena
1. SQL Query mungkin mengandung informasi confidential sehingga perlu di hide seperti misalnya:

• Nama dari database component (Nama field, nama table) 
• Relasi antara table serta process logic 
• Account name serta password

2. Gunakan Store procedure seintensif mungkin karena selain menyembunyikan logic yang ada. SP membuat proses eksekusi lebih cepat dibandingkan query biasa.
3. Sebaiknya view lebih banyak digunakan daripada mengakses table secara langsung. Ini ada kaitannya juga dengan kemanan dalam hal menyembunyikan informasi dan relasi dari table yang ada.
4. Gunakan security best practice khususnya untuk aplikasi yang jalan di atas web application (secure code)

Demikian secara singkat bagaimana kita mengamankan database yang ada di SQL Server kita dengan memanfaatkan best practice untuk mengamankan database server khususnya untuk data production (kn-sqlgeeks).

Must Know : OLAP v.s OLTP

SQL Server 2008 - Date time data type

Permasalahan date time menjadi masalah serius dalam pengelolaan database, sering kali kita mengalami masalah yang semestinya tidak terjadi karena adanya anomali data akibat proses operasi tipe data date time ini. permasalahan mulai timbul ketika kita memiliki kebutuhan seperti :

• Memisahkan date dengan Time, mengingat secara standar tipe data selalu datetime
• Range tanggal yang lebih luas
• Bisa sesuai dengan kebutuhan Time Zone
• Persisi sampai nano second
• dsb

akhir nya di SQL 2008 dilakukan inprovement dengan menyediakan 4 tipe data datetime yang baru yaitu

1. Date

• Date Only
• Range dari 0001-01-01 sampai dengan 9999-01-01 menggunakan kalender gregorian

2. Time (n)

• Time Only
• optional persisi sampai 100 nanoseconds

3. DateTimeOffset(n)

• UTC Datetime dapat mengadaptasi kebutuhan TimeZone
• optional persisi sampai 100 nanoseconds

4. DateTime2

• Ukuran Date Range yang cukup besar
• optional persisi sampai 100 nanoseconds

Berikut contoh dan perbedaan data datetime yang di tunjukkan dalam Script TSQL di SQL Server 2008

CREATE TABLE t1 (c1 DATE, c2 TIME(3),
    c3 DATETIME2(7) NOT NULL DEFAULT GETDATE(),
    c4 DATETIMEOFFSET CHECK
 (c4<CAST(GETDATE() AS DATETIMEOFFSET(0)))
);

INSERT INTO t1 VALUES ('0001-01-01', '23:59:59',
    '0001-12-21 23:59:59.1234567',
    '0001-10-21 23:59:59.1234567 -07:00');

INSERT INTO t1 VALUES ('9999-12-31', '23:59:59',
    '9999-12-31 23:59:59.1234567',
    '1111-10-21 23:59:59.1234567 -07:00');

SELECT c4,
    DATEPART(TZOFFSET, c4),
    DATEPART(ISO_WEEK, c4),
    DATEPART(MICROSECOND, c4) FROM t1;

copy paste TSQL Script di atas untuk bisa melihat perbedaannya, jangan lupa jalankan di SQL Server 2008