Slinging SQL

SharePoint on SQL Server Mirrors?!?!?!

noreply@blogger.com (Dan Ward) — Tue, 13 Apr 2010 17:33:00 +0000

Yes, while SharePoint does not have the native ability to recognize a SQL Server 2008 Database Mirroring Session, you can configure it in such a way that you can protect your SharePoint installation with high availability for a lower cost. (It should be noted that SharePoint is recommended to run on a SQL Server cluster)

I recently implemented SharePoint on SQL Mirrors for a client, and the following is the method which I chose to use. (Some images have been redacted)

Background

Setting up mirroring on SQL Server to be utilized by a SharePoint installation requires some additional steps to achieve automatic failover. SharePoint is not a mirroring aware product, it does not allow for capturing the FAILOVER PARTNER attribute for connection strings. In order to accomplish this you have a couple of options.

First you can manually alter the connection string settings in the configuration files for SharePoint. As this is not the recommended or selected approach it will not be discussed, other than to state that the danger in this is any upgrade or service pack could cause configuration files to be overwritten and the addition of the FAILOVER PARTNER attribute in the connection strings could be lost.

The selected method of running SharePoint against databases that are mirrored is described in detail below.

As a starting point we used the Microsoft White Paper “Using SQL Server Mirroring with Office SharePoint Server® and Windows SharePoint Services®”. This document can be accessed via the link below.

Using Database Mirroring with Office SharePoint Server and Windows SharePoint Services

The white paper describes how to set up the mirroring, how to set up a SQL Alias for SharePoint to use as a data source, and how to set up SharePoint to use that SQL Alias. When the database mirroring session fails over to the mirror server, the SQL Alias on the SharePoint servers can be modified to point to the mirror server, which has become the principal server, thereby removing the need to reconfigure SharePoint. Because the above white paper does not provide a method for automating the failover from the principal database server to the mirror server, we had to derive how to do this on our own. Following is the detailed documentation of the steps involved in automating the switch over for SharePoint to use the alternate database server.

Remotely updating the SQL Server Alias

The first step to achieving automated failover for SharePoint is to automate the update to the SQL Server Alias on each SharePoint Web Front End server and Application server. The SQL Alias configuration is held within the Windows Registry on each individual server, therefore a process must be run to update the value of the SQL Alias to change the Server attribute portion to match the currently active database principal. The registry value is held at HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\<SQL Alias Name>. <SQL Alias Name> is a REG_SZ key with a comma separated value that represents the connection type and server and if necessary the port number.

We created A pair of batch files to reside on each server. The pair of batch consists of one file for each database server in the mirroring session. For development we have chosen to place the files within a folder named “SQLServerMirrorScripts” on the root of drive C:. Note, we could have chosen different options for this, such as PowerShell scripts but due to client standards and preference we were limited.

Note the content of one of the batch files:

set DBSERVERALIAS=SQLAlias

set DBSERVER=DBSrv2

reg add HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo /v %DBSERVERALIAS% /t REG_SZ /d "DBMSSOCN,%DBSERVER%" /f

reg query HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo

The next step is to find a way to execute the batch files from a remote server, the database servers, but within the process space of the target server, the MOSS servers. In order to accomplish this we are used a utility called psexec.exe. You can attain this utility from Microsoft at Microsoft PSTools Page. This utility is a DOS based program that will remotely execute a process on a server under the currently logged in user context or under the user provided by command line argument. Each database server has another pair of batch files that encapsulate the execution command for the appropriate SQL Alias editing batch file on the remote server. These files, along with the psexec utility executable reside on each database server in a folder called “SQLServerMirrorScripts” in the root of drive C:

Again, each batch file is named for the database server it is going to change the SQL Alias too. Each batch file executes the remote batch file appropriate, designated in its name, to the operation. Below is the content of these batch files. These files exist in the same location on each of the database servers.

C:\SQLServerMirrorScripts\psexec.exe \\SharePointServer C:\SQLServerMirrorScripts\SQLAlias1.bat -d -e

At this point we have a process that will allow us to change the SQL Server Alias value from a remote location. When using psexec.exe for the first time from a server, you must manually execute the application and accept the license agreement that will popup. If this step is skipped the process will fail, because it will always wait for license acceptance.

Automating the SQL Server Alias Update

While the previous steps will allow us to modify the SQL Server Alias from each database server, they do not provide for the automatic updating of the alias in response to a database failing over from the principal server to the mirror server. In order to accomplish the automation, several additional steps are required.

Enabling Service Broker for each database

In order to enable automation, there must be a mechanism in place to respond to an event. In order to provide event notification in SQL Server, Service Broker must be enabled for all the mirrored databases and the msdb database. If you are unsure if Service Broker is enabled for your database you can execute the following query to find out.

USE master

SELECT name, is_broker_enabled

FROM sys.databases

This will list the databases and a binary value for the state of is_broker_enabled, 0 is no, and 1 is yes.

By default, SharePoint databases are created with Service Broker enabled, but if there is a need to enable it after the database is created, the following command can be issued in SQL Server Management Studio;

ALTER DATABASE <DATABASE NAME> SET ENABLE_BROKER

You must also ensure that SQL Server Agent is running. If it is not, you must start the service, either via the services control snap in

or command line.

net start SQLSERVERAGENT

Setting Up the Event Notification

DATABASE_MIRRORING_STATE_CHANGE is not one of the events that can be automatically trapped. The event is available as a manually created Event Notification.
You will need to create an event notification named DBMirrorStateChange to raise a notification for the DATABASE_MIRRORING_STATE_CHANGE event, on both database servers in the mirroring session (not the witness):

If Not Exists (Select 1 From sys.server_event_notifications Where name = 'DBMirrorStateChange')

Begin

Create Event Notification DBMirrorStateChange

On Server

For DATABASE_MIRRORING_STATE_CHANGE

To Service 'DBMirrorService', 'current database';

End

This code need only be execute for a single database, denoted above as ‘current database’, in order to create the event notification that will be used for any database the has a mirroring state change.

If it ever becomes necessary to remove an event notification you can use the code below;

DROP EVENT NOTIFICATION notification_name [,...n ]

ON { SERVER | DATABASE | QUEUE queue }[;]

The DATABASE_MIRRORING_STATE_CHANGE event notification provides a property that denotes what type of state change has happened. This “state” property will inform us of what type of state change has occurred. The possible states for the event are as follows;

State	Name	Description
0	Null Notification	This state occurs briefly when a mirroring session is started.
1	Synchronized Principal with Witness	This state occurs on the principal when the principal and mirror are connected and synchronized and the principal and witness are connected. For a mirroring configuration with a witness, this is the normal operating state.
2	Synchronized Principal without Witness	This state occurs on the principal when the principal and mirror are connected and synchronized but the principal does not have a connection to the witness. For a mirroring configuration without a witness, this is the normal operating state.
3	Synchronized Mirror with Witness	This state occurs on the mirror when the principal and mirror are connected and synchronized and the mirror and witness are connected. For a mirroring configuration with a witness, this is the normal operating state.
4	Synchronized Mirror without Witness	This state occurs on the mirror when the principal and mirror are connected and synchronized but the mirror does not have a connection to the witness. For a mirroring configuration without a witness, this is the normal operating state.
5	Connection with Principal Lost	This state occurs on the mirror server instance when it cannot connect to the principal.
6	Connection with Mirror Lost	This state occurs on the principal server instance when it cannot connect to the mirror.
7	Manual Failover	This state occurs on the principal server instance when the user fails over manually from the principal, or on the mirror server instance when a force service is executed at the mirror.
8	Automatic Failover	This state occurs on the mirror server instance when the operating mode is high safety with automatic failover (synchronous) and the mirror and witness server instances cannot connect to the principal server instance.
9	Mirroring Suspended	This state occurs on both partner instances when the user suspends (pauses) the mirroring session or when the mirror server instance encounters an error. It also occurs on the mirror server instance following a force service command. When the mirror comes online as the principal, mirroring is automatically suspended.
10	No Quorum	If a witness is configured, this state occurs on the principal or mirror server instance when it cannot connect to its partner or to the witness server instance.
11	Synchronizing Mirror	This state occurs on the mirror server instance when there is a backlog of unsent log. The status of the session is Synchronizing.
12	Principal Running Exposed	This state occurs on the principal server instance when the operating mode is high protection (synchronous) and the principal cannot connect to the mirror server instance.
13	Synchronizing Principal	This state occurs on the principal server instance when there is a backlog of unsent log. The status of the session is Synchronizing.

Creating an Alert for Automatic Failover capture

We are interested in State 8 for this step. State 8 indicates an automatic fail over has occurred. This event is raised on the mirror server when a database that is acting as the mirror, changes state to acting as the principal.

