Counter of Festivals

Ashok Blog for SQL Lovers

Thursday, 23 October 2014

How to find Delay/Latency between Publisher and Subscriber in SQL Server Transactional Replication?

Determine data latency between Publisher and Subscriber in SQL Server Transactional Replication


To find out what still needs to be replicated, we could use both Replication Monitor as well as T-SQL commands to find out how what needs to be replicated to the subscriber database. Both options assume transactional replication is already configured in your environment.

Option 1: Using Replication Monitor

In SQL Server Management Studio (SSMS), navigate to Replication and right click and select 'Launch Replication Monitor'. Go to your listed server and expand it. Click on your publication and on the right side pane under the 'All Subscriptions' tab, go to your subscription and double click on it. Once done, you would see this window.
Replication status as viewed on Replication Monitor
From this window, you could see the details of the activities that are occurring between the 'publisher-distributor' and 'distributor-subscriber' combinations. Click on the respective tabs to check the status of replication. If there are any replication commands that remain to be applied at the subscriber from the distribution database you would see the details in the 'Undistributed commands'. From this tab, you would get an idea of estimated time remaining to apply commands at the subscriber. Below is a sample screenshot.
Undistributed commands  as viewed from Replication Monitor
Replication Monitor gives you this simple interface to identify any issues you may face with your replication configuration. For more details related to replication latency, we could use some replication commands as shown below.

Option 2: Using Replication commands

Run this on publisher database
To check if replication is fine, we could run sp_repltrans on the publisher database. This displays the undistributed commands present in the publisher database. If your log reader agent is scheduled to run continuously and if this command returns no rows, replication is fine on the publisher side. However, if your log reader agent is scheduled to run at intervals and there are changes that need to be sent to the distribution database, you would see rows returned when you execute (during the interval) this procedure which shows the LSNs of the transactions. See sample screenshot below.
Output of  sp_repltrans when run on publisher database
On a busy server, if the log reader agent is running continuously, the rows that are returned after executing sp_repltrans would be constantly changing based on the load. By using this procedure, you wouldn't get the exact command that is pending but you would get an idea on the progress that is happening in your replication environment.
Run this on distribution database
The distribution database contains the system tables - MSrepl_commands and MSrepl_transactions which contain details of the replicated commands. Here is a sample output of a select query on these system tables.
select * from distribution.dbo.MSrepl_commands
select * from distribution.dbo.MSrepl_transactions

Select operation on MSrepl_commands and MSrepl_transactions
From the screenshot, we could see that the command column is of varbinary datatype and is non-readable. Hence, we could execute the sp_browsereplcmds system procedure in the distribution database with the relevant parameters and retrieve the data of the replicated commands in a user friendly manner.
In the above example, if we need to find out the actual command corresponding to xact_seqno = '0x00000085000002A10003' and command_id = 1, we could execute sp_browsereplcmds with these parameters. See screenshot and sample script below.
Use distribution
exec sp_browsereplcmds  @xact_seqno_start = '0x00000085000002A10003', 
                        @xact_seqno_end =   '0x00000085000002A10003', 
                        @publisher_database_id = '1', -- run sp_helppublication on publisher database                
                        @command_id = '1' -- command_id in MSrepl_commands table distribution database   

A sample execution of sp_browsereplcmds on 

distribution database
Here, we could see the actual command that was executed which was an insert statement. It is recommended to execute this procedure with the appropriate parameters otherwise it could result in generation of a huge number of rows as output.
In option 1, using replication monitor, we saw how to view the 'undistributed commands'. The same data could be obtained by executing sp_replmonitorsubscriptionpendingcmds in the distribution database. Use sample script below.
 @publisher ='Enter publisher server name',
 @publisher_db = 'Enter publisher database name',
 @publication ='Enter name of publication',
 @subscriber ='Enter subscriber server name',
 @subscriber_db ='Enter subscriber database name',
 @subscription_type ='0' --0 for push and 1 for pull 
Below is a sample output.
Sample output of executing sp_replmonitorsubscriptionpendingcmds


In your test replication environment, you could stop the distribution agent job and run a few insert commands in the publisher database. Once done, execute this procedure sp_replmonitorsubscriptionpendingcmds in the distribution database to find out the details of the pending commands to be applied on to the subscriber. Then proceed to query the MSrepl_commands and MSrepl_transactions system tables in the distribution database to determine the actual commands that are yet to be sent to the subscriber. Using MSrepl_transactions system table you could get the time you had executed the insert statements in your publisher database. Then make use of the sp_browsereplcmds as shown above to find out the full text of commands by providing the appropriate parameters. This would give an idea of the actual commands that are yet to be replicated to the subscriber.
You can also use this script to query the distribution system tables.
select rc.publisher_database_id, rc.xact_seqno, rc.command, rt.entry_time 
   from MSrepl_commands rc, MSrepl_transactions rt
    where rc.xact_seqno =  rt.xact_seqno 

