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

High Availability Lingo

You have got to love the way the SQL Server team change the lingo in all of their high availability (HA) / disaster recovery (DR) features.  To a novice and even to more seasoned DBA’s it can be confusing, this post will not go into the nitty gritty of how each of these features work but just simply explain the terms commonly used.  I will cover the most common terms used with;

• Log Shipping
• Replication
• Database Mirroring
• AlwaysOn Availability Groups

  

Log Shipping

Primary Server

The primary server in a log shipping configuration is the instance of the SQL Server Database Engine that is your production server.

Primary Database

                The primary database is the database on the primary server that you want to back up to another server.

Secondary Server

                The secondary server in a log shipping configuration is the server where you want to keep a warm standby copy of your primary database.

Secondary Database

                The secondary database is the database on the secondary server that exists as a warm standby of the primary database.

Monitor Server

                The optional monitor server tracks all of the details of log shipping.

Backup Job

                A backup job is created on the primary server instance for each primary database. It performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information.

Copy Job

A copy job is created on each secondary server instance in a log shipping configuration. This job copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server.

Restore Job

A restore job is created on the secondary server instance for each log shipping configuration. This job restores the copied backup files to the secondary databases.

Alert Job

                If a monitor server is used, an alert job is created on the monitor server instance. This alert job is shared by the primary and secondary databases of all log shipping configurations using this monitor server instance.

Replication

Publisher

The Publisher is a database instance that makes data available to other locations through replication.

Publication

                A publication is a collection of one or more articles from one database.

Article

                An article identifies a database object that is included in a publication.

Distributor

                The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers. Each Publisher is associated with a single database (known as a distribution database) at the Distributor.

Subscriber

                A Subscriber is a database instance that receives replicated data.

Subscription

                A subscription is a request for a copy of a publication to be delivered to a Subscriber.

Transactional Replication

                Transactional replication typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.

Merge Replication

                Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

Snapshot Replication

                Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Peer to Peer Replication

                Peer-to-peer transactional replication lets you insert, update, or delete data at any node in a topology and have data changes propagated to the other nodes. Because you can change data at any node, data changes at different nodes could conflict with each other. If a row is modified at more than one node, it can cause a conflict or even a lost update when the row is propagated to other nodes.

Snapshot Agent

                The Snapshot Agent is typically used with all types of replication. It prepares schema and initial data files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent runs at the Distributor.

Log Reader Agent

                The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

Distribution Agent

                The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers.

Merge Agent

                The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both.

Queue Reader Agent

                The Queue Reader Agent is used with transactional replication with the queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber back to the Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution database.

Database Mirroring

Principal Server

The Principal Server instance serves the database to clients.

Mirror Server

                The Mirror Server instance acts as a hot or warm standby server.

Witness

                High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning.

Hot Standby

                Hot Standby is the term used when a database mirroring session is synchronised database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions.

Warm Standby

                Warm Standby is the term used when a database mirroring session is not synchronized; the mirror server is typically available as a warm standby server (with possible data loss).

Operating Modes

There are two mirroring operating modes. One of them, high-safety mode supports synchronous operation.  The second operating mode, high-performance mode, runs asynchronously.

High-Safety

                Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible.

High-Performance

                Under high-performance mode, the mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small.

Synchronous

                Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.

Asynchronous

Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance.

Transaction Safety

                If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase. If a witness is set in high-safety mode, the session supports automatic failover.  If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode, and the WITNESS option should also be OFF.

Role Switching

                Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server.

AlwaysOn Availability Groups

Availability Group

A container for a set of databases, availability databases, that fail over together.

Availability Database

A database that belongs to an availability group. For each availability database, the availability group maintains a single read-write copy (the primary database) and one to four read-only copies (secondary databases).

Primary Database

The read-write copy of an availability database.

Secondary Database

A read-only copy of an availability database.

Availability Replica

An instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group. Two types of availability replicas exist: a single primary replica and one to four secondary replicas.

Primary Replica

The availability replica that makes the primary databases available for read-write connections from clients and, also, sends transaction log records for each primary database to every secondary replica.

Secondary Replica

An availability replica that maintains a secondary copy of each availability database, and serves as a potential failover targets for the availability group. Optionally, a secondary replica can support read-only access to secondary databases can support creating backups on secondary databases.

Availability Group Listener

A server name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica.

Availability Modes

                In AlwaysOn Availability Groups, the availability mode is a replica property that determines whether a given availability replica can run in synchronous-commit mode. For each availability replica, the availability mode must be configured for either synchronous-commit mode or asynchronous-commit mode.

Asynchronous-commit

