Create Database Script

I always try to enforce standards across all the SQL Servers I manage, it makes tons of administrative tasks much easier to complete and ensures consistency across the estate.  One thing that really irritates me is existing “legacy” environments that do not conform to these standards.  For example I have server X which is running SQL Server 2005 on Windows Server 2003, this server was installed and configured long before I started working for my employer and has a disk configuration of;

C:\ - OS

D:\ - SQL Server files

With this non-standard drive configuration my standard create database script falls flat on its face as the Data and Log directories are hard coded (not brilliant I know but with consistency it works a treat :)  As my Standard installation guide specifies the Data and Log directory paths I know that any new servers will be consistent but the new script will also work for existing “legacy” servers with non-standard drive configurations.

Below is the modified script, It will create a database called dbLogging in the default data and log directories with a few other settings, which may or may not be what you require so get modding.  Yes it does assume that the database xabcx123xdbxdefaultxfilexpathx321xcbax does not exist, to be honest I don’t really care, if someone has a database with that name then I would love to know why.  If you do then you just need to do a find and replace to something more unconventional like say dbAdmin or dbSQLAdmin.

/*

      -----------------------------------------------------------------

      Create database with files in default the directories

      -----------------------------------------------------------------

   

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- set database context

USE master;

GO

-- Create a temp database

CREATE DATABASE xabcx123xdbxdefaultxfilexpathx321xcbax;

-- Declare variables

DECLARE @DefaultDataFilePath VARCHAR(512)

DECLARE @DefaultLogFilePath VARCHAR(512)

DECLARE @DatabaseName VARCHAR(512)

DECLARE @DataFileName VARCHAR(517)

DECLARE @LogFileName VARCHAR(517)

DECLARE @DataFileExtension VARCHAR(4)

DECLARE @LogFileExtension VARCHAR(4)

DECLARE @SQL VARCHAR(4000)

/*

      *** THIS IS ALL YOU NEED TO SPECIFY ***

*/

SET @DatabaseName = 'dbLogging'

-- Set variables

SET @DataFileName = @DatabaseName + '_Data'

SET @LogFileName = @DatabaseName + '_Log'

SET @DataFileExtension = '.mdf'

SET @LogFileExtension = '.ldf'

-- Get the default data path  

SELECT @DefaultDataFilePath =   

(   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)

    FROM sys.master_files mf  

    INNER JOIN sys.databases d  

    ON mf.database_id = d.database_id  

    WHERE d.[name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' AND type = 0);

-- Get the default log path  

SELECT @DefaultLogFilePath =   

(   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)  

    FROM sys.master_files mf  

    INNER JOIN sys.databases d  

    ON mf.database_id = d.database_id

    WHERE d.[name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' AND type = 1);

-- Drop the temp database

IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' )  

BEGIN 

    DROP DATABASE xabcx123xdbxdefaultxfilexpathx321xcbax  

END;

-- If the database already exists print message tro client

IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = @DatabaseName) 

BEGIN

      -- Print message if database already exists

      PRINT 'Database ' + @DatabaseName + ' already exists on ' + @@SERVERNAME

END;

-- Build up SQL string to create database

IF NOT EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = @DatabaseName)  

BEGIN

      SET @SQL = 'CREATE DATABASE ' + '[' + @DatabaseName + ']' +'

      ON

      ( NAME = ' + '[' + @DataFileName + ']' + ',

            FILENAME = '''+ @DefaultDataFilePath + @DataFileName + @DataFileExtension + ''',

            SIZE = 1024MB,

            MAXSIZE = UNLIMITED,

            FILEGROWTH = 10% )

      LOG ON

      ( NAME = ' + '[' + @LogFileName + ']' + ',

            FILENAME = '''+ @DefaultLogFilePath + @LogFileName + @LogFileExtension + ''',

            SIZE = 1024MB,

            MAXSIZE = UNLIMITED,

            FILEGROWTH = 10% )'

-- Create the database