The mechanism we use for responding to an event notification is an alert. An alert can be created via SQL Server Management Studio (SSMS), or via T-SQL script. The SSMS method will be shown here. Locate and expand the SQL Server Agent node in the SSMS object tree view.

Right click on Alerts and select “New Alert”. Enter a name for the Alert in the Name: attribute box, select WMI event alert in the Type drop down list. The Namespace: value will be populated automatically for the server you are on. Note: you will have to do this on both servers, but you can script the alerts and jobs from the first server and use those scripts on the second server as shown later. In the Query: box you will write the query based on the DATABASE_MIRRORING_STATE_CHANGE event.

Select the Response page and check the box for “Execute Job”, then select the “New Job” button.

This will create a new job and open the Job properties dialog for you. Enter a job name, owner and select the category as Database Mirroring. If the category “Database Mirroring” is not available select “Uncategorized (Local)”. Enter a description to detail the purpose of the job, and ensure Enabled is checked.

Select the Steps page on the left next. And select the “New” Button on the bottom. This will launch the Job Step properties page.

Enter a name for the job step you are creating, select the type, in this case we are executing a batch file so select “Operating System (CmdExec)”, and chose the Run As account from the drop down. This will be the user that the process will run under. In the Command box input the batch file for the server you are working on and click “OK”.

Back on the Alert properties page now, click “OK” to finish creating the Alert.

We now have an Alert that will capture the event that a database has gone from Mirror to Principal and take appropriate action. When the event occurs, the Alert is activated if the state value is 8. The Alert then executes the Job that was created, and the job executes the batch file which executes the psexec utility to execute the batch file on the SharePoint server, which modifies the registry to change the server value for the SQL Server Alias.

Creating an Alert for Manual Failover capture

A manual fail over is different than an automatic fail over, in that it fires an event with state 7 that occurs on the Principal server where the fail over command originated. Because SharePoint is a multi-database application, it is desired that all databases reside on the same server at all times. For automatic fail overs, it would be rare that the fail over would happen at a database level. Normally it will occur because the server went down, the network card failed, the disks failed, or some other catastrophic event occurred which rendered the Principal server inoperable. Manual fail over occurs when the Fail Over button is pressed in the Mirroring properties of the database or when certain T-SQL commands are issued. Manual fail over occurs at the database level, therefore a mechanism to automate manual fail over for the SharePoint instance, must not only change the SQL Server Alias, but also cause the other SharePoint databases to fail over to the Mirror. We will follow the same steps as for Automatic fail over, but we will add a new step to the job before the alias update step.

Locate and expand the SQL Server Agent node in the SSMS object tree view.

Right click on Alerts and select “New Alert”. Enter a name for the Alert in the Name: attribute box, select WMI event alert in the Type drop down list. The Namespace: value will be populated automatically for the server you are on. Note: you will have to do this on both servers, but you can script the alerts and jobs from the first server and use those scripts on the second server as shown later. In the Query: box you will write the query based on the DATABASE_MIRRORING_STATE_CHANGE event. Note that we are looking for State 7 in this event response.

Select the Response page and check the box for “Execute Job”, then select the “New Job” button.

Select the Steps page on the left next. And select the “New” Button on the bottom. This will launch the Job Step properties page.

Enter a name for the job step you are creating, select the type, in this case we are a SQL Server query so select “Transact-SQL script (T-SQL)”. Leave the “Run As” box empty. In the Command box input the code shown and click “OK”.

Code from above step:

SET NOCOUNT OFF

DECLARE @strSQL NVARCHAR(4000)

DECLARE @strDatabasename NVARCHAR(2000)

DECLARE MyCursor CURSOR FOR

SELECT name FROM master.sys.databases a

INNER JOIN master.sys.database_mirroring b

ON a.database_id=b.database_id

WHERE NOT mirroring_guid IS NULL

AND mirroring_role_desc='PRINCIPAL'

OPEN MyCursor

FETCH Next FROM MyCursor INTO @strDatabasename

WHILE @@Fetch_Status = 0

BEGIN

SET @strSQL = 'ALTER DATABASE [' + @strDatabaseName + '] SET PARTNER FAILOVER'

EXEC sp_executesql @strSQL

FETCH Next FROM MyCursor INTO @strDatabasename

END

CLOSE MyCursor

DEALLOCATE MyCursor

This code will force all databases on the server to become the “FailOver Partner” or Mirror, which causes the existing Mirror, or “FailOver Partner” to become the principal.

Back on the Job properties Steps page now, click “New” to create another step. This step will be identical to the job step for automatic failover, with the exception of which batch file it calls. Enter the values as below and click “Ok”.

Click “OK” on the Job Properties pages. We now have a job with two steps to force all databases to fail over if one does, and to update the SQL Server Alias after that.

Back on the Alert Property page, click “Ok” to finish creating the alert.

We now have an Alert that will capture the event that a database has been manually failed over and take appropriate action. When the event occurs, the Alert is activated if the state value is 7. The Alert then executes the Job that was created, and the job executes the T-SQL code to fail over the remaining databases and then the batch file which executes the psexec utility to execute the batch file on the SharePoint server, which modifies the registry to change the server value for the SQL Server Alias.

Of course you have to do the same for the other server in the mirroring set. The procedure is the same, just in reverse of what is shown here, as for which batch file to call when.

That’s all for this post. Feel free to leave comments or ask questions. I’ll be happy to answer any. I’ve also included some useful links below.

Useful Links

SQL Server Central Blog

http://msdn.microsoft.com/en-us/library/ms188712.aspx

http://www.mssqltips.com/tip.asp?tip=1564

Technet Blog On Mirroring

Technorati Tags: SharePoint,Server,Mirrors,Database,Session,installation,cost,client,method,Some,Background,product,FAILOVER,PARTNER,connection,options,settings,configuration,danger,addition,databases,Microsoft,White,Paper,Office,Windows,Services,Alias,data,servers,documentation,Front,Application,Registry,HKLM,SOFTWARE,MSSQLServer,ConnectTo,Name,REG_SZ,port,batch,development,folder,SQLServerMirrorScripts,Note,PowerShell,scripts,preference,DBSERVERALIAS,SQLAlias,DBSERVER,DBMSSOCN,MOSS,PSTools,Page,user,context,argument,execution,Again,location,SharePointServer,agreement,acceptance,Update,response,automation,Service,Broker,mechanism,event,notification,SELECT,FROM,Management,Studio,ALTER,ENABLE_BROKER,Agent,SQLSERVERAGENT,DATABASE_MIRRORING_STATE_CHANGE,events,DBMirrorStateChange,Exists,Where,Begin,Create,DBMirrorService,DROP,QUEUE,State,Description,Null,Principal,Mirror,Lost,instance,Manual,Automatic,mode,instances,error,Quorum,backlog,status,protection,Alert,SSMS,script,Locate,node,tree,Alerts,Enter,Type,Namespace,jobs,Query,Execute,properties,dialog,owner,category,Local,purpose,Steps,Button,Step,System,CmdExec,account,Command,Back,action,card,disks,Fail,Over,Transact,Leave,Code,NOCOUNT,DECLARE,NVARCHAR,MyCursor,CURSOR,INNER,JOIN,OPEN,FETCH,INTO,Fetch_Status,EXEC,CLOSE,DEALLOCATE,exception,Click,procedure,Feel,Useful,Links,Central,Blog,library,Technet,psexec,synchronous,strSQL,strDatabasename,database_id

Database Mirroring on SQL Server 2008

noreply@blogger.com (Dan Ward) — Fri, 26 Feb 2010 04:28:00 +0000

I know it has been a very long time since I posted, and for that I am sorry. Sometimes work and personal life conspire to evaporate any free time you might have. But here I am with a new and hopefully useful bit of hard won experience to share with you.

I was recently engaged at a client to assist them with implementing database mirroring on SQL Server 2008. Everything seemed straight forward enough, they were conducting a proof of concept (POC) around using SQL Server to host their new ERP system and needed to prove the high availability features of automatic failover. No problem so far. They had a primary and secondary server and a virtual server to act as the witness already in place.

The wrench got thrown in at our kick-off meeting when the client informed me that since they are already implementing SharePoint 2007, they want the POC to use SharePoint as the application that will utilize the mirrored database set up. I’m not sure how many of you know this, but in order for an application to be natively ready to utilize a SQL Server mirrored environment and facilitate automatic fail-over, it must be capable of implementing the FAILOVER PARTNER attribute of a mirroring aware data source. SharePoint does not. There are many posts around the net on DB Mirroring, and SharePoint on mirrors, but I failed to find a comprehensive source that provided all the information I needed. Hence, my contribution to the community via this post. Actually this and the next post. I plan to break it up into a pair of posts, one that deals with the setting up of the mirroring, and one that deals specifically with the steps necessary to implement SharePoint on the mirroring session and provide automatic fail-over.

