Month: August 2017

Posted on

Configuring distributor and publisher with T-SQL

I already described how to configure distributor and publisher by using GUI.

How to setup Transactional Replication? Part 1

However if you have to setup more than one publisher  to setup and also multiple distributors (or distribution dbs) it is getting quite hard to do it quickly. I often you one remote distributor server and multiple distribution dbs on that one server. That below script helped me a lot.  By default I set retention to 48 hours and 24 hours for history. You can change it in the script or later by using GUI. Script will also generate code that you need to run on Publisher server in order to enable Replication and configure distributor.

Note: It will try to create a folder, if parameter @create_folder set to 1, otherwise it will create db in default or provided path. Still if you want to create folder for new distribution database xp_cmdshell need to be enabled on the server.

If you have any improvements or comments to below script let me know.

/* NOTE: 
1.You need to run the script for EACH distribution db u want to create
2.Model db cant be in use during the installation (It need exclusive lock on model in order to create distribution db)
*/
/****** Installing the server as a Distributor.  ******/
use master

declare @distribution_Db nvarchar(300),
@path nvarchar(300),
@SQL nvarchar(max),
@publisher nvarchar(300),
@password nvarchar(300),
@distributor nvarchar(200),
@create_folder bit

-------------- setting variables ------
set @distributor=@@servername
set @distribution_db=''
set @password=''
set @publisher=''
set @path =NULL -- leave null if u want to create distribution db in this forlder: 
--select substring(physical_name,1,patindex('%tempdb%',physical_name)-1) from sys.master_files where database_id in (select database_id from sys.databases where name='tempdb') and file_id =1
set @create_folder =1 --xp_cmdshell need to be enabled otherwise distribution db will be created in default folder. Set to 0 if you created folder manually.
-------------- end of variables --------

--Configuring Distributor
if not exists (select 1 from sys.servers where is_distributor=1 and name ='repl_distributor' and data_source=@distributor)
BEGIN
exec sp_adddistributor @distributor = @distributor, @password = @password
END

if @create_folder =1 and @path is NULL

BEGIN
		select @path=substring(physical_name,1,patindex('%tempdb%',physical_name)-1) from sys.master_files where database_id in (select database_id from sys.databases where name='tempdb') and file_id =1
END

if @create_folder =1 and @path is not NULL
	BEGIN
		set @path = @path + @distribution_Db
