:::: MENU ::::

Posts Categorized / dbDigger

  • Sep 19 / 2017
  • 0
dbDigger, High Availability, Replication, SQL Server tools, SQL Server Training

SQL Server Replication refresher

During phase of training material preparation, a brief overview of replication architecture is with me. In coming lines i am going to put it in this post as a good starting point for replication learners.


Replication is a way of distributing data automatically from a source database to one or more recipient databases. It is built to sync the selected objects of a database not whole DB.  It encompasses set of objects in a database whose data is required to keep sync at subscriber databases. Replication architecture is not part of the SQL Server database engine, but a separate feature or application. It works through agents, triggers and scheduled jobs specific for this purpose. Replication can have uses in high availability systems or in a distributed system and can have different toplogies.

There are 3 main types of replication

  • Transactional Replication (one or two way)
  • Merge Replication
  • Snapshot Replication

Transactional Replication

This type of SQL Server replication is used for near real time or short delayed synchronization from publisher and subscriber. Initially a snapshot process synchronizes the subscribers with the publisher. All committed transactions on the publisher are then propagated to the subscribers in sequence. It may be one way transactional replication or two way.

In one way replication, modifications from publisher are synced on subscriber but no modification from subscriber is propagated back to publisher. While in case of peer to peer or two way replication, changes are propagated both ways and data remains sync on both sides for selected articles.

There are two options while propagating changes back from subscriber to publisher.

  • Immediate updating
  • Queued updating

Both methods can be used at the same time to maximize the availability and efficiency.

Immediate updating

In case of immediate updating changes made at the subscriber are synced to the publisher using a two-phase commit process managed by Microsoft Distributed Transaction Coordinator (MS DTC). Changes made at the subscriber must be made to both systems or are rolled back. This method is suitable for reliable, high-speed connection between the Publisher and Subscriber.

Queued updating

This method is suitable for less reliable connections. Writes updates at the subscriber to a queue. Publisher reads the queue and applies changes. This flow might result in conflicts due to latency.


Peer to Peer replication

Peer to peer replication was introduced in SQL Server 2005 and only supported in Enterprise edition of SQL Server version. It resembles in some features features with two way transactional replication. Main difference is that peer to peer replication involves more than just single publisher and associated subscriber nodes. It can span on multiple nodes and each will be kept in Sync. Also peer to peer replication is better while propagating the DDL of articles and locking mechanism.

Merge Replication

Merge replication is relatively complex type of replication. It does not use transactions but a set of defined rules used to resolve conflicts when data updated at multiple sites is synchronized. It tracks changes through triggers and metadata tables provides the means to implement part of a high-availability system. This also serves well the original purpose of serving mobile and disconnected users. It is designed for cases where the publishers are not in constant communication with the subscribers. After the initial snapshot synchronization, subsequent changes are tracked locally and the databases are merged when in contact using series of rules to resolve conflicts.


Snapshot Replication

Data changes are not tracked for snapshot replication. Each time a snapshot is applied, it completely overwrites the existing data. Snapshot replication process provides the initial synchronization for transactional and merge publications. In several cases, this initial synchronization is all that is necessary. Data is synced at defined intervals as opposed to constant transactional sync of other types.


Distribution Setup

Distribution is important setup in replication architecture. It has following components and terms.

Distribution server

Distribution server is a role where distribution setup (agent, jobs, DB) resides. Distribution server may be same as publisher or a dedicated server. For big and busy replication setups separate distributor server is recommended. We can mark a server as distributor before going to setup replication publisher and subscriber.

Distribution database

Distribution is system database for replication setup and is created automatically on server with distributor role. We can customize the DB name, files name, location and file size however. For transactional replication, the distribution database tracks changes made to publications. For merge replication it stores only synchronization history.

Distribution agent

Distribution agent is setup as a separate module and works in form of scheduled job. Distribution agent is a process that moves transactions and snapshots held in the distribution database to subscribers. Works in form of scheduled job under replication jobs category.