So in this post we will go through the process of creating a mirroring session and implementing mirroring for a database. We’ll use the AdventureWorks2008 sample database that Microsoft was so kind to create for us.

Any time you embark on configuring something in SQL Server, it is a good idea to be sure all the prerequisites are met ahead of time. There is a good source for prerequisites on SQL Server database mirroring at Microsoft TechNet. Here is the link Prerequisites, Restrictions, and Recommendations for Database Mirroring

Some things to note: Make sure both partners are using the same version and EDITION of SQL Server 2008. If you are running Enterprise on the primary, you should run Enterprise on the secondary. Both servers should also utilize the same default collation. You can not implement mirroring on database servers that have implemented the FILESTREAM option. Use of a third server, which can be a different Edition of SQL Server 2008, even EXPRESS, is required to fill the role of witness if you are implementing high availability mode (high safety with automatic fail-over).

So let’s begin. The first step is to ensure we have 3 server instances available to us. In a production application, these will be 3 separate servers, even if any or all are virtual. If you are doing this on your own, you can install a named instance of SQL Server along with your default instance and install SQL Server Express to act as the witness.

Once you have established your servers, you will need to ensure you have met all the requirements and pre-requirements as laid out in the above link. When you are ready to begin, follow the steps below.

Step 1:
Set Recovery Model to FULL.

Step 2:
Backup the database.

Step 3:
Copy the backup to the designated mirror instance.
Step 4:
Restore the backup on the designated mirror instance with the options selected as below.

You must choose the “Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored (RESTORE WITH NORECOVERY)” option for database mirroring.

Step 5:
On the designated principal instance, right click the database and select “Tasks” and then “Mirror”. This will launch the Database Mirroring Wizard.

Click “Next” and move through the wizard, selecting the Principal or primary server, the secondary or Mirror server, and the Witness server. In order to achieve automatic fail over a witness server is required.

There will be an identical screen on which to select the Witness server. The wizard creates and configures the endpoints for you.

These accounts are used for communication between the three servers.

Final check before finishing.

This screen shows the configuration progress for each server. Any warnings or errors will be caught and displayed here. When this screen finishes, you will be prompted to start mirroring.

Once the process concludes and the databases have synchronized, the mirroring session is started and all transactions made at the principal will be duplicated on the mirror. In the event of a failure of the primary/principal the Witness and the Mirror form a quorum and determine to switch control to the mirror, which exits the restoring state and becomes a disconnected principal. When the mirroring session is started the Mirror page of the database properties is as shown below.

You will see in the database object tree view in SQL Server Management Studio, that one database shows Principal, synchronized, and one shows Mirror, synchronized.

That’s it. You have set up mirroring on your database. Now when a problem occurs with the principal, the mirror will take over operation as the principal database. Any mirroring aware applications can implement the FAILOVER PARTNER attribute of the connection string to ensure continuous operation through a failed database server.

You can manual fail over the database from primary server to the secondary server in two ways.

One: Open the properties for the database and select the Mirror page. There is a button “Failover” that if you press, will manually fail over the database from the primary to the secondary.
Two: You can issue a T-SQL Query to fail over the database. On the primary server with the principal database execute the following code;
ALTER DATABASE AdventureWorksDW SET PARTNER FAILOVER

That’s all there is too setting it up. Of course there is a ton more information on Microsoft MSDN and TechNet as well as in BOL. Look it up, there’s much, much more to learn about database mirroring.

In my next post we will continue from here and implement automatic failover for a SharePoint installation. Until thin, keep slinging the SQL.

Change Tracking (Not CDC)

noreply@blogger.com (Dan Ward) — Tue, 18 Nov 2008 14:21:00 +0000

Change Tracking has a similar name to Change Data Capture and has caused some minor confusion. Change Tracking, however, is entirely different and serves a separate purpose.

CDC is an asynchronous process that captures row level changes and stores them in special change tables. This information is available as relational data and can be queried by client applications.

Change Tracking is synchronous and tracks data changes but stores only the fact that they were changed and the last value for the row. Both one-way and two-way synchronization is supported, but remember that with two-way conflict detection is supported but the client is responsible for handling it.

Change Tracking uses tracking tables to store the primary key of the modified rows along with version numbers. It is easy to detect version conflicts.

An application requests the version number for a row it intends to modify.
If the version has changed since the last request, there is a conflict.
Solving the conflict is up to the requesting application.

In order to work with Change Tracking there are a few steps to do.

Enable Change Tracking at the database level.
Enable Change Tracking at the table level.
Use CHANGE_TRACKING_MIN_VALID_VERSION function to get the minimum version, which is an integer. This value can be used to get the changes for a specific table.
Use CHANGETABLE function to get information , such as changes, types of changes, columns that changed, etc.
Use CHANGE_TRACKING_CURRENT_VERSION function to get the current version. This is set by the last transaction committed in the database.
Use the WITH CHANGE_TRACKING_CONTEXT() hint to specify a context for data modifications. This allows data modifications to be grouped according to client or application.

Let's look at some code that will show some of the basics.

USE master;
GO
--Create a test database if necessary
IF NOT EXISTS(SELECT * FROM sys.databases WHERE [name]=N'MyDB')
    CREATE DATABASE MyDB;
GO
--Configure the database to allow change tracking
IF NOT EXISTS(SELECT * FROM sys.change_tracking_databases 
WHERE database_id = (SELECT database_id FROM sys.databases WHERE [name]=N'MyDB'))
    ALTER DATABASE MyDB SET CHANGE_TRACKING = ON 
    (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO
--Configure the database to use snapshot isolation
ALTER DATABASE MyDB SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
USE MyDB;
GO
--Create a test table
IF NOT EXISTS(SELECT * FROM sys.tables WHERE [name]=N'testTbl')
    CREATE TABLE testTbl(testID INT IDENTITY(1,1) PRIMARY KEY 
    NOT NULL, VAL VARCHAR(64));
GO
--Enable change tracking on the table
IF NOT EXISTS(SELECT * FROM sys.change_tracking_tables WHERE [object_id] = OBJECT_ID(N'testTbl'))
    ALTER TABLE testTbl ENABLE CHANGE_TRACKING;
GO
--Examples of using the Change Tracking functions.
SELECT CHANGE_TRACKING_CURRENT_VERSION();
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'testTbl'));INSERT INTO testTbl(VAL) VALUES('V01');
SELECT CHANGE_TRACKING_CURRENT_VERSION();
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'testTbl'));
INSERT INTO testTbl(VAL) VALUES('V02'),('V03'),('V04');
SELECT CHANGE_TRACKING_CURRENT_VERSION();
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'testTbl'));

The example code above shows the initial values for Change Tracking as well as the changes when you update a row's data. To use Change Tracking you need to reconnect to check that you have a valid version of the data before you process it. Below shows the one-way synchronization scenario.

--This shows the one way sync scenario
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
DECLARE @Last_sync_version INT = 0;
DECLARE @sync_version INT;
--Use transaction for consistency
BEGIN TRANSACTION
--Make sure the last_sync_version is valid
IF (@Last_sync_version < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'testTbl')))
    RAISERROR(N'Invalid value', 10, 1);
ELSE
BEGIN
    --Get the current for the next sync
    SET @sync_version = CHANGE_TRACKING_CURRENT_VERSION();
    --Show the current tracking version and change version
    --for each row.
    SELECT testTbl.testID, testTbl.VAL, CT.SYS_CHANGE_VERSION CV,
        CT.SYS_CHANGE_CREATION_VERSION CCV, CT.SYS_CHANGE_OPERATION CO,
        CT.SYS_CHANGE_COLUMNS CC, CT.SYS_CHANGE_CONTEXT CContext
    FROM CHANGETABLE(CHANGES testTbl, @Last_sync_version) AS CT
    LEFT JOIN testTbl ON testTbl.testID = CT.testID;
    SELECT @sync_version;
END
COMMIT TRANSACTION
GO

--Show another session updating the data
UPDATE testTbl SET VAL='New V01' WHERE testID = 1;
INSERT INTO testTbl(VAL) VALUES('V05');
DELETE FROM testTbl WHERE testID = 3;

--The initial session reconnects to get
--changes
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
DECLARE @Last_sync_version INT = 2;
DECLARE @sync_version INT;
BEGIN TRANSACTION
--Check that you have a valid version
IF(@Last_sync_version < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'testTbl')))
    RAISERROR(N'Invalid value', 10, 1);
