SQL Server knowledge center

Database Integrity Check with SMO

Kasim.Wirama — Tue, 26 Jan 2010 09:38:00 GMT

Database Integrity Check with SMO

By : Kasim Wirama, MCITP, MCDBA

The last series of SMO postings, I would highlight one of SMO capabilities in term of database administration. For DBA, database integrity check is one of must-have database maintenance activities that should become part of database maintenance to ensure database optimal performance and reduce possible data corruption that would reduce database availability. Regarding to SMO capability, SMO provides several integrity check under SMO Database object. The methods are related to database integrity check. They are :

1. CheckAllocations

This method is equivalent to T-SQL command : DBCC CHECKALLOC

2. CheckAllocationsDataOnly

This method is equivalent to T-SQL command : DBCC CHECKALLOC (‘databasename’, NOINDEX)

3. CheckCatalog

This method is equivalent to T-SQL command : DBCC CHECKCATALOG

4. CheckTables

This method is equivalent to T-SQL command : DBCC CHECKDB

5. CheckTablesDataOnly

This method is equivalent to T-SQL command : DBCC CHECKDB (‘databasename’, NOINDEX)

Example of SMO integrity check functions is shown below:

Server srv = new Server(“(local)”);
Database db = srv.Databases[“AdventureWorks2008”];

StringCollection results = db.CheckCatalog();

Foreach ( string result in results)
{
Console.Writeline (result);
}

If there is no issue on database integrity (for example : AdventureWorks2008 database on your local machine), it would give message : “DBCC execution completed. If DBCC printed error messages, contact your system administrator”.

Share this post: | | | |

Performing Database Restore by SMO

Kasim.Wirama — Sat, 23 Jan 2010 12:44:00 GMT

Performing Database Restore by SMO

By : Kasim Wirama, MCITP, MCDBA

This series of SMO posting, I would like to show how to do database restore with SMO. Backup operation is represented by Microsoft.SqlServer.Management.Smo.Restore class. The example I show here is how to use the SMO Restore class by doing full AdventureWorks2008 database restore on my local SQL Server Machine, so let’s get started by creating windows form application in Visual Studio 2008.

Add 4 references : Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc, Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended. Double click on Form1 to switch into code behind page and put 2 namespace declaration here : using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;create 1 button, 1 label, and 1 progress bar control on the form, and create click event handler of the button and put code here :progressBar1.value = 0;
progressBar1.Maximum = 100;

Restore rst = new Restore();rst.Action = RestoreActionType.Database;
rst.Database = “AdventureWorks2008”;
rst.Devices.AddDevice (@”C:\AdventureWorks2008.bak”, DeviceType.File);
rst.ReplaceDatabase = true;
rst.PercentCompleteNotification = 10;
rst.PercentComplete += new PercentCompleteEventHandler(restore_PercentComplete);Server srv = new Server(“(local)”);
rst.SqlRestore (srv); Let’s go through code steps above. Create instance of SMO Restore class. Define restore type and specify database you would like to restore (for example AdventureWorks2008). Specify restore filename by adding filename into restore device with FILE device type enumeration value.If you would like to replace existing database, put value true into ReplaceDatabase property of the restore instance. If you would like to specify restore progress you could set PercentCompleteNotification property to 10 or any number you prefer; in this sample the 10 means that you instruct the restore object to let you know restore progress for each 10% increment. And specify eventhandler for each completion 10% incremental steps.2 remaining last steps is define SQL Server and run restore by issuing method : SqlRestore on the SQL Server.You could set restore progress value into label and progress bar control so that user could see backup progress inside the event handler (restore_PercentComplete). In the event handler put the code below :

progressBar1.value = e.Percent;
label1.Text = e.Percent.ToString() + “ % complete”;

Share this post: | | | |

Performing Database Backup by SMO

Kasim.Wirama — Fri, 22 Jan 2010 13:00:00 GMT

Performing Database Backup by SMO

By : Kasim Wirama, MCITP, MCDBA

This series of SMO posting, I would like to show how to do database backup with SMO. Backup operation is represented by Microsoft.SqlServer.Management.Smo.Backup class. The example I show here is how to use the SMO backup class by doing full AdventureWorks2008 database backup on my local SQL Server Machine, so let’s get started by creating windows form application in Visual Studio 2008.

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

Now, create 1 button, 1 label, and 1 progress bar control on the form, and create click event handler of the button and put code here :

progressBar1.value = 0;
progressBar1.Maximum = 100;

Backup bkup = new Backup();bkup.Action = BackupActionType.Database;
bkup.Database = “AdventureWorks2008”;
bkup.Incremental = false;
bkup.Devices.Add (new BackupDeviceItem(@”C:\AdventureWorks2008.bak”, DeviceType.File));
bkup.Initialize = true;
bkup.PercentCompleteNotification = 10;
bkup.PercentComplete += new PercentCompleteEventHandler(backup_PercentComplete);Server srv = new Server(“(local)”);
bkup.SqlBackup (srv); Let’s go through code steps above. Create instance of SMO Backup class. Define backup type (database/log/filegroup backup). Specify database you would like to backup (for example AdventureWorks2008).