set @sql = 'exec xp_cmdshell ''mkdir '  +@path +''''
print @sql
if @create_folder =1
	BEGIN
	 if exists (select 1 from sys.configurations where name ='xp_cmdshell' and value_in_use =1)
	 BEGIN
	 
	print 'Creating Folder  ' + @path
	exec sp_executesql @sql
	END
	ELSE
	BEGIN
	print 'xp_cmdshell not enabled. Db will be created in default folder'
	set @path =NULL
	END
	END
END




-- Adding the distribution databases
if @path is not NULL
set @path = @path + '\'

if @path is NULL
print 'Distribution db will be created in default folder'


if not exists (select 1 from sys.databases where name =@distribution_db)
BEGIN
exec sp_adddistributiondb @database = @distribution_db, 
@data_folder = @path, 
@log_folder = @path,  
@min_distretention = 48, 
@max_distretention = 48, 
@history_retention = 24, 
@security_mode = 1
END


-- Adding the distribution publishers
exec sp_adddistpublisher @publisher =@publisher,
 @distribution_db = @distribution_Db, 
 @security_mode = 1, 
  @publisher_type = N'MSSQLSERVER'

/****** End: Script to be run at Distributor ******/



select 'Execute on publisher ' + @publisher
select 'use master 
exec sp_adddistributor @distributor = ''' + @distributor + ''' , @password = '''+ @password +''''
Posted on

How to “Browse” Distributor (sp_browsereplcmds explained)

In last post I explained how LSN and seqno works. In this one we will focus on the tool that helps you investigate what actually happen with your replication.

Procedure sp_browsereplcmds.

This is one of the most important tools that use while dealing with transactional replication. The parameters that I only use with that proc are:

  • @xact_seqno_start
  • @xact_seqno_end
  • @publisher_database_id
  • @article_id
  • @command_id
  • @agent_id

Where only first three are “obligatory” in order to get some usefull information. For example not providing @xact_seqno_end will get you ALL commands from seqno that u provided as @xact_seqno_start…. and that could ba a LOT!.

Ok lets explain this base on some example. You have transactional replication and one of the agents is failing with “row not found on subscriber” error. Which pretty much means that the data is missing on subscriber… how do you find what exactly is broken? Well as I explained in previous post in most cases you get seqno and command id from replication monitor (from error of dist agent). To get this exact command you need to provide seqno as a start and end range. Also provide publisher database id and to get this id you can get from distribution db:

select * from MSpublisher_databases

In order to narrow down to exact command just add command id. If you want get entire transaction and all commands in given transaction just skip that variable. Here is the example. First I deleted a row on Subscriber and then I tried to update same row on Publisher.

Error:

error_row_not_found

Updating parameters taken from error above:

exec sp_browsereplcmds 
@xact_seqno_start='0x0000002400000053000300000000',
@xact_seqno_end='0x0000002400000053000300000000',
@publisher_database_id=1, --select * from MSpublisher_databases
@command_id=1

 

After running it you will get command that is actually failing.

command

As you can see this is an Update procedure on TableA. Now you are able to find what exact row is missing. To get exact information about what value is matching what parameter in that update proc just go to Subscriber (and subscriber db) and check the code of that procedure. In our case it will be this:

Procedure

The good news is that from SQL 2014 SP2 CU2 you won’t need this and all the information about errors you will be able to get from actual error from Replication Monitor. More about that change here (as it may give you some issues if u upgrade your servers in wrong order):

Procedure or function sp_MSreplraiserror has too many arguments specified.

Still it is good to know how to get exact command from distribution database.

 

Posted on

How to “Browse” Distribution Db (LSN/seqno explained)

You ever wonder if you can get more information what is actually replicated in your system or how to get exact commands/transactions that been replicated?

I believe you did:). As I mentioned before Transactional Replication operates on LSN (sequence numbers of transactions). Understanding how sequence number works is crucial thing. Lets try to explain.

Once Log Reader read the transaction log of published database it is putting information about transaction and commands into system tables into relevant distribution database. The system tables I mentioned are msrepl_transactions and msrepl_commands. First one contains one row for each transaction and second all commands for given transaction. The most important column is xact_seqno which is the transaction ID and you can join these two tables on that column (and publisher_database_id) to get what commands are included in what transactions.

select top 10 * from MSrepl_transactions (NOLOCK) t
join MSrepl_commands (NOLOCK) c
on t.publisher_database_id=c.publisher_database_id
and t.xact_seqno= c.xact_seqno

Now, How you get current LSN that is being delivered. There are two ways. First one is to check msdistribution_history system table for given distribution agent. Second is to check subscriber and system table msreplication_subscriptions and get current_timestamp. That values is the information about last delivered transaction and as the replication is sequential next seqno in the queue will be the one that is currently delivered. With both there are issues that you need to be aware of. I noticed that misdistribution_history is not live data and it is refreshed with some delay. The problem with second solution is that the next seqno that you get from distribution database could belong to different publication (there is no pubid column in these tables). To over come this you can filter by article id, however then you need to join msrepl_commands table and this can be quite heavy query. Here are the examples:

On Subscriber:

select publisher,publisher_Db, publication,transaction_timestamp from MSreplication_subscriptions

msreplication_subscriptions

On Distribution database:

select top 10 * from MSrepl_transactions (NOLOCK) t
join MSrepl_commands (NOLOCK) c
on t.publisher_database_id=c.publisher_database_id
and t.xact_seqno= c.xact_seqno
join MSarticles a
on a.article_id=c.article_id
join MSpublications pub
on a.publication_id = pub.publication_id
where t.xact_seqno >= <seqno from subscriber>
and pub.publication =< publication name>
order by t.xact_seqno asc

LSN/sequence number is often used in order to troubleshoot replication issues. While u checking replication monitor for failing distribution agent you will get and error that includes seqno and a command id. With these you can find what command exactly failing. In order to do that you need system procedure located on distributor called  sp_browsereplcmds. This procedure allows you to browse actual commands. More on that next post.

How to “Browse” Distributor (sp_browsereplcmds explained)