ELSE
BEGIN
    SET @sync_version = CHANGE_TRACKING_CURRENT_VERSION();
    SELECT testTbl.testID, testTbl.VAL, CT.SYS_CHANGE_VERSION CV,
        CT.SYS_CHANGE_CREATION_VERSION CCV, CT.SYS_CHANGE_OPERATION CO,
        CT.SYS_CHANGE_COLUMNS CC, CT.SYS_CHANGE_CONTEXT CContext
    FROM CHANGETABLE(CHANGES testTbl, @Last_sync_version) AS CT
    LEFT JOIN testTbl ON testID.Tbl.MyID = CT.MyID;
    SELECT @sync_version;
END
COMMIT TRANSACTION
GO

There is also two-way synchronization, which is shown below. In the scenario shown, an attempt is made to update the first row. If it can't update the row, the execution path follows the conflict resolution code. This example only uses a table with 2 columns, but in real-world scenarios a table will have far more columns, some of which may have been updated. In this situation, if you are updating columns in a table that haven't been updated, you may choose to proceed.

--Two-way synchronization scenario.
--Assumes the last sync occurred when the sync value was 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
DECLARE @Last_sync_version INT = 2;
DECLARE @sync_version INT;
DECLARE @current_row_version INT;
--Find the current row version for testID=1
SELECT @current_row_version = ISNULL((SELECT CT.SYS_CHANGE_VERSION 
FROM CHANGETABLE(VERSION testTbl, (testID), (1)) CT),0)
SELECT @current_row_version;
BEGIN TRANSACTION
IF (@Last_sync_version < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'testTbl')))
    RAISERROR(N'Invalid value',10,1);
ELSE
BEGIN
    --Attempt to update the 1st row
    UPDATE testTbl SET VAL = 'NewAppValue'
    FROM testTbl WHERE testID = 1 AND @Last_sync_version >= @current_row_version;
END
IF (@@ROWCOUNT = 0)
BEGIN
    --A conflict is indicated by the execution path (@@ROWCOUNT = 0)
    --Gather more information to see if the VAL column has been updated.
    IF EXISTS(SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK(2, CT.SYS_CHANGE_COLUMNS) CC
    FROM CHANGETABLE(CHANGES testTbl,@Last_sync_version) CT
    WHERE CT.testID = 1 AND 
    CHANGE_TRACKING_IS_COLUMN_IN_MASK(2,CT.SYS_CHANGE_COLUMNS)=1)
        RAISERROR(N'The column VAL was changed', 10, 1);
        RAISERROR(N'An update conflict has occurred', 10, 1);
END
COMMIT TRANSACTION

Change Tracking fits into Microsoft's "Connect to your data from any device" theme for SQL Server 2008, which is made up of the Microsoft Sync Framework, Sync Services for ADO.Net, SQL Server Compact Edition 3.5. It provides for conflict detection and easy data retrieval of changes with minimum performance impact, and automatic setting for data retention. There are several advantages, such as no schema changes on the tracked tables (no need for a timestamp column), security is at the table level and prevention of loopbacks is easy.

As always, please leave your comments or send email to sql.slinger@gmail.com. I'm always interested in your feedback.

Until next time, happy slinging.

Technorati Tags: SQL Server,SQL Server 2008,Change Tracking,Versioning,Version,Row Version,Tracking,Conflict Resolution

Beyond Auditing

noreply@blogger.com (Dan Ward) — Fri, 31 Oct 2008 16:16:00 +0000

Sometimes capturing the fact that someone changed something and who that someone is, isn't enough. Sometimes, if the values in a row of a table were changed, you need to know what the original values were along with who changed them. Into this space we introduce SQL Server 2008 Change Data Capture (CDC).
This new feature of SQL Server records Data Modification Language operations (INSERT, UPDATE, DELETE) on user tables. The changes are exposed using table-valued functions. The CDC asynchronously reads changes in the source tables from the transaction log and inserts those changes into separate change tables. This feature is available in Enterprise, Developer, and Evaluation editions only.

Before we can use this feature we have to configure the database for it. A member of the sysadmin server role must enable CDC on the database by running the sys.sp_cdc_enable_db stored procedure. You can also enable CDC for individual tables only by running sys.sp_cdc_enable_db_change_data_capture.

--First, let's create a demonstration database with a table in it
USE [master];
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'DemoDB')
    CREATE DATABASE [MyDB];
GO
USE [MyDB];
GO
CREATE TABLE MyTable
(MyID INT PRIMARY KEY NOT NULL,
 MyItem VARCHAR(128),
 MyQty INT);
 GO
 
--Next, let's enable CDC on the database
EXEC sys.sp_cdc_enable_db;
GO
--Enable CDC for the Products tableEXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = NULL,
@capture_instance = N'MyTbl_1',
@captured_column_list = N'MyID, MyItem, MyQty',
@filegroup_name = N'PRIMARY',
@supports_net_changes = 1
GO

When you run the two system stored procedures you will see results like these;

Job 'cdc.MyDB_capture' started successfully.
Job 'cdc.MyDB_cleanup' started successfully.

Notice that two jobs are created to monitor/capture and to cleanup the data capture tables.
Now, we have our table, let's put some data into it and modify that data so we can see CDC in action.

--Now let's add some initial data
INSERT INTO MyTable(MyID,MyItem,MyQty) 
Values(1,'Item 1',20),(2,'Item 2',10),(3,'Item 3',1),(4,'Item 4',3);
GO--And change it
UPDATE MyTable SET MyQty+=10 WHERE MyID=1;
GO



Command(s) completed successfully.

Now, when we enabled CDC for the database and the table in it, SQL Server created a pair of Table Valued Functions for us. The functions are named as cdc.fn_get_all_changes_'capture_instance', where 'capture_instance' is the value we specified for @capture_instance in our sp call to enable cdc on the table. In our case they are cdc.fn_cdc_get_all_changes_MyTbl_1. There are also several built-in functions for CDC that make querying information about CDC tables and instances easier. (See BOL for details). We will use two of the built-in functions in our example below.
Here they are in action;

DECLARE @from_lsn binary(10), @to_lsn binary(10);
SELECT @from_lsn = sys.fn_cdc_get_min_lsn(N'MyTbl_1');
SELECT @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_MyTbl_1(@from_lsn,@to_lsn,N'all');
SELECT * FROM cdc.fn_cdc_get_net_changes_MyTbl_1(@from_lsn,@to_lsn,N'all');
GO

And the results from the above query;

Notice the difference in the results from the two functions. The first function tracks history for every row in the table. It shows Item 1 as it was insert and again after it's update. The second function however, doesn't show the history, but just the net changes. Notice that the initial insert value for Item 1 is missing, only the current value is shown.

To read more on this topic check out the section in SQL Server 2008 Books Online. See all the available system functions associated with it. BOL topic is Change Data Capture.

Until next time, happy slinging.

Technorati Tags: SQL Server 2008,CDC,Change Data Capture,Auditing,Audit,Tracking,T-SQL,Database audit,database tracking,change tracking,data history

Productivity enhancements for SSMS

noreply@blogger.com (Dan Ward) — Thu, 31 Jul 2008 15:57:00 +0000

SQL Server 2008 includes several enhancements geared toward improving productivity and customer satisfaction. Some of the enhancements have been long sought after by the SQL Server database developer and administrator.

IntelliSense!
Yes we have long awaited the ability to write T-SQL code with IntelliSense helping code faster. IntelliSense with SQL 08 provides word completion and displays parameter information for functions and stored procs. In the XML editor, IntelliSense can completely show an element declaration. It also indicates errors with red squiggly lines and references them in the Error List window, so you can quickly navigate to the error line by double-clicking the entry. IntelliSense is provided in the Query editor as well as the XML editor, and supports most, but not all, T-SQL syntax. Below are some screen-shots of IntelliSense at work.

Collapsible Regions
Similar functionality is now provided in the SMS08 Query Editor to regions delimited by BEGIN...END blocks and multi-line statements, such as SELECT statements that span two or more lines. This is similar to what Visual Studio has offered for a few releases now. Here's an example (note the '-' sign on the left, click to collapse text):

Delimiter Matching
Also similar to Visual Studio, SQL Server 2008 Management Studio now offers delimiter matching and highlighting. When you finish typing the second delimiter in a pair, the editor will highlight both the delimiters, or you can press CTRL + ] to jump to the matching delimiter when you are on one of them. This will help you keep track of parenthesis' and nested blocks. Automatic delimiting will recognize these delimiters;
(...), BEGIN...END, BEGIN...END TRY, BEGIN...END CATCH. Brackets and quotes are not recognized for delimiter highlighting.

SMS Object Explorer
More context menu choices have been provided for your right-click menu in Object Explorer. These choice include options for changing table design, to opening the table with a certain number of returning records, to getting some of the new reports available. Options for partitioning (Storage), Policies, and Indexing are provided as well.

You will notice a new "Start PowerShell" option. Look for a future post to cover the new Windows PowerShell integration.