If you would like to run backup as incremental backup, specify value true for Incremental property of the backup instance; otherwise it is false. And specify backup filename by adding new BackupDeviceItem.

If you would like to overwrite existing backup set inside backup file, put value true into Initialize property of the backup instance. And put PercentCompleteNotification to 10; that means that you instruct the backup object to let you know backup progress for each 10% increment. And specify eventhandler for each completion 10% incremental steps.

2 remaining last steps is define SQL Server and run backup by issuing method : SqlBackup on the SQL Server.

You could set backup progress value into label and progress bar control so that user could see backup progress inside the event handler (backup_PercentComplete). In the event handler put the code below :progressBar1.value = e.Percent;
label1.Text = e.Percent.ToString() + “ % complete”; Because the UI got freezed during update progress running before 100%, it’s better to implement threading, here is complete code to implement threading so that user could do any other task on the windows form user interface (UI) whilst waiting backup operation to complete.

    public partial class Backup : Form
    {
        public Backup()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            button1.Enabled = false;
            Thread tr = new Thread(new ThreadStart(doBackup));
            tr.Start();
        }

        private void doBackup()
        {
            progressBar1.Value = 0;
            progressBar1.Maximum = 100;

smo.Server srv = new smo.Server("(local)");
smo.Backup backup = new smo.Backup();

            backup.Action = Microsoft.SqlServer.Management.Smo.BackupActionType.Database;
            backup.Database = "AdventureWorks2008";
            backup.Incremental = false;
            backup.Devices.Add(new Microsoft.SqlServer.Management.Smo.BackupDeviceItem(@"C:\adventureWorks2008.bak", smo.DeviceType.File));
            backup.Initialize = true;
            backup.PercentCompleteNotification = 10;
            backup.PercentComplete += new Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler(backup_PercentComplete);

backup.SqlBackup(srv);
}

        void backup_PercentComplete(object sender, Microsoft.SqlServer.Management.Smo.PercentCompleteEventArgs e)
        {
            this.Invoke(new displayProgress_delegate(displayProgress), e.Percent);
        }

private delegate void displayProgress_delegate(int progress);

        private void displayProgress(int progress)
        {
            label1.Text = progress.ToString() + " % complete";
            progressBar1.Value = progress ;

            if (progress == 100)
                button1.Enabled = true;
        }
    }

Here is screenshot as combination of C# threading and SMO backup operation implementation to give non-freezing user interface.

Share this post: | | | |

Get User Database Properties with SMO

Kasim.Wirama — Fri, 22 Jan 2010 11:28:00 GMT

Get User Database Properties with SMO

By : Kasim Wirama, MCITP, MCDBA

Still with SMO discussions, this time I would like to show how we easily get properties of a user database with SMO (Server Management Object). As brief explanation, SMO is an SQL Server specific API that enables application developer to build custom SQL Server solution inside their application.

Let’s jump into example here.

Fire up Visual Studio 2008 and create windows application project with C# (you could choose other .NET compliant language such as VB.NET). Double click on Form1 listed in Solution Explorer and drag-drop a button and a propertyGrid control.

Add 4 SMO references by right click on References folder (in Solution Explorer) and choose “Add Reference…”, choose : Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc, Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended and click OK.

Double click on the form to open code behind and add 2 namespace declaration here :

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

Switch back to graphical designer of Form1 and double click on the button to create its event handler and put the code here :

Server srv = new Server(“(local)”);
Database db = srv.Databases[“AdventureWorks2008”];
this.PropertyGrid1.SelectedObject = db;

The code above is to retrieve database properties of AdventureWorks2008 in your local SQL Server instance.

Run your windows application project (by pressing F5 button) and click the button to display the below result :

Share this post: | | | |

Get List of SQL Server Instances with SMO

Kasim.Wirama — Sat, 16 Jan 2010 22:01:00 GMT

Get List of SQL Server Instances with SMO

By : Kasim Wirama, MCDBA, MCITP

Sometimes there are requirements where you need to get list of SQL Server instances. For example you need to do database health check on each databases of each SQL Server instances. First you need to know SQL Server instance before you could get list of databases of the SQL Server instance. There are many ways to get list of SQL Server instance. You could get from running SQLCmd dos-prompt command here SQLCmd –Lc. If you need to get other attributes of a SQL Server instance such as SQL Server version, whether the instance is local instance or remote instance, and whether the instance is clustered instance then you could use SMO as I explain below.

SMO supplies one excellent class called SMOApplication. The class has 1 static method : EnumAvailableSqlServer. The method has one Boolean parameter named localOnly. Specify it to true, the method will return all SQL Server instances that run on your local machine only. Specify it to false, the method will return all SQL Server instances than run on your local and remote machine. The return result is ADO.NET datatable.