It should be noted that the data available in MSrepl_commands, MSrepl_transactions, sp_browsereplcmds is purged periodically based on the schedule of the distribution clean up job and the distribution retention period.

Wednesday, 8 October 2014

The backup set holds a backup of a database other than the existing

The backup set holds a backup of a database other than the existing

Do not create blank database or dummy name database and restore from backupfile is diffrent then u can get above error

To avoid:

Do not create any empty or dummy name database

Just right click from databases and click Restore Database then type the name as u want and select backupfile location then choose overwrite option then click ok.

Thats it.

Thursday, 25 September 2014

Database Mirroring

Configure SQL Server Database Mirroring Using SSMS

It will allow failover of database in the event you lose your main SQL Server.

Before starting this, be sure that you have 3 SQL Servers in different locations for best results. 2 Servers need to have the identical SQL Server instance which means the same version (either Standard or Enterprise) also it's highly recommended that also the service pack and if any cumulative updates are the same on both servers. But for the third server, it can be SQL Server Standard, Enterprise, Workgroup, or Express. Witness Server will be the one pinging the other 2 servers if there's something wrong. This is the server that has the ability to recognize whether to initiate an automatic failover. This will not contain any database, that's why it's nonsense to use a SQL Server other than Express edition.
  1. Verify the following:
    1. You have 3 SQL Servers for Principal, Mirror and Witness
    2. SQL Server is using an Active Directory account. Ideally, use the same account for all SQL Servers.
    3. Primary Database is in Full Recovery model.
  2. Back up the database on the Principal SQL Server.
  3. Create a database with the same name from the Principal SQL Server on the Mirroring SQL Server, then restore the backup on the Mirroring SQL Server with the option to Overwrite the existing database checked and RESTORE WITH NORECOVERY option.


In this tip I am going to outline my environment and then walk through the process of setting up Database Mirroring.  This will include the configurations, backups, restores and verification process.  Let's jump in.
My test environment consists of two separate VM's running VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server 2008 R2 Enterprise named appropriately Principal and Mirror. The SQL Server and SQL Server Agent Services accounts are running as domain users (DOMAIN\User). Windows Firewall is OFF for the sake of this example.
I created a database on the Principal SQL Server instance and named it TestMirror. The recovery model is set to FULL RECOVERY.
BACKUP DATABASE TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
BACKUP LOG TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 

Below are the two files in the file system:
3rd step: Assuming you have the backup folder shared on the Principal Server and you can access it from the Mirror Server, you will need to restore the full backup to the Mirror server with the NORECOVERY option.
RESTORE DATABASE TestMirror FROM DISK = N'\\Principal\Backup\Backup.bak' 
WITH FILE = 1, MOVE N'TestMirror_log' TO 
N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestMirror_1.ldf', 
RESTORE LOG TestMirror FROM DISK = N'\\Principal\Backup\Backup.trn' 

Now it's time to dig down and configure Database Mirroring. From the Principal server, right click the database and choose "Tasks" | "Mirror" or choose "Properties" | "Mirroring".
Click the "Configure Security" button and click "Next >" if the Configure Database Mirroring Security Wizard intro screen appears. The next screen should be the Include Witness Server screen:
This is where you would configure a witness server for your mirroring, but since we're just configuring a basic mirror we will skip this part. However, if you are configuring mirroring in an Enterprise environment it is recommended you configure a witness server because without one you will not have synchronous automatic failover option.
Select "No", then click "Next >" to continue the process.
The next screen will give you options to configure the Principal Server Instance:
Here we will be creating our endpoint, which is a SQL Server object that allows SQL Server to communicate over the network. We will name it Mirroring with a Listener Port of 5022.
Click the "Next >" button to continue.
The next screen will give you options to configure the Mirror Server Instance:
To connect to the Mirror server instance we will need to click the "Connect..." button then select the mirror server and provide the correct credentials:
Once connected, we also notice our endpoint name is Mirroring and we are listening on port 5022.
Click "Next >" and you'll see the Service Accounts screen.
When using Windows Authentication, if the server instances use different accounts, specify the service accounts for SQL Server. These service accounts must all be domain accounts (in the same or trusted domains).
If all the server instances use the same domain account or use certificate-based authentication, leave the fields blank.
Since my service accounts are using the same domain account, I'll leave this blank.
Click "Finish" and you'll see a Complete the Wizard screen that summarizes what we just configured. Click "Finish" one more time.
If you see the big green check mark that means Database Mirroring has been configured correctly. However, just because it is configured correctly doesn't mean that database mirroring is going to start...
Next screen that pops up should be the Start/Do Not Start Mirroring screen:
We're going to click Do Not Start Mirroring just so we can look at the Operating Modes we can use:
Since we didn't specify a witness server we will not get the High Safety with automatic failover option, but we still get the High Performance and High Safety without automatic failover options.
For this example, we'll stick with synchronous high safety without automatic failover so changes on both servers will be synchronized.
Next, click "Start Mirroring" as shown below.
If everything turned out right, Database Mirroring has been started successfully and we are fully synchronized.
Mirror15 Mirror16
If Database mirroring did not start successfully or you received an error here are a few scripts to troubleshoot the situation:
Both servers should be listening on the same port. To verify this, run the following command:
SELECT type_desc, port 
FROM sys.tcp_endpoints;
We are listening on port 5022. This should be the same on the Principal and Mirror servers:
Database mirroring should be started on both servers. To verify this, run the following command:
SELECT state_desc
FROM sys.database_mirroring_endpoints;
The state_desc column on both the Principal and Mirror server should be started:
To start an Endpoint, run the following:
ALTER ENDPOINT <Endpoint Name>
AS TCP (LISTENER_PORT = <port number>)
FOR database_mirroring (ROLE = ALL);
ROLES should be the same on both the Principal and Mirror Server, to verify this run:
SELECT role 
FROM sys.database_mirroring_endpoints;