Integrated T-SQL Debugging
Debugging has now been integrated into the Query Editor! You can set breakpoints, step through code, step into code a particular location, and even set watches up to monitor variable values, locals and the call stack. Woohoo!!!!

Other improvements
Multi-Server queries, allow you to run a query across numerous servers and return the results with the server name prepended.
Launch SQL Profiler directly from Management Studio.
Customizable tabs, allow you to modify the information shown and the layout from the tools\options dialog.
Object Explorer Detail Pane has been improved for better functionality and productivity with navigational improvements, detailed object information pane at the bottom, and integrated object search.
Object Search, allow you to search within a database on a full or partial string match and return objects to the Object Explorer Details pane.
Activity Monitor, you have to see it to appreciate it. Built brand new from scratch, it is based on the Windows Resource Monitor and allows you to see graphs of processor wait time, waiting processes, database I/O and batch requests. Detail grids are provided for Processes, Resource Waits, Data File I/O, and Recent Expensive Queries. It's a vast improvement for the DBA.
Performance Studio, new performance tuning tool that tracks historical performance metrics and stores them using drill-through reports.
Partitioning Setup GUI, at last a way to create and manage table partitions graphically. This is accessed from the context menu for a table under Storage. A wizard will launch allowing you to create or manage partitions.
Service Broker Hooks, new context menu items centralize access to the Service Broker T-SQL templates for messages, contracts, queues, etc. Read only property pages are provided for each of these objects as well.

The improvements in the toolset provided with SQL Server 2008 are vast and far sweeping. There are more for you to discover and the best way is to get your hands on it. You can download SQL Server Express 2008 from Microsoft if you don't have a developers edition license.

Please, as always, post your comments and questions. Until next time, happy slinging.

Technorati Tags: SQL Server 2008,SQL Server,SSMS,SMS,SQL Server Management Studio,T-SQL,Query Editor,Enhancments,Object Explorer,Object Explorer Detail view,Results Grid,Activity Monitor,SQL Tool Enhancemenets.

Auditing, Katmai Style

noreply@blogger.com (Dan Ward) — Wed, 30 Jul 2008 13:51:00 +0000

Ok, once again I've been unable to keep up with daily posts, so I've decided to stop fighting it. Since I'll most likely never be able to consistently post daily, I'm going to quit worrying about it and just focus on posting good content as I can.

In this installment though, let's explore the new Audit feature included in SQL Server 2008. SQL Server 2005 provided auditing via SQL Trace, C2 audit model, and DDL Triggers. While these methods are adequate they leave some room for improvement.

Into that gap steps the SQL Server Audit. This new feature is based on Extended Events and allows you to monitor server-level and database-level events, individually and in groups. The new SQL Server Audit object collects a single instances of the server or database-level actions and groups of actions to be monitored. The Audit object exists at the SQL Server instance level and you can have multiple audit objects per instances.

There are two specification objects can be included in an Audit object, the Server Audit Specification and the Database Audit Specification. You can only specify one server audit specification per audit, however you can have a database audit specification for each database in the instance. You can also only have a server audit specification or database audit specifications, not both.

As you can imagine a server audit specification collects information about the server, which are raised by the Extended Events feature. There are several predefined groups of actions, which are events exposed by the database engine, known as audit action groups. These can be included in your server audit specification object within the audit object.

The database audit specification collects what else but database-level audit actions, which are raised by the Extended Events feature. You can include either groups of actions or single actions to a database audit specification. Again, audit action groups are provided as predefined groups of actions.

So, we can collect all this audit information, now what? Well, obviously this feature wouldn't be complete without the ability to record the information somewhere. This is where the audit target comes into play. Each audit object sends the audit results to a target. That target can be a file, the Windows Security event log, or the Windows Application event log. Note that writing to the Windows Security event log requires elevated permissions, so in order for audit results to be logged there, the SQL Server service account will have to be granted the "Generate security audits" permission in the group or local policy.

The process of creating and using a SQL Server Audit is as follows;
1. Create a SQL Server Audit object
2. Create either a server audit specification or a database audit specification and map it to the audit object.
3. By default the Audit and Specification objects are disabled, so in this step enable them.
4. Auditing occurs and you can read the data in the target you defined as part of the Audit object.

Let's see an example:

USE master GO --Create the audit object

CREATE SERVER AUDIT SrvAudit TO APPLICATION_LOG; GO --Create the audit specification object

CREATE SERVER AUDIT SPECIFICATION AuditSpec --and map it to the audit object

FOR SERVER AUDIT SrvAudit --then add the audit action group ADD (FAILED_LOGIN_GROUP); GO --Enable the audit ALTER SERVER AUDIT SrvAudit WITH (STATE = ON); GO

So looking at our Application log after attempting to log in with invalid credentials we see:

(Details)

For more information about auditing in SQL Server 2008 check out the SQL Server 2008 BOL, topic Auditing.

Next time we'll look into some of the IDE and configuration tool enhancements. Until then, keep slinging.

Technorati Tags: SQL Server 2008,SQL Server,Auditing,Event Log,Enhanced Event feauture,Server audit,Database audit,database,RDBMS

New Data Types

noreply@blogger.com (Dan Ward) — Tue, 15 Jul 2008 16:22:00 +0000

Well, I managed to miss yesterday, again, so I'm sorry. Luckily, it was only one day, so we aren't falling into a rut yet.

Today I'd like to look at the new data types available in SQL Server 2008. Specifically the new Date and Time, Filestream, and the spatial data storage types.

Date and Time
The new date/time types are a result of improvements that satisfy the requirements of separating date and time values, allowing a larger range or dates, allowing larger fractional second precision, providing Time zone awareness, providing ANSI SQL compliant or equivalent time and date data types, and to allow database migration compatibility with other database platforms.
The new types that allow these requirements to be met are:

DATE	Stores only the date portion of a date/time and has a range of 0001-01-01 through 9999-12-31. It has a size of 3 bytes.
*TIME(prec)*	Stores on the time portion of a date/time and has a range of 00:00:00.0000000 through 23:59:59.9999999, an accuracy of 100 nanoseconds (precision dependant). It has a size of 3 to 5 bytes dependant on precision.
DATETIMEOFFSET(prec)	Stores a time zone aware, UTC preserved datetime, and has a range of 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 UTC. It has a size of 8 to 10 bytes, dependant on precision
*DATETIME2(prec)*	Stores datetime data, similar to datetime data type, but with a far larger range. Range is 0001-01-01 00:00:00.0000000 through 9999-12-31 59:59:59.9999999. This data type is not time zone aware and the size is 6 to 8 bytes dependant on precision.

Along with the new data types, new system functions are also provided:

*SYSDATETIME()*	Returns the current database system timestamp as a datetime2(7) value.
*SYSDATETIMEOFFSET()*	Returns the current database system timestamp as a datetimeoffset(7) value.
*SYSUTCDATETIME()*	Returns the current database system timestamp as a datetime2(7) value, which represents the current UTC time.
*SWITCHOFFSET(datetimeoffset, time_zone)*	Converts a datetimeoffset to a new time zone.
*TODATETIMEOFFSET(datetime, time_zone)*	Converts a local datetime value to a datetimeoffset UTC value using the passed in time_zone value.

Let's look briefly at these new data types in action:

-- Show type usage
DECLARE @DateEx DATE;
DECLARE @TimeEx TIME;
DECLARE @DTOffEx DATETIMEOFFSET(7);
DECLARE @DT2Ex DATETIME2(7);
-- Put in some data
SET @DateEx = '2008-02-28 06:59:01.9489484';
SET @TimeEx = '2008-02-28 06:59:01.9489484';
-- View results
SELECT @DateEx AS [Date], @TimeEx AS [Time]
Output:
Date                    Time
----------------------- ----------------
2008-02-28 00:00:00.000 06:59:01.9489484
(1 rows(s) affected)
------------------------------------------------
-- system date and time
SELECT  SYSDATETIME() AS [SysDateTime], 
        SYSDATETIMEOFFSET() AS SYSDTOffset, 
        SYSUTCDATETIME() AS SysUTC;

Output:
SysDateTime             SYSDTOffset                        SysUTC
----------------------- ---------------------------------- -----------------------
2008-07-15 10:49:55.459 7/15/2008 10:49:55 AM -04:00       2008-07-15 14:49:55.459

(1 rows(s) affected)
-- Offset for a date
SELECT DATENAME(TZoffset, '2008-02-28 12:15:32.1234567 +02:10') AS TZoffset;

Output:
TZoffset
------------------------------
+02:10

(1 rows(s) affected)

--Change the offset
SELECT SWITCHOFFSET ('2008-02-28 9:50:00.6722 -8:00','+02:00') AS Changed