Let’s implement SMO into .NET project here. Fire up Visual Studio 2008. Add 4 SMO references (Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended, Microsoft.SqlServer.Management.Sdk.Sfc). Switch on code behind of Form1 user interface and put 2 SMO-related namespace here :

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

On Form1 user interface, draw 1 button and 1 DataGridView. Double click on the button so that Visual Studio will automatically create “click” event handler for the button. Inside event handler, you specify the code below :

DataTable dt = SmoApplication.EnumAvailableSqlServers (true);
DataGridView1.DataSource = dt;

I would like to get list of local SQL Server so I pass true value into EnumAvailableSqlServers method. The result is shown on the screenshot below :

I find out that creating SMO application is easy and give additional security layer to your SQL Server.

Share this post: | | | |

Building First SMO (SQL Server Management Object) Application

Kasim.Wirama — Sat, 16 Jan 2010 21:59:00 GMT

Building First SMO (SQL Server Management Object) Application

By : Kasim Wirama, MCDBA, MCITP

With limitless idea exposed by classes in SMO, you could build full-fledged administrative tool which is similar or even more complete than SQL Server Management Studio (SSMS). Here I show important steps to build SMO application. These steps will apply from simplest until most complex SMO application.

To get started to build first application with SMO, let’s fire up Visual Studio 2008. Choose any C#/VB.NET project type. For example in this posting, I choose C# windows form application. Add Project Reference and pick up 4 DLLs, i.e. Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended, Microsoft.SqlServer.Management.Sdk.Sfc.

Switch on code behind of Form1 user interface and put 2 SMO-related namespace here :

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

On Form1 user interface, draw 3 textbox and 1 button, name them txtServer, txtUserName, txtPassword and btnLogin. Double click on btnLogin so that Visual Studio will automatically create “click” event handler for the button. Inside event handler, you specify the code below :

ServerConnection conn = new ServerConnection();
conn.ServerInstance = txtServer.txt;

conn.LoginSecure = false;
conn.Login = txtUserName.txt;
conn.Password = txtPassword.txt;
try{
Server localServer = new Server(conn);
Messagebox.Show (“connected”);
}
catch (SmoException exSMO){
MessageBox.Show (exSMO.ToString());
}

This code above just simply logins into specified SQL Server instance with supplied user name and password, and that’s SQL Server authentication mode. ServerConnection object (conn variable) represents instance of SQL Server and assign connection to instance of Server object (localServer variable). If connection is made with SQL Server authentication mode, you need to set LoginSecure property is false otherwise it is true (Windows authentication mode). For windows authentication mode, it is not necessary to supply value to Login and Password property.

Next code is “Server localServer = new Server (conn);”. It makes connection and if successfully authenticated, conn object will assign connection state into Server instance and pop up message “connected” to end-user. If failed to connect, then the code pass to SmoException.

That’s the simple SMO example for getting started. With important initial steps to setup SMO solution, you have basic knowledge to get ready to build more complex SMO application.

Share this post: | | | |

SMO (SQL Server Management Object) in SQL Server 2005/2008

Kasim.Wirama — Sat, 16 Jan 2010 21:56:00 GMT

SMO (SQL Server Management Object) in SQL Server 2005/2008

By : Kasim Wirama, MCITP, MCDBA

SMO has been introduced since SQL Server 2005. The earlier version of SMO is DMO (Distributed Management Object) which is used by previous version of SQL Server 2005. What is purpose of SMO and DMO? The purpose of SMO and DMO is to bring SQL Server administrative feature into flexibility format to be reused by application developer to build custom SQL Server administration tool. Examples of SQL Server administration activities that might be incorporated into custom application are attaching database when an application setup is installed or building simple database security management as part of an application or building automated backup restore that runs without SQL Server Agent. Both SMO and DMO come in form of APIs library (wrapped into assembly) that contains libraries to accomplish the above task examples.

SMO comes in assembly called Microsoft.SqlServer.Smo.dll, and it is in Program Files\Microsoft SQL Server\100\SDK\Assemblies folder for 32 bit.

Below are list of useful SMO namespaces with the description :

1. Microsoft.SqlServer.Management.Common

It contains SMO common class and RMO (Replication Management Object).

2. Microsoft.SqlServer.Management.Smo

It contains core classes and enumerations for manipulating SQL Server

3. Microsoft.SqlServer.Management.Smo.Agent

It contains classes for SQL Server Agent.

4. Microsoft.SqlServer.Management.Smo.Wmi

It used when implement Wmi functionality (conjunction with assembly Microsoft.SqlServer.SqlWmiManagement.dll)

5. Microsoft.SqlServer.Management.Smo.RegisteredServers

It is used when working with registered servers (conjunction with assembly Microsoft.SqlServer.SmoExtended.dll)