Replication components

There are some other terms and components that are always involved in replication setup.


Publisher is a server that makes data available for other servers, i.e. it is the server that makes data available for replication.


Publication can be a selection of tables, indexed view and stored procedures. Each publication can be replicated as a snapshot publication, a transactional publication or a merge publication. It is important to choose the publication articles on a valid base like size, relation of tables. There may be multiple publications in a single publisher.


An object included in a publication is called an article. Articles are combined on some basis to form a publication. Article can be an entire table, select rows from a table, specified columns from a table indexed view or a stored procedure. An article is a unit in publication.


Subscriber is the server that hosts the DB where data from publisher is being synced. There may be multiple subscribers against a single publisher. Subscriber may be on same server as that of publisher. Also there may be multiple subscriber databases on a single subscriber server.


Subscription relates to publication and goes to subscriber database. There may be multiple subscriptions against a single publisher and each will go to separate subscriber database. There are two types of subscriptions.

Pull Subscription

With pull subscription the subscriber asks the publisher for periodic updates. With pull subscription each subscriber can specify the best time for the update. Pull subscription is generally considered the better form of replication when a large number of subscribers are involved, or where the load on the LAN needs to be distributed.

Push Subscription

With push subscription the publisher pushes any changes made to the data out to the subscribers. Typically push subscription is used where changes need to be replicated to the subscribers shortly after the change occurs, i.e. where the replication of changes is required in near real time.

Replication Agents

As we have discussed several components of replication in above sections some agents were discussed as well. It looks suitable to list the agents so that they may give a clear picture of setup

  • Snapshot agent
  • Log Reader Agent
  • Distribution Agent
  • Merge Agent
  • Que Reader Agent

Replication effect on log file

It is important to know the relation and effects of replication on database log file. If replication falls in latency then log begins to accumulate in log file unless it is send to distributor. This may ultimately fill the log file and slow down the system in some cases. Situation is same when database is with any recovery model FULL or even SIMPLE.

Replication topologies

Topology is how the replication setup is designed and components are arranged. Following are different disgrams to give view of different toplogies.

SQL Server Replication Topology Central Distributor


SQL Server Replication Topology Central Publisher with Remote Distributor


SQL Server Replication Topology Central Publisher


SQL Server Replication Topology Central Subscriber


SQL Server Replication Topology Publishing Subscriber

  • Aug 21 / 2017
  • 0
Columnstore Indexes, dbDigger

Introduction to SQL Server Columnstore Indexes

Columnstore indexes are introduced in SQL Server 2012. These are used for large tables where primarily READ operations are performed. Suitable environment is DWH fact tables. Storing data in this structure may help to get 10 X read performance and 10 X compression as compared to traditional row structured storage of data.

In this technology data is physically stored, retrieved and managed in a columnar data format. There are two types of columnstore indexes

  • Clustered columnstore indexes (CCI)
  • Non clustered columnstore indexes

Clustered columnstore indexes (CCI)

are used for large tables in DWH where considerable size difference may be acheived by compression. And READ operations are intensive.

Non clustered columnstore indexes

are used for environment where OLTP load is also involved with analysis queries.

Creating columnstore index on existing table

While creating a clustered columnstore index (CCI) on a table there may be some issue due to data type or column type. For example we may not create CCI on a table with data type nvarchar(max) in it. Or with a computed column (persisted or non persisted) in that table.

Combining columnstore and rowstore indexes

Starting from SQL Server 2016 we can have non clustered columnstore indexes on a table with rowstore table. And we can also have non clustered rowstore indexes on a columnstore table.

Get columnstore indexes in a DB

We can get general information about columnstore indexes in a DB with following script

SELECT schema_name(o.schema_id)+'.'+o.name AS TableName, i.name AS IndexName, i.type_desc AS IndexType
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE i.type > = 4
ORDER BY TableName

Columnstore indexes on partitioned tables

Columnstore indexes can be implemented with partitioned tables by keeping these partition aligned.

  • Aug 09 / 2017
  • 0