Output:
Changed
----------------------------------
2/28/2008 7:50:00 PM +02:00
(1 rows(s) affected)

Filestream
The Filestream attribute is a storage feature that combines the windows file system with the SQL Server database. When the Filestream attribute is added to a varbinary(max) column the database storage engine stores the column values in the NTFS file system, but the behavior of the database column will remain the same.
Utilizing this feature allows you to access the data in a dual programming model, in other words, you can access the data view T-SQL as if it were a normal BLOB type, as well as via the Win32 streaming API with T-SQL transactional semantics.
So, when should I use Filestream storage as opposed to good old BLOB storage? If your average value size is going to exceed 1MB and you need fast read access, it is a good idea. Also when you are developing middle tier application services that may need to access this data.
Standard BLOB storage is limited to 2GB size restrictions, however, the Filestream storage is only limited by the file system free space. Since the database engine keeps the column behavior consistent, integrated management is maintained, backups and restores work normally as well as third party management tools.
There are a few drawbacks to using the Filestream feature. Support is not provided for Database Snapshots or Database Mirroring, and encryption does not work with Filestream storage.

Spatial Data Types
With all the pressure from Google providing Google Maps, Google Earth, and other geographical services from various sources, it is no wonder Microsoft has begun to provide Spatial Data Storage support.
SQL Server 2008 provides new data types to handle spatial data. These data types are Geometry and Geography. Both data types are implemented as .Net CLR types, and support various methods and properties.
As the name implies, the Geometry data type is designed to store data specified by coordinates in a 2 dimensional, flat-earth system (Euclidean data). It remains compliant with the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0.
The Geography data type, is similar to the Geometry data type in that it stores coordinates, however, the Geography data type stores coordinates defined by latitude and longitude in a round-earth system.
With the use of new data types we need to be able to efficiently store and search for that data, so SQL Server 2008 provides the new Spatial Indexes to go along with the new data types. These indexes are grid-based and level of the index decomposes the one above it.

Here is a diagram from BOL that depicts the structure of the spatial data types.

See SQL Server 2008 BOL for more detailed information.
Finally, let's take a peek at this in action:

-- Make a table to use that holds geography data type
CREATE TABLE SpatialTable 
    ( id int IDENTITY (1,1),
    GeogCol1 geography, 
    GeogCol2 AS GeogCol1.STAsText() );
GO
-- Put some data into the table to use
-- Notice the use of the STGeomFromText 
-- instance method.
INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326));
INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('POLYGON((47.653 -122.358, 47.649 -122.348, 47.658 -122.348, 47.658 -122.358, 47.653 -122.358))', 4326));
GO
-- Make some variables
DECLARE @geog1 geography;
DECLARE @geog2 geography;
DECLARE @result geography;
-- Put some data in the variables from the table,
-- notice the use of built in methods/properties
-- of the data type "STAsTex()"
SELECT @geog1 = GeogCol1 FROM SpatialTable WHERE id = 1;
SELECT @geog2 = GeogCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geog1.STIntersection(@geog2);
SELECT @result.STAsText();
Output:
------------------------------------------------------------------------------------------
LINESTRING (47.656000260658459 -122.3479999999668, 47.656000130309728 -122.35799999998773)
(1 row(s) affected)
-- Quick example of using the POINT instance
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT (3 4)', 0);
SELECT @g
Output:
------------------------------------------------------------------------------------------
0x00000000010C00000000000008400000000000001040
(1 row(s) affected)

Well, that brings us to the end of another post. I hope you gained something useful from it. I will try really hard to make sure there is a post tomorrow, though I have late meetings this evening so the next entry may be on Thursday. Until then, see you later.

Technorati Tags: SQL Server 2008,SQL Server,T-SQL,Data Types,Indexes,Spatial Data,Dates,Times,Time Zones

Hierarchy made easier

noreply@blogger.com (Dan Ward) — Fri, 11 Jul 2008 22:20:00 +0000

Well, we are on a roll now, three days in a row! Today let's examine some new data types that have been added to SQL Server 2008.

SQL Server 2008 offers several new data types. One of the more interesting is the HierarchyID type. The HierarchyID data type is implemented as a CLR UDT (User defined type) and is exposed to CLR clients as SqlHierarchyId. The HierarchyID type will allow you to model organization structures, tree structures and hierarchies.
This new data type comes with many built-in methods that will allow you to manipulate and edit hierarchical data.
The methods exposed by the HierarchyID are:

child.GetAncestor(n) - this returns the HierarchyID of the nth ancestor

parent.GetDescendant(child1, child2) - this returns a child node

node.GetLevel() - returns an integer value of the depth of the node in the structure

hierarchyid::GetRoot() - returns the root of the structure

parent.IsDescendant(child) - determines if a node is a descendant of another node

hierarchyid::Parse(string) - converts a string representation of a hierarchyid into a hierarchyid value

void Read(BinaryReader x) - for CLR clients, reads a binary representation of a SqlHierarchyId object from the BinaryReader parameter and sets the SqlHierarchyId object to that value

node.Reparent(OldRoot, NewRoot) - move a node in the hierarchy from one parent to another

node.ToString() - returns a string with a logical representation of the node

void Write(BinaryWriter x) - for CLR clients, writes a binary representation of a SqlHierarchyId object to the BinaryWriter parameter.

You can use any of the above methods that return a node (representation of the hierarchy) in place of HierarchyID data type.

Let's look at how the HierarchyID works (as seen on Ravi Maniam's blog (http://blogs.msdn.com/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx);

Use AdventureWorks
Go  
--Create Scheme
Create Schema HumanResources 
Go  
--Create Table  
CREATE TABLE HumanResources.EmployeeDemo 
( 
OrgNode HIERARCHYID, 
EmployeeID INT, 
LoginID VARCHAR(100), 
Title VARCHAR(200), 
HireDate DATETIME 
) 
Go 
--Create Index
CREATE UNIQUE CLUSTERED INDEX idxEmployeeDemo 
ON HumanResources.EmployeeDemo (OrgNode,EmployeeID) 

--Insert First Row       
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (hierarchyid.GetRoot(), 1,'adventure-works\scott', 'CEO', '3/11/05') ; 
Go           
--Insert Second Row      
DECLARE @Manager hierarchyid            
SELECT @Manager = hierarchyid.GetRoot() FROM HumanResources.EmployeeDemo; 
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(NULL,NULL), 2, 'adventure-works\Mark',            
'CTO', '4/05/07')         
Go 
--Insert Third Row
DECLARE @Manager hierarchyid            
DECLARE @FirstChild hierarchyid         
SELECT @Manager = hierarchyid.GetRoot() FROM HumanResources.EmployeeDemo; 
Select @FirstChild = @Manager.GetDescendant(NULL,NULL)              
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(@FirstChild,NULL), 3, 'adventure-works\ravi',            
'Director Marketing', '4/08/07')        
Go           
--Insert the First Descendant of a Child Node         
DECLARE @Manager hierarchyid            
SELECT @Manager = CAST('/1/' AS hierarchyid)          
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(NULL, NULL),45,        
'adventure-works\Ben','Application Developer', '6/11/07') ;         
Go           
--Insert the Second Descendant of a Child Node        
DECLARE @Manager hierarchyid            
DECLARE @FirstChild hierarchyid         
SELECT @Manager = CAST('/1/' AS hierarchyid)          
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)              
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(@FirstChild, NULL),55,        
'adventure-works\Laura','Trainee Developer', '6/11/07') ;           
Go           
--Insert the first node who is the Descendant of Director Marketing       
DECLARE @Manager hierarchyid            
DECLARE @FirstChild hierarchyid         
SELECT @Manager = CAST('/2/' AS hierarchyid)          
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(NULL, NULL),551,              
'adventure-works\frank','Trainee Sales Exec.', '12/11/07') ;        
Go           
--Insert the second node who is the Descendant of Director
--Marketing             
DECLARE @Manager hierarchyid            
DECLARE @FirstChild hierarchyid         
SELECT @Manager = CAST('/2/' AS hierarchyid)          
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)              
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(@FirstChild, NULL),531,              
'adventure-works\vijay','Manager Industrial Sales', '12/09/06') ;         
Go           
--Insert the third node who is the Descendant of Director 
-- Marketing              
--in between 2 existing descendants            
DECLARE @Manager hierarchyid            
DECLARE @FirstChild hierarchyid         
DECLARE @SecondChild hierarchyid        
SELECT @Manager = CAST('/2/' AS hierarchyid)          
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)              
SELECT @SecondChild = @Manager.GetDescendant(@FirstChild,NULL)            
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(@FirstChild, @SecondChild),543,            
'adventure-works\james','Manager Consumer Sales', '12/04/06') ;
--Use Serializable Transaction 
CREATE PROCEDURE AddEmployee(@ManagerID hierarchyid, @EmpID int, 
@LogID varchar(100), @JobTitle as varchar(200), @JoiningDate datetime) 
AS 
BEGIN 
DECLARE @LastChild hierarchyid 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
BEGIN TRANSACTION 
SELECT @LastChild = Max(OrgNode) From HumanResources.EmployeeDemo 
WHERE OrgNode = @ManagerID 
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) 
VALUES(@LastChild, @EmpID,@LogID , @JobTitle, @JoiningDate) 
COMMIT 
END ;