6. Microsoft.SqlServer.Management.Smo.Mail

It is used when implementing database mail

7. Microsoft.SqlServer.Management.Smo.Broker

It is used for SQL Server Service broker functionality

SMO could be used to administer from SQL Server 7.0 until latest release of SQL Server (SQL Server 2008).

If administration functionality could be delivered with Transact SQL (T-SQL), why do we bother to use SMO? The answer is because of security. T-SQL execution exposes risk of SQL injection, SMO reduces attack surface because developer defines their own functions when interacting to SQL Server through compiled .NET code. It is not possible to attack with SQL injection when administration function is done through SMO and compiled .NET code.

Share this post: | | | |

Passing Table-Valued Parameter in SSIS 2008

Kasim.Wirama — Tue, 12 Jan 2010 11:50:00 GMT

Passing Table-Valued Parameter in SSIS 2008

By : Kasim Wirama, MCITP, MCDBA

One of new TSQL enhancements in SQL Server 2008 is table-valued parameter. The challenge is how to pass table valued parameter into stored procedure from SSIS. This process involves 2 major steps. First step is to save the resultset into SSIS object-type variable. Second step is to fill SSIS object-type into ADO.NET datatable, which, in turns, pass the data table into stored procedure table valued input parameter.

Let’s try to scenario here. You need to pass customer information (first name and last name) from 1 database (AdventureWorks) to other database (let’s say tempdb database). The tempdb database contains customer table here :

CREATE TABLE dbo.Customer
(
FirstName VARCHAR(50),
LastName VARCHAR(50))

You create the stored procedure to insert new data into Customer table here :