--Try Catch block to create database                 

      BEGIN TRY

            EXEC ( @SQL )

            PRINT @DatabaseName + ' has been created on ' + @@SERVERNAME

      END TRY

      BEGIN CATCH

            SELECT  @DatabaseName message_id,

                        severity,

                        [text],

                        @SQL

            FROM    sys.messages

            WHERE   message_id = @@ERROR

                        AND language_id = 1033 --British English

      END CATCH

END;

GO

Enjoy!

Chris

tablediff Utility

I recently stumbled across a little gem of a utility called tablediff.  I have been working a lot with replication in my current position and recently had to setup merge replication between 3 servers on our WAN.  For those of you who have used replication in anger Replication Monitor doesn’t do a very good job of refreshing, especially for large publications or subscribers with quite a high latency, this is even more apparent during the delivery of a snapshot.

This particular publication was quite sizable and one particular subscriber was on a different continent so I was a little concerned about monitoring the delivery of the snapshot, now I could have used the below script to check the date and time the tables where created and also get the record count from each table on the subscriber;

-- Set database context

USE SQLServer365;

GO

-- get created date of user tables

SELECT name, create_date

FROM sys.tables where is_ms_shipped = 0

-- Get record count for all user tables

SELECT o.name 'TableName', p.[rows] 'Rows'

FROM sys.objects o

JOIN sys.partitions p ON o.object_id = p.object_id

WHERE (o.[type] = 'U')

AND o.is_ms_shipped = 0

AND (p.index_id IN (0,1))

ORDER BY p.[rows] DESC;

GO

I could have then compared this with the publisher to make sure they were in sync.  I didn’t though, instead I decided to use tablediff, which is fantastic!  BOL describes tablediff as;

The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:

·         A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.

·         Perform a fast comparison by only comparing row counts and schema.

·         Perform column-level comparisons.

·         Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.

·         Log results to an output file or into a table in the destination database.

Let s setup a merge publication and subscribe to it so we can use tablediff.  I have two databases ReplicationTest and SQLServer365 on my local instance of SQL Server 2008.  ReplicationTest has a publication called MtoC_M (MastertoChild_Merge the naming convention adopted by my company for publications) which comprises of one article a table called MergeTest.  SQLServer365 is the subscriber database, the definition of the table and replication topology is below;

The MergeTest table can be created using the below;

-- Set database context

USE [ReplicationTest]

GO

-- Drop table if it exixts

IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[MergeTest]') AND [type] in (N'U'))

DROP TABLE [dbo].[MergeTest]

GO

-- Create table