Please post any questions in the comments and I'll get back to you quickly. I hope you can find a use for the HierarchyID data type in your day to day work.

Have a good weekend and we'll be back on Monday. Remember to post any suggestions for topics or specific questions. You can also email me at sql.slinger@gmail.com.

Technorati Tags: SQL Server,SQL Server 2008,T-SQL,Data Types,Katmai,Microsoft

Encryption and Key Management

noreply@blogger.com (Dan Ward) — Thu, 10 Jul 2008 23:59:00 +0000

Wow, I'm getting to post two days in a row again. I'm finally getting into the right frame of mind I think.
Today I'd like to look at a couple of new features that SQL Server 2008 introduces. Transparent Data Encryption (TDE) and Extensible Key Management (EKM). These subjects are closely related as we will see further on.
Transparent Data Encryption gives us the ability to encrypt our database objects without the need for changes to the client-side application. In other words, there is no need for us to provide decryption in our database applications. When enabled, data is encrypted prior to being written to the data file. This means that the data in the data file is encrypted, not just the access to it. With all the security concerns around identity theft and lost information, companies now have one less worry. Let's say that a company is running an application entirely on a laptop and storing the data in a local instance of SQL Server 2008. Yeah, I know, why would anyone do that, but for arguments sake, let's say they did. Now if the the laptop is stolen, and the data is not encrypted, the thief could very easily break into the data files, even without the SQL Server login info. But, if that data is encrypted with TDE, then that problem no longer exists. The data file can't be broken simply by attaching it to a new SQL Server. In fact TDE also encrypts backups so that the backup files are also useless to a thief. No more restoring the backup on another server.
TDE encryption is accomplished at the page level. The data is encrypted when it is written to disk and automatically decrypted when it is read from disk. The encryption will be based on a key of course, a database encryption key (DEK) that is protected with a certificate stored in the master database of the server.
As you may know, SQL Server 2005 offered column encryption, but this had its drawbacks. TDE is fully searchable. Column encryption is still part of SQL Server 2008, as it adds a finer control over what is encrypted. If only a few columns in a database need to be encrypted, such as SSN's, salary info, privacy data, then column encryption may be a better choice. TDE encrypts all files of a database, including backups. If you use TDE for a database that is being mirrored, then the mirroring database will have the same encryption. One thing to note is that if any of the databases on a server use TDE, the tempdb database also gets encrypted and can harm performance.
Let's look at enabling TDE for a database.

USE master;
GO

--We need a master key for our database
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='p@ssw0rd';
GO

--Now we need a certificate to protect our key (DEK)
CREATE CERTIFICATE encryptCert
WITH SUBJECT='Certificate to protect DEK';
GO

--Now we change databases (certs and keys are created in the master db)
USE AdventureWorks;
GO

--Let's create our Database Encryption Key (DEK)
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM=AES_128
ENCRYPTION BY SERVER CERTIFICATE encryptCert;
GO

--Now all we have to do is enable TDE
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON;
GO

Now, we can protect our DEK in a different way if we choose. We can use the Extensible Key Management (EKM) feature instead. This method will allow us to use an asymmetric key stored on a Hardware Security Module (HSM).It is a good idea, a best practice in fact, to store the keys used in encryption separately from the encrypted data. EKM allows us to follow this. EKM exposes encryption functionality to the vendors who make HSM devices. You can store encryption keys on devices such as USB devices and smartcards. HSM provides: Authentication with an EKM device, Separation of duties by additional authorization checks, Better performance for encrypt/decrypt operations, External generation of keys, storage, retrieval, distribution and disposal of keys, Key rotation.
Since EKM is disabled by default, here is how we enable it:

USE master;
GO

--Enabling is an advanced option
--so let's configure advanced options to show up
EXECUTE sp_configure 'show advanced option', '1';
RECONFIGURE;
EXECUTE sp_configure 'EKM provider enabled', '1';
RECONFIGURE;

--Here is how to protect an EKM module
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM=AES_256
ENCRYPTION BY EKM_SomeModuleDEK;
GO

And that's all there is to it. For now. One thing to note is that TDE and EKM are both based on the Microsoft Cryptographic API (MSCAPI) provider for encryption and key generation. For more information see the SQL Server 2008 BOL and MSDN.

Table-valued Parameters

noreply@blogger.com (Dan Ward) — Wed, 09 Jul 2008 15:34:00 +0000

Last post I said I was going to try to get better at posting daily. I must apologize, as I realize it has been over a month, despite the best of intentions. Things are going well and work is getting busy, life is getting busier as well. My wife and I are dealing with our first teenager. I'm sure there are those of you who can sympathize, and those that can't yet, run, while you can. Luckily for me, our oldest son is a good kid, so things could be much, much worse.

Thanks to a gentleman I spoke with yesterday. I was interviewing him for a position at Sogeti, and he actually saw my blog link in my email signature and took the time to peek at it. His interest and feed back have once again prodded me into action. If you read my articles and like them but find I'm not keeping pace with a daily blog, please feel free to drop me an email at sql.slinger@gmail.com. I'm always open to suggestions for topics too.

Well, enough said about that, let's get to today's post.

SQL Server 2005 gave us Table data types for use as variables and return values from functions. One missing piece was the ability to pass table data into a function or stored procedure as a parameter. This is now rectified in SQL Server 2008.

Table-valued parameters offer flexibility, better performance than other methods of passing a list of parameters, and reduce server round trips. They can be used in place of BULK INSERT operations and are backed up by temporary tables, which are stored in the tempdb.

ADO.NET, OLEDB and ODBC. as well as other client data access libraries, now include support for table-valued parameters

A new data type was introduced to support table-valued parameters; the user-defined table type. Not to be confused with the table data type introduced in SQL Server 2005. The user-defined table type defines the structure of the table and allows constraints, such as PRIMARY KEY, UNIQUE, and CHECK to be defined on the table type. DEFAULT constraints are not support, nor are direct index creation.

Let's look at it in action:

USE master;
GO
IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=N'DBExample')
    CREATE DATABASE DBExample;
GO
USE DBExample;
GO

--Working table definition
CREATE TABLE RequestedTopics(TopicID INT, TopicSubject VARCHAR(128));

--Here is where we create the USER-DEFINED TYPE
CREATE TYPE BlogTopicType AS TABLE(TopicID INT, TopicSubject VARCHAR(128));
GO

--We need a stored procedure to take the parameter
CREATE PROCEDURE usp_InsertBlogTopic(
    @TopicTable BlogTopicType READONLY
)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO RequestedTopics(TopicID, TopicSubject)
    SELECT TopicID, TopicSubject FROM @TopicTable;
END
GO

--Now let's reference the table type we created
DECLARE @BTT AS BlogTopicType;

--Populate it with data
INSERT INTO @BTT(TopicID, TopicSubject) VALUES(1,'Transparent Encryption');
INSERT INTO @BTT(TopicID, TopicSubject) VALUES(2,'Service Broker Enhancements');
--Now we may as well use something we learned before
INSERT INTO @BTT(TopicID, TopicSubject) 
    VALUES(3,'Analysis Services'),(4,'Reporting Services'),(5,'Integration Services');

--Run the stored proc with the table-valued parameter
EXECUTE usp_InsertBlogTopic @BTT;

--Now let's see what's in our work table
SELECT * FROM RequestedTopics;

Output:
------- ---------------
TopicID TopicSubject
1        Transparent Encryption
2        Service Broker Enhancements
3        Analysis Services
4        Reporting Services
5        Integration Services

What's New in T-SQL - Pt. 2

noreply@blogger.com (Dan Ward) — Wed, 04 Jun 2008 21:01:00 +0000

Well, like all good things, I have the best intentions of posting daily, and what happens? Life, work, the real world invades. Sorry to be so long adding and improving the blog here.

Let's look at some of the other new features that are now part of T-SQL.

SQL Server 2008 introduces the concept of row constructors. This allows us to create a table or table data type variable and then populate it with multiple sets of data in the same statement.

DECLARE @Product TABLE
(
    ProductID INT,
    ProductName NVARCHAR(64),
    ProductType INT,
    ProductCategory INT
);