Is a disaster-recovery solution that works well when the availability replicas are distributed over considerable distances.

Synchronous-commit

Emphasizes high availability over performance, at the cost of increased transaction latency.

Automatic failover

A failover that occurs automatically on the loss of the primary replica. Automatic failover is supported only when the current primary and one secondary replica are both configured with failover mode set to AUTOMATIC and the secondary replica currently synchronized. If the failover mode of either the primary or secondary replica is MANUAL, automatic failover cannot occur.

Planned manual failover (without data loss)

Planned manual failover, or manual failover, is a failover that is initiated by a database administrator, typically, for administrative purposes. A planned manual failover is supported only if both the primary replica and secondary replica are configured for synchronous-commit mode and the secondary replica is currently synchronized (in the SYNCHRONIZED state). When the target secondary replica is synchronized, manual failover (without data loss) is possible even if the primary replica has crashed because the secondary databases are ready for failover. A database administrator manually initiates a manual failover.

Forced manual failover (with possible data loss)

A failover that can be initiated by a database administrator when a planned manual failover is not possible, because either no secondary replica is SYNCHRONIZED with the primary replica (that is, no secondary replica is ready for failover) or the primary replica is not running. Forced manual failover, or forced failover, risks possible data loss and is recommended strictly for disaster recovery. This is the only form of failover supported by in asynchronous-commit availability mode.

Automatic failover set

Within a given availability group, a pair of availability replicas (including the current primary replica) that are configured for synchronous-commit mode with automatic failover, if any. An automatic failover set takes effect only if the secondary replica is currently SYNCHRONIZED with the primary replica.

Synchronous-commit failover set

Within a given availability group, a set of two or three availability replicas (including the current primary replica) that are configured for synchronous-commit mode, if any. A synchronous-commit failover set takes effect only if the secondary replicas are configured for manual failover mode and at least one secondary replica is currently SYNCHRONIZED with the primary replica.

Entire failover set

Within a given availability group, the set of all availability replicas whose operational state is currently ONLINE, regardless of availability mode and of failover mode. The entire failover set becomes relevant when no secondary replica is currently SYNCHRONIZED with the primary replica.

Enjoy!

Chris

Cost Savings and Backup Compression

Let me set the scene, one of our internal IT SQL Servers which stores a whole host of performance metrics has over the last few months’ experienced tremendous growth as we have started to monitor more metrics on more servers.  A routine we have in place to collect and report on a multitude of server and database information including database sizes and growth highlighted this to me along with a significant decrease in free space on the backup volume.  We use SQL Server to back up our databases as opposed to a third party product as any benefits we may gain are outweighed by the cost.

When checking the backup routine on the server I noticed that we were not using backup compression.  Rather than allocating more expensive SAN storage to the volume I turned on backup compression, checking the server a day later I was pleased to report an 80% saving in cumulative backup size across the server, Happy Times! J

You can use the script below to calculate the backup compression percentage;

/*

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

      Calculate backup compression percentage per backup

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

    

      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 msdb;

GO

-- Calculate compression ratio of all backups taken in the last 24 hours

SELECT (((backup_size - compressed_backup_size) / backup_size) * 100) AS CompressionPercentage, database_name, [type], backup_start_date

FROM msdb.dbo.backupset

WHERE backup_start_date > GETDATE()-1

ORDER BY backup_start_date DESC;

GO

Backup compression was introduced in SQL Server 2008 and although it may not have as many bells and whistles as some third party vendors it is still an extremely valuable addition to SQL Server and one which isn’t is used nearly as much as believe it should.  The saving alone in disk space makes it a no brainer surely?!  I mean 80% saving in storage across a single server is something we as DBA’s can ill afford to ignore, turning backup compression on, on an additional 4 servers this did however drop slightly to 71% but that is still a huge saving.  Let’s say that per 1TB of enterprise storage costs £5,000 even with a 50% saving using backup compression that is £2,500 saved that can be used elsewhere for training courses, conventions, books etc.  Add to this the fact that because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.

The amount of compression you achieve will vary depending on the below factors;

·         The type of data.

Character data compresses more than other types of data.

·         The consistency of the data among rows on a page.

Typically, if a page contains several rows in which a field contains the same value, significant compression might occur for that value. In contrast, for a database that contains random data or that contains only one large row per page, a compressed backup would be almost as large as an uncompressed backup.

·         Whether the data is encrypted.

Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.

·         Whether the database is compressed.

If the database is compressed, compressing backups might not reduce their size by much, if at all.

Before you blindly turn on backup compression there are a few things to be wary of though;

·         Compressed and uncompressed backups cannot co-exist in a media set.

·         Previous versions of SQL Server cannot read compressed backups.

·         NTbackups cannot share a tape with compressed SQL Server backups.

·         By default, compression significantly increases CPU usage.

As always, make sure any change you make has been thoroughly tested and any and all implications understood!

Enjoy!

Chris

Database Tables

Today I was asked for "a list of all tables in all databases" on a particular instance of SQL Server.  Knowing what was actually required was all "User" tables in all "Accessible" "User" databases I wrote the script below . It will return the ServerName, DatabaseName, SchemaName and TableName of all the user tables in all online read-writeable non system databases.

Hopefully someone will find this useful ;)

/*

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

      Get all user tables for all online, read-writable user databases

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

    

      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"

     

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

*/

