Written a script to pull list of each table in a subscription and when the last time the table replicated.
| use [distribution]
select sss.srvname PublisherServer,mss.publication_id,mss.publisher_db, |
While setting up critical Transactional Replication I came across this scenario of alerting the L3 DBA support team with Log Reader Agent Status and writing it to SQL Server Error Log.
When Log Reader Agent is Retrying to read the Database Log and sending it to Distribution Database and when it fails.
To achieve this I have written an after insert trigger on table
MSlogreader_history in Distribution Database.
To get the last status of the Log Reader Agent execute the below code on distributor database.
SELECT comments,runstatus FROM distribution.dbo.MSlogreader_history
where timestamp=(SELECT
MAX(timestamp) from MSlogreader_history)
—Trigger Code
CREATE TRIGGER LogReaderStatus
ON MSlogreader_history
AFTER INSERT
AS
BEGIN
DECLARE @LogReaderStatus int
DECLARE @LogReaderComments varchar(255)
DECLARE @LogReader varchar(255)
DECLARE @@Status varchar(255)SELECT @LogReaderComments=lrh.comments,@LogReaderStatus=lrh.runstatus,
@LogReaderComments=lra.name
FROM distribution.dbo.MSlogreader_history lrh INNER JOIN MSlogreader_agents lra
ON lrh.agent_id=lra.id and lrh.timestamp=(SELECT
MAX(i.timestamp) from INSERTED i)
IF @LogReaderStatus=5
SET @@Status=‘Log Reader Agent : ‘+ @LogReader+‘ is retrying…..’
BEGIN
EXEC xp_logevent 60000, @@Status, informational
END
IF @LogReaderStatus=6
SET @@Status=‘Log Reader Agent : ‘+ @LogReader+‘ Failed…….’
BEGIN
EXEC xp_logevent 60000,@@Status, informational
END
END
GO
While troubleshooting Replication issues it’s always a question till what point transaction has been replicated to subscription and from which point transaction are in distribution database waiting to be replicated to subscription database.
Replication Architecture
Let’s say distributor agent failed to replicate transaction from distributor to subscriber due to some reason and now after restarting the distributor agent it’s still not replicating the transactions.
There could be n number of reason due to which distributor agent may fail.
In this post we will focus on how to get last transaction replicated to subscriber database and what all transaction are waiting in the distributor database to be replicated.
Every time distributor agent replicates the transaction from distributor to subscriber, it does an entry in a system table dbo.MSreplication_subscriptions which exists in subscriber database.
The MSreplication_subscriptions table contains one row of replication information for each Distribution Agent.
Code: SELECT publisher,publisher_db,publication,transaction_timestamp
FROM dbo.MSreplication_subscriptions
Transaction_timestamp value is same as xact_seqno and till this sequence number all the transaction are replicated to subscription database.
In case u does not have access to the subscription database and to find out the last replicated xact_seqno to subscriber database from distributor database
sp_MSget_last_transaction Publicationid,Publication_database
sp_MSget_last_transaction 2,TestDB
Connect to the Distributor
Code: select * from MSrepl_commands Where xact_seqno>Transaction_timestamp value
This will give you those records which are in distributor and not been replicated to subscriber database after the last replicated xact_seqno
in subscriber database (dbo.MSreplication_subscriptions)
sp_browsereplcmds : @xact_seqno_start, @xact_seqno_end
The stored procedure will use to view pending commands in the distributor database.|
It will display the commands in the readable format.
Replication monitor will also give the xact_seqno at which distributor agent failed
You can also check the error on browsing MSrepl_errors table in distribution database.
select
*
From MSrepl_errors
In this case error is :”The row was not found at the Subscriber when applying the replicated command:
To resolve the same I have xact_seqno and command id , I simple deleted the record from the MSrepl_commands at the distributor.Once done distributor agent will start replicating pending transaction to subscriber.
DELETE from MSrepl_commands where command_id=1295 and xact_seqno=0x000260AD000000100021
I have created a script to get all replication information about Publisher,Publication,Articals and LogReader in a treeview Structure .Like How many publisher withsnapshot location,how many publication are generated from each of the publisher,How many articals are associated with each publication ,Log Reader Agents associated with each publication with LodReader Agent Job information. I will write more about Replication Type,SyncType,Subscription Type etc in my next blog post or in the same one.
Script: Download the script and run the Script at Distribution Database
Run this script if u have SQL Server 2005 or above.
Download : Replication_Info_Script – V3
Download :Replication_Info_Script – V2
Download : Replication_Info_Script
Update : Added Subscription Information
Update : Added Distributor Information
How to: Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming)
It’s really a gigantic task to setup a transactional replication on database of size 1.2 TB.
As a new bee we will just go and setup transactional replication on any database and generate the snapshot without putting a thought that , how much time snapshot will take and amount of disk for snapshot folder .On the top of this DBA don’t put much brain in thinking what will happen to the network.
The stress-free way of setting up T-Replication for Gigantic Database is to initialize the subscription with Backup.
Here I will demonstrate the steps how we can setup T-Replication and initialize the Subscription with Backup.
1.Configure the distribution on the server as per you subscription. In my case I setup the distribution on the same server where I have my production Database.
2.Right Click the Replication Folder in Object Explorer and Select Publisher Properties. Select the Database from the list and check the Type of Replication to be used for replication.
3.Let’s setup the publication for the database. One way is to setup the replication using GUI with default settings. Add the articles which need to be added.
Below is the sample default script which gets generated using GUI Option.
| Use [Bprompt_Partition_Prod]
exec sp_replicationdboption @dbname = N’BPrompt_Partition_Prod’, @optname = N’publish’, @value = N’true’ GO use [BPrompt_Partition_Prod] exec [BPrompt_Partition_Prod].sys.sp_addlogreader_agent @job_login = N’Provide a Domain user’, @job_password = ‘@Jon_Login Password’, @publisher_security_mode = 0, @publisher_login = N’SQLLogin or Impersonating the Process Account’, @publisher_password = N’ ‘, @job_name = null GO – Adding the transactional publication use [BPrompt_Partition_Prod] exec sp_addpublication @publication = N’BPrompt_Prod_Pub’, @description = N’Transactional publication of database ”BPrompt_Partition_Prod” from Publisher ”NL03-DF271\DELL”.’, @sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’, @allow_pull = N’true’, @allow_anonymous = N’false’, @enabled_for_internet = N’false’, @snapshot_in_defaultfolder = N’true’, @compress_snapshot = N’false’, @ftp_port = 21, @ftp_login = N’anonymous’, @allow_subscription_copy = N’false’, @add_to_active_directory = N’false’, @repl_freq = N’continuous’, @status = N’active’, @independent_agent = N’true’, @immediate_sync = N’false’, @allow_sync_tran = N’false’, @autogen_sync_procs = N’false’, @allow_queued_tran = N’false’, @allow_dts = N’false’, @replicate_ddl = 1, @allow_initialize_from_backup = N’false’, @enabled_for_p2p = N’false’, @enabled_for_het_sub = N’false’ GO use [BPrompt_Partition_Prod] exec sp_addarticle @publication = N’BPrompt_Prod_Pub’, @article = N’xyz’, @source_owner = N’dbo’, @source_object = N’xyz’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509F, @identityrangemanagementoption = N’manual’, @destination_table = N’xyz’, @destination_owner = N’dbo’, @vertical_partition = N’false’, @ins_cmd = N’CALL sp_MSins_dboxyz’, @del_cmd = N’CALL sp_MSdel_dboxyz’, @upd_cmd = N’SCALL sp_MSupd_dboxyz’ GO |
4.For an existing publication, ensure that the publication supports the ability to initialize from backup by executing sp_helppublication (Transact-SQL) at the Publisher on the publication database. Note the value of allow_initialize_from_backup in the result set.
use [BPrompt_Partition_Prod]
GO
EXEC sp_helppublication @publication = BPrompt_Prod_Pub
By default the value of allow_initialize_from_backup is set to 0.
To set the publication to support the ability to initialize from backup
| EXEC sp_changepublication
@Publication=BPrompt_Prod_Pub, @property = N’allow_initialize_from_backup’, @value = True |
This can also be achieved at the time of configuring the publication using the script.
| use [BPrompt_Partition_Prod]
exec sp_addsubscription @publication = N’BPrompt_Prod_Pub’, @subscriber = N’Subscriber Server’, @destination_db = N’BPrompt_Partition_Prod_Rep’, @subscription_type = N’Push’, @sync_type = N’initialize with backup’, @article = N’all’, @update_mode = N’read only’, @subscriber_type = 0, @backupdevicetype =’disk’, @backupdevicename =’\\SharedDrive\j$\BPromptSubscriberDB\BPrompt_Partition_Prod.BAK’ –Location of Backup file– exec sp_addpushsubscription_agent @publication = N’BPrompt_Prod_Pub’, @subscriber = N’SubscriberServer’, —Name of the Subscribing Server @subscriber_db = N’BPrompt_Partition_Prod_Rep’, @job_login = N’Provide a Domain user’, @job_password = Domain User Password’, @subscriber_security_mode = 0, @subscriber_login = N’SQLLogin or Impersonating the Process Account’, @subscriber_password = ‘Password’, @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 = 20100903, @active_end_date = 99991231, @enabled_for_syncmgr = N’False’, @dts_package_location = N’Distributor’ GO |
Once Subscription is configured, Open the Replication Monitor and double click the Running Subscription.
You will see the Distributor agent will start doing the check sum validation for all the included article.
Thursday i was too happy that weekend started as I took a leave on friday, as i stepped out from my technology room i got an unexpected call from the technology datacenter hearing what he said said my dream of weekend shattered.
The problem was one of the SQL Server on Cluster failed .On the server we have a huge OLTP database over 1.2 TB which was configured with transactional replication on a subscriber db on different server.
The issues where will the transaction get replicated from distribution when the server will be up and running or will there be any problem doing so.
When the server came up the replication was messed up, Transaction where getting replicated from publisher to distributor but failed to replicate the same transaction from distributor to subscriber.
I spent enough time to know what all transaction failed to replicate and what the way to replicate the same at subscriber seamlessly.
Later hopefully I managed to resolve this with one transaction loss (identified one).
How to know total number of transaction which is not replicated to subscriber?
select * From msdistribution_status
| article_id | agent_id | UndelivCmdsInDistDB | DelivCmdsInDistDB |
| 1 | 1 | 0 | 1 |
How to know details of undelivered transactions?
—–Code has been copied and modified from Paul’s Blog————–
declare @xact_seqno varbinary(16)
select @xact_seqno = max(xact_seqno) from MSsubscriptions
inner join MSpublications
on MSpublications.publication_id = MSsubscriptions.publication_id
inner join MSdistribution_history
on MSdistribution_history.agent_id = MSsubscriptions.agent_id
Where subscriber_db = ‘reptest_test’
AND Publication = ‘Pub_test’
Print @xact_seqno
declare @str varchar(255)
set @str = master.dbo.fn_varbintohexstr (@xact_seqno)
set @str = left(@str, len(@str) – 8)
–if exists(select object_id(‘tempdb..#trancommands’)) drop table #trancommands
create table #trancommands
(xact_seqno varbinary(16) null,
originator_srvname sysname null,
originator_db sysname null,
article_id int null,
type int null,
partial_command bit null,
hashkey int null,
originator_publication_id int null,
originator_db_version int null,
originator_lsn varbinary(16) null,
command nvarchar(1024) null,
command_id int)
insert into #trancommands
exec sp_browsereplcmds @xact_seqno_start = @str
select * from #trancommands where xact_seqno > @xact_seqno
drop table #trancommands
Here we got lot of transactions which were in the waiting list (waiting to be replicated), Now we have to identify those transaction which need to be marked as replicated or deleted.
I put lot of efforts to identify those transactions and lucky we found one such transaction.
Now the question is how to mark that transaction as replicated or delete it from the distribution list.
I posted the question on MSDN SQL Server Forum and in a day time I got my answer.
http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/06dbde58-457c-4638-9b13-b40b75ea6264
We can delete the transaction using the below code:
DELETE FROM distribution.dbo.msrepl_commands where xact_seqno=put the xact_seqno which need to be deleted.
Doing so my transaction replication started without pain and we lost nothing as I have deleted the same transaction from publisher also.
I wana thank though this BLOG to Wissam and Hilary on helping me on this.
In this blog we will talk about why to create an indexed view, how to create an index view and replicate the index view as a table to existing or new Transactional Replication.
While working with one of my client running PeopleSoft Application having database on SQL Server 2005 wanted to segregate the reporting part. To do so we proposed Transactional Replication and use the subscriber database as Reporting Database.
The problem came up was number of tables doesn’t have any primary key and they were not ready to create any type of Keys on the table. So, the antonym is how to add those tables as article in the publication.
Searching the BOL we came across about Indexed view. Now the question is how we can use it.
We took one of the tables which doesn’t have a primary key and created an indexed view with SCHEMA BINDING referencing table.
SCHEMA BINDING: Binds the view to the schema of the underlying base tables.
Creating Indexed View with Schema Binding
USE <Database Name>
CREATE VIEW Vw_Emp_SalTrans_rep WITH SCHEMABINDING
AS
SELECT EmloyeeID, SalDay, LastSalMon, SalYrs
FROM dbo.Emp_SalTrans
Once the Indexed View is created,Create Unique Clustered Index on the same
CREATE UNIQUE CLUSTERED INDEX Vw_Emp_SalTrans_rep_CluInd ON
Vw_Emp_SalTrans_rep (EmloyeeID, SalDay, LastSalMon, SalYrs)
Confirm that table on which view has been created exists on both the publisher and subscriber.
Doing so is a mandatory step since indexed view works on the principle of a log based operation i.e whatever DMLs are being committed on the production table gets replicated to the subscriber through the indexed view.
Add the indexed view as an article in existing transactional Replication
To added index view in the exiting transactional publication we will use sp_addartical stored procedure as GUI doesn’t much option to do so.
Generate the snasphot of the publication with re-initialize all subscription.
Once is snapshot is generated the same get applied to the subscription database.
Stored Procedure sp_addartical has lot of more option , so to explore more search for sp_addartical in BOL.
There is always this question how to generate snapshot for the articles which are newly added to the Publication.
Run the below command on the Publisher Server
EXEC sp_changepublication
@publication = ‘PublicationName’,
@property = N’allow_anonymous’,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘PublicationName’,
@property = N’immediate_sync’,
@value = ‘false’
GO
Now add new article to existing publication for which above command was executed and generate the snapshot.Snapshot for the newly addedartical will get generated. To check the same , check the snapshot Folder.
Here we will go over how we can move the indexes of the Partitioned Publisher database (Data being partitioned on the basis of Clustered or non-clustered index) on the basis of Partition Function and Schemes or on different file groups to the subscriber database.
This is applicable to the environment which has their database partitioned using partitioned function & schema or their data is on different file groups other then Primary.
Once setting has been done, generate the snapshot by running the snapshot agent or my marking subscription for re-initialization.
After snapshot is generated we can check the generated scripts in the snapshot folder whether index script is properly generated or not with the filegroup or Partition scheme defined.
Now check if the snapshot is properly delivered or not.
Working with different versions of sql servers i came across with an issue,How to get Publisher information from Subscriber.
To get to know I ran sql profiler for couple of time and captured an undocumented stored procedure which helped in getting the information I needed about the publisher.
exec sp_MSenumsubscriptions ‘both’
The stored procedure can be found in subscribed database.
The same can be achieved from table MSreplication_subscriptions, can found in subscribed database.
SELECT * FROM MSreplication_subscriptions
msufian