--Old way
INSERT INTO @Product (ProductID, ProductName, ProductType, ProductCategory)
VALUES (1,'Widget1',1,1);
INSERT INTO @Product (ProductID, ProductName, ProductType, ProductCategory)
VALUES (2,'Widget2',1,2);
INSERT INTO @Product (ProductID, ProductName, ProductType, ProductCategory)
VALUES (3,'Widget3',2,3);

--New way
INSERT INTO @Product (ProductID, ProductName, ProductType, ProductCategory)
VALUES (4,'Gadget1',3,1), (5,'Gadget2',3,2), (6,'Gadget3',4,1);

Notice the difference! We can insert multiple rows of data in a single insert statement now. You can also use row constructors as pseudo tables if you specify a table name and column names for the set of values in the VALUE clause, as below.

SELECT * FROM (
VALUES(1,'Widget1',1,1),(2,'Widget2',1,2),(3,'Widget3',2,3)
)
Product (ProductID, ProductName, ProductType, ProductCategory);


Output:
ProductID   ProductName ProductType ProductCategory
----------- ----------- ----------- ---------------
1           Widget1     1           1
2           Widget2     1           2
3           Widget3     2           3

(3 row(s) affected)

Another new feature introduced to T-SQL by SQL Server 2008 is Single Statement Variable Declaration and Assignment. This feature allows to declare a variable and assign it's value in the same statement. Similar to other .Net languages.

--assign when declared
DECLARE @myInt INT=1, @MyName NVARCHAR(64)=N'Dan';

--assign from a query return value
DECLARE @myTable TABLE(
    ID INT,
    Name NVARCHAR(64)
);
INSERT INTO @myTable(ID, Name)VALUES(1, 'Dan'),(2, 'Bob');
DECLARE @yourName NVARCHAR(64)=
    (SELECT Name FROM @myTable WHERE ID=2);

One thing to note is that the order of precedence is not enforced when the DECLARE statement is processing. Therefore you cannot use a variable value from the same line to assign to another variable.

The last item for today's post will be Compound Assignment Operators. Just like C++ and C#, T-SQL now has the compound assignment operators.

Here is a table describing the operators and what they do.

OPERATOR                          DESCRIPTION 
+=                                Add and assign 
-=                                Subtract and assign 
*=                                Multiply and assign 
/=                                Divide and assign 
%=                                Modulo and assign 
&=                                Bitwise AND and assign 
|=                                Bitwise logical OR and assign 
^=                                Bitwise exclusive OR and assign

Compound operators work on the value already assigned to the variable and then use the operator to apply the right-side value to the current variable value.

--assign when declared
DECLARE @myInt INT=1, @MyName NVARCHAR(64)=N'Dan';

--assign from a query return value
DECLARE @myTable TABLE(
    ID INT,
    Name NVARCHAR(64)
);
INSERT INTO @myTable(ID, Name)VALUES(1, 'Dan'),(2, 'Bob');
DECLARE @yourName NVARCHAR(64)=
    (SELECT Name FROM @myTable WHERE ID=2);

Result1     Result2
----------- -------
20          1

(1 row(s) affected)


--Bitwise example
DECLARE @x TINYINT=10,@y TINYINT=2;
SET @x^=@y;  --Exclusive OR of @x by @y
SELECT @x AS Result;

Result
------
8

(1 row(s) affected)

Well, that will rap things up for today. I will endeavor to do better at posting daily. Feel free to leave your comments, whether they are suggestions, requests, or just feedback, they are always welcome.

What’s New in T-SQL - Pt. 1

noreply@blogger.com (Dan Ward) — Wed, 28 May 2008 04:25:00 +0000

Continuing, or should I say, beginning, our look at SQL Server 2008, I will be spending the next few posts discussing new and improved features in T-SQL.
Let’s start by examining the MERGE statement.
What does the MERGE statement let you do? The MERGE statement will allow you to Insert, Update, and Delete rows in your target table in just a single statement. Before the MERGE statement you would have to do each operation separately using complex join statements or WHERE clauses.

Syntax:

(from SQL 2008 Books Online)

[ WITH <common_table_expression> [,...n] ]
MERGE 
[ TOP ( expression ) [ PERCENT ] ] 
[ INTO ] target_table [ [ AS ] table_alias ]
        [ WITH ( <merge_hint> ) ]
USING <table_source>
ON <search_condition>
[ WHEN MATCHED [ AND <search_condition> ]
        THEN <merge_matched> ]
[ WHEN [TARGET] NOT MATCHED [ AND <search_condition> ]
        THEN <merge_not_matched> ]
[ WHEN SOURCE NOT MATCHED [ AND <search_condition> ]
        THEN <merge_ matched> ]
<output_clause>
[ OPTION ( <query_hint> [ ,...n ] ) ];

<merge_hint>::=
        { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }

<table_source> ::= 
{
        table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
                [ WITH ( table_hint [ [ , ]...n ] ) ] 
    | rowset_function [ [ AS ] table_alias ] 
                [ ( bulk_column_alias [ ,...n ] ) ] 
    | user_defined_function [ [ AS ] table_alias ]
    | OPENXML <openxml_clause> 
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
    | <joined_table> 
    | <pivoted_table> 
    | <unpivoted_table> 
}

<merge_matched>::=
                { UPDATE SET <set_clause> | DELETE }

<set_clause>::=
    { column_name = { expression | DEFAULT | NULL }
  | { udt_column_name. { { property_name = expression 
        | field_name = expression } 
        | method_name ( argument [ ,...n ] ) } }
  | column_name { .WRITE ( expression , @Offset , @Length ) }
  | @variable = expression 
    } [ ,...n ] 

<merge_not_matched>::=
        INSERT [ ( <column_list> ) ] 
                { VALUES ( <values_list> )
             | DEFAULT VALUES }

<output_clause>::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
        [ (column_list) ] ]
        [ OUTPUT <dml_select_list> ]
}

<dml_select_list>::=
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
     [ ,...n ]

<column_name> ::=
    { DELETED | INSERTED | from_table_name } . { * | column_name }
   | $ACTION

Example:

Scenario: You regularly update a table from a bulk source. The incoming dataset has new records and updates to existing records.
You maintain a table of the current inventory, which is updated nightly to reflect the transactions from the day.

Assumptions: Products are kept in a Product table which is not shown below. A foreign-key relationship exists with the Inventory table. The LastModified field is automatically populated by a default for all inserts.

DECLARE @InventoryMaster TABLE(InventoryID INT IDENTITY,
ProductID INT,
QtyOnHand INT,
ReorderLevel INT,
LastModified datetime DEFAULT GETDATE()
);

DECLARE @DailyTransactions TABLE(ProductID INT, TransQty INT);

INSERT INTO @InventoryMaster (ProductID, QtyOnHand, ReorderLevel) 
VALUES (1, 100, 20), (2, 50, 10), (3, 1000, 100), (25, 200, 50), (26, 200, 20);

INSERT INTO @DailyTransactions (ProductID, TransQty)
VALUES (1, -20), (1, 5), (3, -25), (3, 10), (25, -5), (24, 10), (25, 50);

SELECT * FROM @InventoryMaster;

MERGE @InventoryMaster inv
USING (SELECT ProductID, SUM(TransQty) as TransQty FROM @DailyTransactions GROUP BY ProductID) dt
ON inv.ProductID = dt.ProductID
WHEN MATCHED
THEN UPDATE SET QtyOnHand += TransQty
WHEN NOT MATCHED 
THEN INSERT VALUES(ProductID, TransQty, TransQty*.25,GetDate())
OUTPUT $action,inserted.*,deleted.*;

SELECT * FROM @InventoryMaster;

You will notice that the insert statements are a little different than you may be used to. The above example utilizes row constructors (new to SQL Server 2008). You will also keenly observe the use of "+=", and realize that SQL Server 2008 must now allow compound assignment operators, and you would be right. We will cover both of these in tomorrows post.

For now, be aware that there are many ways to construct your MERGE statement. The example utilizes a sub-select query from the daily transaction table to sum the amount deltas together. If they aren't summed you will get an error because you are not allowed to perform multiple updates on a row within the same MERGE statement.

Katmai 1st Look

noreply@blogger.com (Dan Ward) — Fri, 23 May 2008 21:14:00 +0000

A few weeks ago I presented at a Microsoft/Sogeti USA joint event in Dayton. The topic was SQL Server 2008 - What’s New. I had just over an hour for my presentation, and it turned out this was just not nearly enough time to do justice to all the new and improved features found in Katmai. I have been looking for something to motivate me into starting a blog and now it looks like I have found something. In order to rectify the injustice done to the topic because of time constraints I will be publishing a series of posts on my blog. In each post we’ll explore a new feature or an improvement in depth. Join with me and participate with your comments. Together we will uncover the mysteries of SQL Server 2008.

Look for my next post to begin our journey with "What’s New in T-SQL"