CREATE TABLE [dbo].[MergeTest](

      [MergeTestID] [int] IDENTITY(1,1),

      [Tester] [varchar](255) NULL,

      [ROWGUID] [uniqueidentifier] ROWGUIDCOL  NULL,

 CONSTRAINT [PK_MergeTest:MergeTestID] PRIMARY KEY CLUSTERED

(

      [MergeTestID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY];

GO

ALTER TABLE [dbo].[MergeTest] ADD  CONSTRAINT [MSmerge_default_constraint_for_rowguidcol_of_2137058649]  DEFAULT (newsequentialid()) FOR [ROWGUID]

GO

The table contains 10,000 records which can be inserted with below;

-- Set database context

USE [ReplicationTest];

GO

-- Insert 10,000 records

INSERT INTO MergeTest (Tester)

VALUES ('Testing Merge Replication')

GO 10000

The publication can be created using the below;

-- Set database context

USE [ReplicationTest];

GO

-- Create publication

EXEC sp_replicationdboption @dbname = N'ReplicationTest', @optname = N'merge publish', @value = N'true'

GO

EXEC sp_addmergepublication @publication = N'MtoC_M', @description = N'Merge publication of database ''ReplicationTest'' from Publisher ''MANL003150''.', @sync_mode = N'native', @retention = 14, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_subdirectory = N'ftp', @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'false', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = null, @publication_compatibility_level = N'100RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'false', @allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0

GO

EXEC sp_addpublication_snapshot @publication = N'MtoC_M', @frequency_type = 4, @frequency_interval = 14, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 500, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

GO

EXEC sp_addmergearticle @publication = N'MtoC_M', @article = N'MergeTest', @source_owner = N'dbo', @source_object = N'MergeTest', @type = N'table', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = null, @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

The subscription can be created using the below;

-- Set database context

USE [ReplicationTest];

GO

-- Create subscription

EXEC sp_addmergesubscription @publication = N'MtoC_M', @subscriber = N'manl003150', @subscriber_db = N'SQLServer365', @subscription_type = N'Push', @sync_type = N'Automatic', @subscriber_type = N'Global', @subscription_priority = 75, @description = null, @use_interactive_resolver = N'False'

EXEC sp_addmergepushsubscription_agent @publication = N'MtoC_M', @subscriber = N'manl003150', @subscriber_db = N'SQLServer365', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @publisher_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20120822, @active_end_date = 99991231, @enabled_for_syncmgr = N'False'

GO

Next we need to initialise the subscription from a new snapshot, again I’m using replication monitor for this because I’m lazy.  Ok so now we have a synchronizing merge replication topology let’s take a look at tablediff.

Start with the below;

tablediff -sourceserver MANL003150 -sourcedatabase ReplicationTest -sourcetable MergeTest -sourceschema dbo -sourcelocked  -destinationserver MANL003150 -destinationdatabase SQLServer365  -destinationtable MergeTest -destinationschema dbo -destinationlocked -o D:\ReplTableDiff\ReplicationTest-MtoC_M.txt

You will need to update the values for the below accordingly;

–sourceserver

-sourcedatabase

-destinationserver

-destinationdatabase

-o

There are also 4 more parameters you will need to add if you are using SQL authentication;

-sourceuser

-sourcepassword

-destinationuser

-destinationpassword

NOTE

Add -sourcepassword and -sourceuser before –sourcelocked

Add –destinationuser and -destinationpassword before -destinationlocked

Once you have updated the command open a command prompt window and browse to the COM directory of your SQL Server installation directory, mine is below;

C:\Program Files\Microsoft SQL Server\100\COM\

Paste the command and press enter, tablediff will analyse the schema and records of both tables and the output will be written to the output file you specified in the command mine is below;

Table [ReplicationTest].[dbo].[MergeTest] on MANL003150 and Table [SQLServer365].[dbo].[MergeTest] on MANL003150 are identical.

The requested operation took 0.2971372 seconds.

Result, but what about when the tables have different records, well let’s stop replication insert some records and run table diff again, I’ve just stopped replication from replication monitor as I’m running out of time, and below is the script to insert 10,000 more records;

-- Set database context

USE [ReplicationTest];

GO

-- Insert 10,000 records

INSERT INTO MergeTest (Tester)

VALUES ('Testing Merge Replication')

GO 10000

Right now run tablediff again, no need to change the output file name as it will just append, and voila, 10,000 differences followed by the ID’s of the differences, my output is below;

Table [ReplicationTest].[dbo].[MergeTest] on MANL003150 and Table [SQLServer365].[dbo].[MergeTest] on MANL003150 have 10000 differences.

Err          MergeTestID

Src. Only              10001

Src. Only              10002

Src. Only              10003

Src. Only              10004

Src. Only              10005

Awesome or what?! What’s that, how about schema differences, well let’s take a look.  I’ve just started replication synchronizing again so that we don’t get another 10,000 records to scroll through in the output file, make sure you stop synchronising once the 10,000 records have synched though.

-- Set database context

USE [ReplicationTest];

GO

-- Update schema

ALTER TABLE MergeTest

ADD SchemaDiff INT NULL

So if you run tablediff again, no need to change the output file name as it will just append.  Well would you take a look at that the tables have different schemas and cannot be compared.

Table [ReplicationTest].[dbo].[MergeTest] on MANL003150 and Table [SQLServer365].[dbo].[MergeTest] on MANL003150 have different schemas and cannot be compared.

The requested operation took 0.1407231 seconds.

The next thing I want to show you is how to record the differences between the two tables.  Start replication synchronising again so the schema change can replicate, once this has replicated stop replication again and insert some more records using the below;

-- Set database context

USE [ReplicationTest];

GO

-- Insert 10,000 records

INSERT INTO MergeTest (Tester)

VALUES ('Testing Merge Replication')

GO 10000

Then run the below to record the differences in a .sql file MergeTestDiff.sql

tablediff -sourceserver MANL003150 -sourcedatabase ReplicationTest -sourcetable MergeTest -sourceschema dbo -sourcelocked  -destinationserver MANL003150 -destinationdatabase SQLServer365  -destinationtable MergeTest -destinationschema dbo -destinationlocked -dt -et MergeTestDiff -f D:\ReplTableDiff\MergeTestDiff.sql

And hey presto we have a .sql file with the insert statements to update the subscriber.

-- Host: MANL003150

-- Database: [SQLServer365]

-- Table: [dbo].[MergeTest]

SET IDENTITY_INSERT [dbo].[MergeTest] ON

INSERT INTO [dbo].[MergeTest] ([MergeTestID],[ROWGUID],[SchemaDiff],[Tester]) VALUES (42001,'11fcecd2-0503-e211-862c-54766c3ed109',Null,N'Testing Merge Replication')

INSERT INTO [dbo].[MergeTest] ([MergeTestID],[ROWGUID],[SchemaDiff],[Tester]) VALUES (42002,'12fcecd2-0503-e211-862c-54766c3ed109',Null,N'Testing Merge Replication')

INSERT INTO [dbo].[MergeTest] ([MergeTestID],[ROWGUID],[SchemaDiff],[Tester]) VALUES (42003,'13fcecd2-0503-e211-862c-54766c3ed109',Null,N'Testing Merge Replication')

INSERT INTO [dbo].[MergeTest] ([MergeTestID],[ROWGUID],[SchemaDiff],[Tester]) VALUES (42004,'14fcecd2-0503-e211-862c-54766c3ed109',Null,N'Testing Merge Replication')

The penultimate thing I am going to cover in this post is to log to a table, for this we need two parameters;

-dt this tells tablediff to drop the table if it already exists

-et creates the table to output the results too

So my command becomes;

tablediff -sourceserver MANL003150 -sourcedatabase ReplicationTest -sourcetable MergeTest -sourceschema dbo -sourcelocked  -destinationserver MANL003150 -destinationdatabase SQLServer365  -destinationtable MergeTest -destinationschema dbo -destinationlocked -dt -et MergeTestDiff

With replication still NOT synchronising run the above command, this will create a table called MergeTestDiff with the same output we saw earlier in our output file, to take a look run the below;

-- Set database context

USE [SQLServer365];

GO

-- Take a look at MergeTestDiff

SELECT TOP 100 *

FROM SQLServer365.dbo.MergeTestDiff

GO

MergeTestID  MSdifftool_ErrorCode            MSdifftool_ErrorDescription

52001              2                                                    Src. Only

52002              2                                                    Src. Only

52003              2                                                    Src. Only

52004              2                                                    Src. Only

52005              2                                                    Src. Only

I must say in case you haven’t already guessed but at this point I am in complete awe of this utility.

Finally below is a script to generate the command line commands to compare all merge articles;

-- Set database context

USE ReplicationTest

GO

-- Generate command line commands for all merge articles

SELECT '"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver [MANL003150] -sourcedatabase [ReplicationTest] -sourcetable [' + name + '] -sourceschema [dbo] -sourcelocked [TABLOCK] -destinationserver [MANL003150] -destinationdatabase [SQLServer365] -destinationtable [' + name + '] -destinationschema [dbo] -destinationlocked [TABLOCK] -f D:\ReplTableDiff\' + CAST(name AS VARCHAR(100))+ '.sql'

FROM sysmergearticles

GO

The results can be copied and executed or used in a batch file there are a lot of uses for this utility and I hope this post has given you an insight into it.

Enjoy!

Chris