dbDigger, Monitoring and Analysis, System Administration, Windows Server

Get disk report by using PsInfo utility

In a previous post we have discussed PsInfo utility is part of PsTools set.Today we will utilize Psinfo to get disk information on any machine. Any of PsTools utility can be used by simply unzipping on disk. I have unzipped all PsTools files in D drive and PsInfo is also among those.

Get disk report by using PsInfo utilityNo we can utilize the PsInfo to get disk information for local or any other connected machine.

  • Open cmd and go to unzipped PsTools folder
  • Execute psinfo \\MachineNameHere -D disk

It would generate general information about drives like label, type, format, Size and free size.

Run PsInfo in cmd


  • May 10 / 2017
  • 0

SQL Server Management Studio (SSMS) 2017 available now

SQL Server Management Studio (SSMS) 2017 is now available for download now. Click here to get it. Download page has further information about new features and compatibility.

Following are some highlights

This release features improved compatibility with previous versions of SQL Server, a stand-alone web installer, and toast notifications within SSMS when new releases become available.

SSMS 17.x can be installed side-by-side with SSMS 16.X or SQL Server 2014 SSMS and earlier.

Support SQL Server 2008 and onward. There is no explicit block for SQL Server 2000 or SQL Server 2005, but some features may not work properly.

Supports Windows 10, Windows 8, Windows 8.1, Windows 7 (SP1), Windows Server 2016, Windows Server 2012 (64-bit), Windows Server 2012 R2 (64-bit), Windows Server 2008 R2 (64-bit)

Click here to get full list of changes

Click here for list of known issues and work arounds

  • Mar 09 / 2017
  • 0
Database Migrations, dbDigger, T-SQL Scripts

Code snippets for database migration task

Following are some code snippets that i used during a database migration task from one server to another. Backup restore method is used in this migration task

DB list on source server

Get the database list on source server that are required to migrate

select name, user_access, state_desc, create_date
from sys.databases
where name in
( 'comma separated list of specific databases to be migrated')
order by name

Note DB properties

Note the important database properties on source server so that we may verify these on destination server after migration is complete.

select name, is_published, is_subscribed, is_cdc_enabled, is_trustworthy_on, is_read_only

from sys.databases where name in
( 'comma separated list of specific databases to be migrated')
order by name

Size of DBs

Get size of all databases that are to be migrated. It would help to estimate the disk capacity on destination and also the time estimate for backup and restore process.

SELECT DB_NAME(database_id) AS DatabaseName, type_desc,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
where DB_NAME(database_id) in
( 'comma separated list of specific databases to be migrated')
GROUP BY DB_NAME(database_id), type_desc
ORDER BY DB_NAME(database_id), type_desc DESC

Get which DBs are in Avaiability Group

AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
where dbcs.database_name in ('Comma seperated list of DBs to be migrated')

Set DBs to READ ONLY and Restricted mode on source server

Before backing up the databases we will do following operations for each DB. As here i have databases in AG so i also included command to set DB out of AG so that i may put it to READ ONLY mode next.


Backup Databases

We will take backup of all databases through following script. Go through the option used for backup and also put a valid path. Backup file name will be generated automatically.

-- Declare cursor for database names
SELECT name FROM sys.databases
WHERE name in
( 'Comma seperated list of databases')

-- Declare variable to hold DBName
declare @Db_name varchar(100)

OPEN BackupCursor
FETCH NEXT FROM BackupCursor INTO @Db_name


-- Backup code
declare @Backup_Path Varchar(500) = 'Full Backup Path here'

DECLARE @fileName VARCHAR(400)  = @Backup_Path +'\'+ replace(@@servername, '\','_' ) + '_' + @Db_name+ '_' + 'FULL'+ '_' + LEFT(CONVERT(VARCHAR,getdate(), 120), 10)
+'.BAK'   -- filename for backup

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'BACKUP DATABASE ['+ @Db_name+'] TO DISK ='+   ''''+@fileName+ ''' WITH COPY_ONLY, CHECKSUM, INIT,COMPRESSION, STATS = 10;'