CREATE PROCEDURE dbo.InsertCustomer
(
@CustomerInfo AS CustomerInfoType READONLY
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Customer (FirstName, LastName)
SELECT FirstName, LastName FROM @CustomerInfo;
END;

Notice that CustomerInfoType is user defined type with table type, the definition of the type is shown here :

CREATE TYPE CustomerInfoType AS TABLE
(
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL
);

Create the type first before you create the stored procedure.

Now you open new SSIS project from Visual Studio 2008. Create new package.

First step is to save source data into SSIS object-type variable. So create SSIS variable with object type and name it tempCustomer. Drag Data Flow Task into Control Flow designer and double click on it to switch into Data Flow designer. At this stage, we need to specify source data where customer information comes from. Drag OLEDB source, and double click to open OLEDB source editor. Click New button to specify new OLE DB connection manager, with database name : AdventureWorks and click Test Connection button. Once OLE DB connection manager has been created and selected on OLE DB Source Editor, choose data access mode : Table or View and table name : Person.Contact.

Click “Columns” on left pane, and select only FirstName and LastName by ticking the check boxes and click OK to close OLE DB Source Editor dialog window. Now you are returned back to Data Flow designer. From SSIS toolbox, drag Recordset Destination onto Data Flow designer and drag connection arrow from the OLE DB Source to the Recordset Destination. Double click on the Recordset Destination instance and set SSIS variable (tempCustomer) into the instance property called VariableName. Move to second tab (Input Columns) and make sure 2 columns (FirstName and LastName) are selected and click OK to return back to Data Flow Designer. You need to create 1 connection manager as destination of data transformation with ADO.NET Destination type. Double click on the new connection manager, and specify database name is tempdb where dbo.Customer and its stored procedure reside. Click OK to close the connection manager. You can rename the ADO.NET destination connection manager to remember easily, for example : destination.

Now you will configure how SSIS variable will be passed into table valued type stored procedure. I will tell you how at second step below.

Switch to Control Flow designer and drag Script Task into the designer and connect arrow from Data Flow Task to Script Task. Double click on Script Task to open Script Task Editor. At Script page, select your language preference (VB.NET or C#), by default it is C#. Specify ReadOnlyVariables to SSIS variable : tempCustomer. Then click Edit Script button to open Visual Studio Tools For Application (VSTO).

In the VSTO, specify 2 namespaces (using System.Data.Oledb and System.Data.SqlClient). Move to Main routine and create 3 instance here :

OleDbDataAdapter oledbDA = new OleDbDataAdapter();
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();

Next code is to fill datatable from SSIS variable through data adapter here :

oledbDA.Fill (dt, Dts.Variables[“User::tempCustomer”].Value;

Next step is to create instance of destination connection (created before second step, named destination) here :

SqlConnection conn = (SqlConnection)Dts.Connections[“destination”].AcquireConnection(Dts.Transaction);

The remaining step here is typical steps to execute stored procedure in .NET.

cmd.Connection = conn;
cmd.CommandText = “dbo.InsertCustomer”;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“@CustomerInfo”, dt);

If (conn.State == ConnectionState.Closed) {conn.Open(); }

cmd.ExecuteNonQuery();

If (conn.State == ConnectionState.Open) {conn.Close(); }

Dts.TaskResult = (int) ScriptResults.Success;

Save the code and compile it. Close all dialog windows and run the package. If it runs successfully, check into destination table to see all customer information has been populated into destination.

That’s the way how you could leverage TSQL new feature in SQL Server 2008 in SSIS.

Share this post: | | | |

Practical Steps of Configuring Transparent Data Encryption

Kasim.Wirama — Sun, 10 Jan 2010 11:04:00 GMT

Practical Steps of Configuring Transparent Data Encryption
(SQL Server 2008)

By : Kasim Wirama, MCITP, MCDBA

Transparent Data Encryption (TDE) is new feature of SQL Server 2008. I can say it is security feature. The feature will encrypt database files into encrypted format in SQL Server 2008 Enterprise or Developer edition. Books Online describes more detail about TDE. What I would focus on this posting is how to configure TDE.

The case is that you will encrypt AdventureWorks2008 OLTP database. So here is following steps to configure :

1. Open SQL Server Management Studio and open new query window.

2. Switch to master database context.

3. Create master key encrypted by password here :
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘123’;

4. Create certificate. The certificate will be encrypted by master key.
CREATE CERTIFICATE mycert WITH SUBJECT = ‘Tde certificate’;

5. Backup certificate. The certificate is required for restoring database backup into other SQL Server instance.

BACKUP CERTIFICATE mycert TO FILE =’C:\mycert_bkup.cer’

WITH PRIVATE KEY

(

FILE = ‘C:\mycert_pvt.cer’,

ENCRYPTION BY PASSWORD = ‘456’

);

6. Switch to AdventureWorks2008 database context

7. Create database encryption key.

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE mycert;

8. Turn on encryption option on AdventureWorks2008

ALTER DATABASE AdventureWorks2008

SET ENCRYPTION ON;

To monitor progress of encryption process, you could query into new dynamic management view here:

SELECT DB_NAME(DATABASE_ID), ENCRYPTION_STATE FROM SYS.DM_DATABASE_ENCRYPTION_KEYS

Share this post: | | | |

Get Spatial Position for Geography Data Type in SQL 2008

Kasim.Wirama — Sun, 10 Jan 2010 10:23:00 GMT

Get Spatial Position for Geography Data Type in SQL 2008

By : Kasim Wirama, MCITP, MCDBA

SQL Server 2008 introduces new data type, one of them is regarding to position and area, which are geometry and geography data type. Geometry data type covers could be used for small area whereas geography should be used when you need to cover a big area in your database solution, such as between 2 towns or even spanning across countries. This posting discuss about geography data type, specifically to show where you could get your latitude and longitude which are used across a globe.

There are many tools to help you to determine latitude and longitude of an object position on earth. One of them is idvSolutions Spatial wiki. The url is http://silverlight.idvsolutions.com/. The url uses silverlight technology. If your browser doesn’t have silverlight installed, you need to download silverlight from http://www.microsoft.com/silverlight/ and install the add-on into your browser.

You will find out world map at the website.

Click new button (near upper right corner) to start determine position of an object in world map. For example, I would like to draw area of Madagascar Island. Fill in Title (at least), so I fill into the textbox as Madagascar Island. Next click drawing tool to select Area as shown on the following screenshot.

Click OK button, and click Save button to save the area into website and wait for some time as shown in pic below.

Now, the item (titled : Madagascar Island) has been saved into the website. The next step is to export latitude and longitude data into SQL Server 2008 geography data type format by clicking icon beside the item as shown at below screenshot

Choose SQL Server 2008 and click open to see geography data which is ready as input into SQL Server 2008 geography spatial data type below.

That’s the geography data we get, you need to get map as well. One of website that provide map is http://www.openstreetmap.org/. it is open source project, from there you could get global map.

Share this post: | | | |

Looking Inside SQL Server Data Row for Variable Length Column

Kasim.Wirama — Tue, 29 Dec 2009 22:42:00 GMT

Looking Inside SQL Server Data Row for Variable Length Column

By : Kasim Wirama, MCITP, MCDBA

I have shown information on hexadecimal value on fixed length column in my recent posting Looking Inside SQL Server Data Row. Now let’s have a look how variable length columns are stored in SQL Server storage engine.

Add more column on the sample table or re-create the table in tempdb database below :

USE tempdb;

CREATE TABLE dbo.Test
(col1 CHAR(2) NOT NULL,
col2 INT NULL,
col3 VARCHAR(10) NOT NULL,
col4 VARCHAR(10) NULL,
col5 VARCHAR(10) NULL
);
GOINSERT INTO dbo.Test VALUES (‘aa’,123,’ccc’,’ddd’,’eee’);

To find file id and page id, run DBCC IND (read Locate SQL Server Data in SQL Server Storage Engine).

DBCC IND (tempdb, ‘dbo.Test’,-1);

The file id and page id is taken from PageType = 1 on the returned resultset.Turn on trace flag 3604 and run DBCC PAGE with print option 1 to get information about data row below:

DBCC TRACEON (3604);GODBCC PAGE (tempdb, 1, 193, 1);

The returned result set for DATA section is :00000000: 30000a00 61617b00 00000500 00030018 †0...aa{......... 00000010: 001b001e 00636363 64646465 6565††††††.....cccdddeee

Let’s focus on bold ones here (30000a00 61617b00 00000500 00030018 001b001e 00636363 64646465 6565)

30 – if it is converted into bit, the value is 0011 0000. Read from right to left :· Bit 0 – versioning information. For SQL Server 2008, it is 0.· Bit 1 to 3,o 000 – it is primary record (data itself)o 001 – it forwarded recordo 010 – it is forwarding stubo 011 – it is indexo 100 – it is BLOB or row-overflow datao 101 – it is ghost indexo 110 – it is ghost datao 111 – it is ghost version data· Bit 4 indicates whether NULL exists, because there is at least one NULL column definition (col2, col4 and col5) then it is 1· Bit 5 indicates whether there is any variable length column. In this sample there are 3 variable length varchar columns· Bit 6 indicates whether the row contains versioning information. No versioning at the moment so it is 0.· Bit 7 is 0.

000a00 61617b00 0000 – see explanation on my posting Looking Inside SQL Server Data Row.

0500 – shows number of columns. There are 5 columns.

00 – shows NULL and NOT NULL columns. Because the total column is less than 8 bit, so the size is 1 byte. If more then 8 bit then divide by 8 and round up the value. 2 decimal is represented as 10 in bit. 1 shows NULLABLE column or unused bit and 0 shows NON NULLABLE column. In the table all columns have value so the value is 0.

0300 – shows number of variable length columns.

1800 – shows position where position of first variable length column ends. It should be swapped to 00 18 (it ends in position of 24 in decimal). The size is 2 bytes.

1b00 – similar to 1800, the position is 00 1b (ends in position 27), it is for col4 (‘ddd’)

1e00 – similar to 1800, the position is 00 1e (ends in position 30) , it is for col5 (‘eee’)

636363 – is data for col3 (‘ccc’)

646464 – is data for col4 (‘ddd’)

656565 – is data for col5 (‘eee’)

From the example above, the pattern for variable length column is <total number of variable length columns> + <offset position for variable length col1> + <offset position for variable length col2> + … + <actual value for col1> + <actual value for col2> + ….

Share this post: | | | |

Integrity Check Order in SQL Server

Kasim.Wirama — Thu, 24 Dec 2009 11:13:00 GMT

Integrity Check Order in SQL Server

By : Kasim Wirama, MCITP, MCDBA

SQL Server has 7 table constraints. They are DEFAULT constraint, NOT NULL constraint, CHECK constraint, FOREIGN KEY constraint, UNIQUE constraint, PRIMARY KEY constraint and trigger. They are part of data integrity enforcement from database. The order of data integrity enforcement from first until last order is:

1. DEFAULT constraint check.

2. NOT NULL constraint check.

3. CHECK constraint check.

4. FOREIGN KEY constraint check from referencing table.

5. FOREIGN KEY constraint check from referenced table.

6. UNIQUE and PRIMARY KEY constraint check.

7. Trigger constraint check.

From the steps above, trigger is last constraint check and that’s the reason why trigger could slow down manipulation operation (INSERT/UPDATE/DELETE) on database level; and probably could presents concurrency issues which is usually complex to troubleshoot.

Those constraints are derived from conceptual database constrains. Conceptual database constraints are entity integrity, domain integrity and referential integrity. I always here DRI (Database Referential Integrity) and actually DRI is one of 3 pillars of database integrity.

Entity integrity means that each row should be uniquely identified either UNIQUE constraint or PRIMARY KEY constraints. Trigger could be also used.

Domain integrity means that you enforce particular one or range of value to be applied on an entity (table) attribute (column). FOREIGN KEY, CHECK, DEFAULT are created for enforcing domain integrity. NOT NULL could be regarded for domain integrity check when combined with the 3 domain constraints. Example of domain integrity check is Age column should be between 0 and 100.

Referential integrity check involves column value check on referencing table to make sure value entered exist on value of referenced table column.

Share this post: | | | |

Question and Answer about SQL Server I/O Subsystem Issue

Kasim.Wirama — Thu, 24 Dec 2009 10:48:00 GMT

Question and Answer about SQL Server I/O Subsystem Issue

By : Kasim Wirama, MCITP, MCDBA

In SQL Server Indo mailing list, there was question about slow query performance on one SQL Server instance but not for the other SQL Server instance. This is something to do either with I/O subsystem or the query itself. I haven’t been aware until the other member replied the mailing list thread with my previous post here. Here is my reply to the first mailing list member that I gave the solution. In case you find performance issue problem, first check on what I have given steps to investigate here.

There are 2 probabilities, either the I/O subsystem issue or the query workload that demands high I/O request to exceed I/O throughput.

To investigate first probability, check on your performance monitor counter below :

PhysicalDiskObject : Avg. Disk Sec/Read and Avg. Disk Sec/Write.

If either of the result shows less than 10 ms, so it is no issue on I/O subsystem. If it is between 10-20 ms, it is still okay. And if it is over 20 ms, then the issue is on I/O subsystem. The solution is to locate on fast I/O subsystem.

The issue is complicated if the database has several data files on same physical I/O. You need to determine which data files that has the issue by querying into sys.dm_io_virtual_stats Dynamic Management Function (DMF) here :

SELECT DB_NAME(database_id), file_id, io_stall_ms, io_stall_write

FROM SYS.DM_IO_VIRTUAL_STATS (NULL, NULL);

If you find either io_stall_ms or io_stall_write is very big number, it is indication on which database files that have big I/O request. Other mailing list member asks how to know filename because there is only file_id. The answer is that you need to query into other DMV, namely SYS.MASTER_FILES

SELECT file_id, physical_name

FROM SYS.MASTER_FILES

WHERE database_id = DB_ID(‘tempdb’);

To investigate second probability, the below DMV query could identify which query causes performance issue here :

select top 5

(total_logical_ read/execution_ count) as avg_logical_ reads,

(total_logical_ writes/execution _count) as avg_logical_ writes,

(total_physical_ reads/execution_ count) as avg_phys_reads,

execution_count,

(select substring (text,statement_ start_offset/ 2 + 1, (case when statement_end_ offset = -1 then len(convert( nvarchar( max),text) ) * 2

else statement_end_ offset

end - statement_start_ offset) / 2)

from sys.dm_exec_ sql_text( sql_handle) ) as query_text,

plan_handle

from sys.dm_exec_ query_stats

order by (total_logical_ reads + total_logical_ writes) desc;

Information from the above query could be used to tune your query further. That’s the area of query tuning (either index tuning or rewrite query for performance boost).

Share this post: | | | |

Determine Row Size in SQL Server

Kasim.Wirama — Thu, 24 Dec 2009 10:22:00 GMT

Determine Row Size in SQL Server

By : Kasim Wirama, MCITP, MCDBA

To be able to determine the number of rows for a page, you need to determine the row size for respective row. SQL Server could give you information through catalogue view. First let’s we have a look a table with only fixed-length columns and second we will have a look a table with fixed and variable-length columns.

To determine overall size of a row, you could query from sys.indexes. There is undocumented property value of INDEXPROPERTY, it is minlen. Let’s examine table I created on my last posting, Locate SQL Server Data in SQL Server Storage Engine. Run the query to sys.columns here :

USE tempdb

SELECT INDEXPROPERTY(object_id, name, ‘minlen’) AS total_length

FROM Sys.indexes

WHERE object_id = object_id(‘dbo.test’).

You will get total row size is 10. Let’s see size of each columns by running query to Sys.Columns catalog view here :

SELECT name, column_id, max_length

FROM Sys.columns

WHERE object_id = object_id(‘dbo.test’)

You will get value 2 for max_length of col1 and 4 for col2. Total of them is 6. Where is the other 4 byte? That’s the row overhead used internally by SQL Server storage engine.

Now add one variable column here to test table :

ALTER TABLE test

ADD col3 varchar(MAX) NULL;

Run again the query below :

SELECT INDEXPROPERTY(object_id, name, ‘minlen’) AS total_length

FROM Sys.indexes

WHERE object_id = object_id(‘dbo.test’).

It still has value 10 though a new variable length column is added. In this case, it would become minimum length instead of total_length.

Now re-run the query below :

SELECT name, column_id, max_length

FROM Sys.columns

WHERE object_id = object_id(‘dbo.test’)

Interestingly, you will find out col3 has max_length equal to -1. That means that that’s LOB column.

Share this post: | | | |

Looking Inside SQL Server Data Row

Kasim.Wirama — Tue, 22 Dec 2009 11:41:00 GMT

Looking Inside SQL Server Data Row

By : Kasim Wirama, MCITP, MCDBA

If you have read my 2 previous postings (Review SQL Server Data Page via Undocumented DBCC Command and Locate SQL Server Data in SQL Server Storage Engine) about the use of DBCC PAGE and how to locate data page, you will know how data is represented as 4 eight digit hexadecimal. What does the 4 eight digit hexadecimal give information? Let’s go back the data page for test table in tempdb database by running DBCC PAGE below.

Use tempdb;

DBCC TRACEON (3604);

DBCC PAGE (tempdb, 1, 164, 1);

The result is shown below :

PAGE: (1:164) BUFFER:

BUF @0x03A1916C bpage = 0x09082000 bhash = 0x00000000 bpageno = (1:164)

bdbid = 2 breferences = 0 bUse1 = 64196

bstat = 0x1c0000b blog = 0xbbbcbcbb bnext = 0x00000000

PAGE HEADER: Page @0x09082000

m_pageId = (1:164) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000m_objId (AllocUnitId.idObj) = 5652 m_indexId (AllocUnitId.idInd) = 2560 Metadata: AllocUnitId = 720575940749688832 Metadata: PartitionId = 720575940748771328 Metadata: IndexId = 0Metadata: ObjectId = 69575286 m_prevPage = (0:0) m_nextPage = (0:0)pminlen = 10 m_slotCnt = 1 m_freeCnt = 8081m_freeData = 109 m_reservedCnt = 0 m_lsn = (23:285:258)m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0m_tornBits = 0

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED

DATA: Slot 0, Offset 0x60, Length 13, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 13

Memory Dump @0x6207C060

00000000: 10000a00 61617b00 00000200 00††††††††....aa{......

OFFSET TABLE:

Row - Offset

0 (0x0) - 96 (0x60)

Let’s dissect the DATA slot section (bold one) here :

00000000: 10000a00 61617b00 00000200 00††††††††....aa{......

10 – shows status bit A. Inside status bit A, there is 8 bit from least significant bit to most significant here:

· Bit 0 – versioning information. For SQL Server 2008, it is 0.

· Bit 1 to 3,

o 000 – it is primary record (data itself)

o 001 – it forwarded record

o 010 – it is forwarding stub

o 011 – it is index

o 100 – it is BLOB or row-overflow data

o 101 – it is ghost index

o 110 – it is ghost data

o 111 – it is ghost version data

· Bit 4 indicates whether NULL exists, because there is one NULL on column definition (col2) then it is 1

· Bit 5 indicates whether there is any variable length column

· Bit 6 indicates whether the row contains versioning information

· Bit 7 is 0.

00 – shows status bit B, status bit B is used to show whether the record is ghost forwarded record. For new record (not deleted), it is 0.

0a00 – shows total length for fixed-length column. Swap the value becomes 00 0a, you will get 10 (decimal) for fixed length column.

6161 – 2 bytes - shows data. 61 is ASCII code for a. because cola contains value ‘aa’ then the hexadecimal value is double 61.

7b00 0000 - 4 bytes as int has – shows data as well. Swap and flip the value, it will gives value 123, which is exactly what colb has the value.

0200 – shows number of columns. There are 2 columns.

Decode value from hexadecimal value is straightforward but the behavior of how to decode data is different when the row is compressed in SQL Server 2008.

Share this post: | | | |

SQL Server knowledge center

Database Integrity Check with SMO

Database Integrity Check with SMO

By : Kasim Wirama, MCITP, MCDBA

Performing Database Restore by SMO

Performing Database Restore by SMO

By : Kasim Wirama, MCITP, MCDBA

Performing Database Backup by SMO

Performing Database Backup by SMO

By : Kasim Wirama, MCITP, MCDBA

Get User Database Properties with SMO

Get User Database Properties with SMO

By : Kasim Wirama, MCITP, MCDBA

Get List of SQL Server Instances with SMO

Get List of SQL Server Instances with SMO

By : Kasim Wirama, MCDBA, MCITP

Building First SMO (SQL Server Management Object) Application

Building First SMO (SQL Server Management Object) Application

By : Kasim Wirama, MCDBA, MCITP

SMO (SQL Server Management Object) in SQL Server 2005/2008

SMO (SQL Server Management Object) in SQL Server 2005/2008

By : Kasim Wirama, MCITP, MCDBA

Passing Table-Valued Parameter in SSIS 2008

Passing Table-Valued Parameter in SSIS 2008

By : Kasim Wirama, MCITP, MCDBA

Practical Steps of Configuring Transparent Data Encryption

Practical Steps of Configuring Transparent Data Encryption (SQL Server 2008)

By : Kasim Wirama, MCITP, MCDBA

Get Spatial Position for Geography Data Type in SQL 2008

Get Spatial Position for Geography Data Type in SQL 2008

By : Kasim Wirama, MCITP, MCDBA

Looking Inside SQL Server Data Row for Variable Length Column

Looking Inside SQL Server Data Row for Variable Length Column

By : Kasim Wirama, MCITP, MCDBA

Integrity Check Order in SQL Server

Integrity Check Order in SQL Server

By : Kasim Wirama, MCITP, MCDBA

Question and Answer about SQL Server I/O Subsystem Issue

Question and Answer about SQL Server I/O Subsystem Issue

By : Kasim Wirama, MCITP, MCDBA

Determine Row Size in SQL Server

Determine Row Size in SQL Server

By : Kasim Wirama, MCITP, MCDBA

Looking Inside SQL Server Data Row

Looking Inside SQL Server Data Row

By : Kasim Wirama, MCITP, MCDBA

Practical Steps of Configuring Transparent Data Encryption
(SQL Server 2008)