To verify the login from the other server has CONNECT permissions run the following:
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
FROM sys.server_permissions  SP , sys.endpoints EP
WHERE SP.major_id  = EP.endpoint_id
ORDER BY  Permission,grantor, grantee;

You can see here from the State and Permissions column that the user has been Granted Connect permissions.

How to get Accurate Data when Datetime Column used in Tables?

How to get Accurate Data when Datetime Column used in Tables?

Wednesday, 24 September 2014

How to insert Identity values and not Identity values on table have Identity column?

Cannot insert explicit value for identity column in table 'TestIdentity' when IDENTITY_INSERT is set to OFF.

Some times we have scenario like we have source table and destination table
we have to insert source to destination table(have identity on both)

1) Insert identity values as per destination(Ex: 1,5,8,9,10 etc)

2) Insert values without consider identity values as per source.(Ex: 1,2,3,4,5 etc)

1) Insert Identity values in Source as per destination

Two methods:

SSMS Method:

Let see an example

See the source and destination table

So now u can use SSMS(SQL Server Management Studio) method

Just right click table and click Modify or Design and change the identity of column to be set to off

as see below and save the table.

 Now insert some 10 records into destination table as see below:

So table looks like this

T-SQL Method:

We can use this method when Identity column should be ON status.
you  can ensure identity column should be on

but when u insert values it will throw as see below

Use code as see below


after that u can check property of table identity column is Now insert values as see below

2) We should insert values without consider identity values as per source.

When identity column is ON status u can insert values but u should not mention identity column in select list then u can get identity values as see below.

Additional Info:

An explicit value for the identity column in table 'products_copy' can only be specified when a column list is used and IDENTITY_INSERT is ON.


we have to specify full column list

insert into tablename(specify here)
select spectify here from tablename


Wednesday, 17 September 2014

Move Database Files MDF and LDF to Another Location


When a novice DBA or Developer create a database they use SQL Server Management Studio to create new database. Additionally, the T-SQL script to create a database is very easy as well. You can just write CREATE DATABASE DatabaseName and it will create new database for you. The point to remember here is that it will create the database at the default location specified for SQL Server Instance (this default instance can be changed and we will see that in future blog posts). Now, once the database goes in production it will start to grow.

It is not common to keep the Database on the same location where OS is installed. Usually Database files are on SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges comes up when database which was installed at not preferred default location and needs to move to a different location. Here is the quick tutorial how you can do it.
Let us assume we have two folders loc1 and loc2. We want to move database files from loc1 to loc2.
USE MASTER;GO-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
Now move the files from loc1 to loc2. You can now reattach the files with new locations.
-- Move MDF File from Loc1 to Loc 2
-- Re-Attached DB
FILENAME N'F:\loc2\TestDB_log.ldf' )FOR ATTACH
Well, we are done. There is little warning here for you: If you do ROLLBACK IMMEDIATE you may terminate your active transactions so do not use it randomly. Do it if you are confident that they are not needed or due to any reason there is a connection to the database which you are not able to kill manually after review.

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON.

An explicit value for the identity column in table 'products_copy' can only be specified when a column list is used and IDENTITY_INSERT is ON.


we have to specify full column list

insert into tablename(specify here)
select spectify here from tablename