-- Declare variables

DECLARE @MinDBID INT

DECLARE @MaxDBID INT

DECLARE @DatabaseName VARCHAR(100)

DECLARE @SQL VARCHAR(2000)

-- Check for temporary tables and drop them if they exists

IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL

    DROP TABLE [#Database] ;

IF OBJECT_ID('tempDB.dbo.#Table') IS NOT NULL

    DROP TABLE [#Table] ;

   

-- Create temporary tables

CREATE TABLE #Database

    (

      ID INT IDENTITY(1, 1),

      DatabaseName VARCHAR(100)

    );

CREATE TABLE #Table

    (

      ID INT IDENTITY(1, 1),

      ServerName VARCHAR(100),

      DatabaseName VARCHAR(255),

      SchemaName VARCHAR(255),

      TableName VARCHAR(255)

    );

   

-- Get online read/writeable user databases

INSERT  INTO #Database ( DatabaseName )

SELECT  name

FROM    sys.databases

WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB' )

AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE'

AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' ;

--Set variables loop            

SELECT  @MinDBID = MIN(ID),

        @MaxDBID = MAX(ID)

FROM    #Database

-- Begin loop

WHILE @MinDBID <= @MaxDBID

    BEGIN

            -- Get DatabaseName

        SELECT  @DatabaseName = DatabaseName

        FROM    #Database

        WHERE   ID = @MinDBID

           

            -- Set SQL to run for each database

            SET @SQL =  ' USE ' + @DatabaseName + ';' + '

                              INSERT INTO #Table

                              SELECT

                                    @@ServerName AS ServerName,

                                    DB_NAME() AS DatabaseName,

                                    s.name AS SchemaName,

                                    t.name AS TableName

                              FROM

                                    sys.tables AS t

                                    INNER JOIN sys.schemas as s on s.[schema_id] = t.[schema_id]

                              WHERE

                                    [type] = ''U''

                              AND is_ms_shipped = 0' + ';'

            -- Try catch block to execute SQL and handle errors              

            BEGIN TRY

                  -- Get table information

                  EXEC ( @SQL

                        )

            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

      -- Get the next database

    SET @MinDBID = @MinDBID + 1

-- End loop

END

-- Return results

SELECT      ID,

            ServerName,

            DatabaseName,

            SchemaName,

            TableName

FROM  #Table

ORDER BY DatabaseName, SchemaName, TableName ASC;

GO

Enjoy

Chris

Rebuilding System Databases

Today’s post is something I thought I would share as there is an awful lot of incorrect commands I have seen (and some even tried) in the past around rebuilding system databases.  Now first off let me explain that rebuilding system databases is no straight forward task.  I would avoid it at all costs if the server is currently in use, but if you absolutely have to refer to this Microsoft article for additional help.

The Server in question was built by someone other than a Database Administrator who from the config clearly didn’t have much knowledge of SQL Server.  To help them out I made a whole host of recommendations but the biggest of which was to change the collation.  I hate collations, it isn’t their fault they do a mighty fine job but can cause me and most of the other DBA’s I know so many problems especially when referencing objects across databases, instances and servers.

The version of SQL Server was SQL Server 2008 R2 Standard edition and was installed with a named instance, the command I used is below;

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceNameHere /SQLSYSADMINACCOUNTS="domain\user" /SAPWD="strongsapassword" /SQLCOLLATION=LATIN1_GENERAL_CI_AS

Open command prompt as an administrator and browse to the directory below (on the volume on which SQL Server is installed);

Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2

Execute the above command obviously replacing;

• InstanceNameHere 

• domain

• user 

• strongsapassword 

When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages. Examine the Summary.txt log file to verify that the process completed successfully. This file is located on the volume on which SQL Server is installed in the below directory;

Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

There should be a Summary.txt and a folder named in the format of yyyymmdd_hhmmss which will correspond to the date and time you ran setup.  This contains much more detail information about the setup process, if you run into any issues this is the first place I would start to look.

Cheers

Chris