EXEC (@sqlCommand)

RESTORE  verifyonly from disk = @fileName
print 'Backup file path is '+@fileName

FETCH NEXT FROM BackupCursor INTO @Db_name
-- Close and deallocate the cursor
CLOSE BackupCursor


Get restore command for each DB one by one

Put DB name in first line of script one by one and get the restore commands for last full backup of that specific database

     declare @db_name varchar(100) = 'DBName'

Declare @tmp table
(StartDT datetime, BKP_Type char(1), RestorePart Varchar(2000), FilePart varchar(max))

If exists (select name from sys.databases where name = @Db_name)

Insert @tmp
SELECT s.[backup_start_date] ,s.[type]
,'Restore database ' + s.[database_name] +'
From Disk = ''' + Replace (replace (B.[physical_device_name], 'SQL Backups 2', 'SQL Backups 2'), 'DROPOFF', 'RECOVERY') + '''
with replace, keep_cdc, recovery, stats =1 ' as RestorePart    ,
'      , Move ''' + f.[logical_name] + ''''
+ ' TO ''' + f.[physical_name] + '''' as FilePart
FROM [msdb].[dbo].[backupset] S
inner join [msdb].[dbo].[backupfile] F
on S.backup_set_id = f.backup_set_id
inner join [msdb].[dbo].[backupmediafamily] B
on s.media_set_id = b.media_set_id
where s.type ='d'
and s.database_name = @Db_name
and s.backup_start_date =
(select max(s.backup_start_date)
from [msdb].[dbo].[backupset] s
where s.type ='d' and s.database_name = @Db_name )
order by s.backup_set_id desc

--select * from @tmp

select distinct '-- Latest Full backup for database ' + @Db_name + ' is from ' + cast(StartDT as varchar(20))
from @tmp

Select '--Run this on DR replica for initial restore:' as [--RestoreStatement]
union all
Select Distinct RestorePart
from @tmp
union all
select FilePart from @tmp

Select 'Database ' + @Db_name + ' does not exist on ' + @@Servername
Set nocount off

Set databases to READ WRITE mode on destination server



Fix and verify the orphan users

-- Fix orphaned users in DB
<pre>EXEC     [dbo].[spFixOrphanUsers]    @DatabaseNM = 'DBNameHere'

-- Verify the orphaned users in DB
USE DBNameHere
sp_change_users_login @Action='Report'


Update the DB properties

Following scripts will generate commands to fix the specific database property.

-- Set DB owner to sa
select suser_sname(owner_sid) , 'ALTER AUTHORIZATION ON DATABASE::'+name+'  TO sa;'
from sys.databases
where suser_sname(owner_sid) &lt;&gt; 'sa'

-- Set compatibility level
from sys.databases
where COMPATIBILITY_LEVEL &lt;&gt; 120

-- Set AutoCreateStats
from sys.databases
where is_auto_create_stats_on = 0

-- Set AutoUpdateStats
from sys.databases
where is_auto_update_stats_on = 0



  • Mar 18 / 2015
  • 0
dbDigger, Monitoring and Analysis, SQL Server Filegroups

Get size of all databases on a server

Following are three scripts to get

  • Get size of all databases on a server
  • Get size of all databases in terms of data and log
  • Get size of all data and log files

Get Size of all databases

SELECT DB_NAME(database_id) AS DatabaseName,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
GROUP BY database_id
ORDER BY database_id

Get Size of all database in data and log category

SELECT DB_NAME(database_id) AS DatabaseName, type_desc,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
GROUP BY database_id, type_desc
ORDER BY database_id, type_desc DESC 

— Get Size of all data and log files

SELECT DB_NAME(database_id) AS DatabaseName, type_desc, name, physical_name,
cast( (size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DBToFiletr'
ORDER BY database_id, type_desc DESC, name 
Consult us to explore the Databases. Contact us