Pre-Steps

The Repserver interfaces file will need entries for both Primary and Replicate Dataservers and the Primary Dataserver’s interface file will need an entry for the Repserver.

• Lets first set some things set up and make a note of various user accounts and passwords.
  Make a note of the various logins passwords you need; Dataserver sa login, Dataserver maint login (which needs to be created on both dataservers), Replication server sa login and Replication server Repagent login. 
  Unless you have an account with the same name/password on all three servers (RepServer and two dataservers), e.g sa, you will also need to create a RepAdmin Login on the RepServer and two Dataservers giving it sa_role and replication_role on the Dataservers and sa role on the RepServer. This login MUST be used when defining the subscription later on. 
  If it already exists drop the maint user from the primary and instead alias the maint user to dbo, e.g sp_addalias Database_maint, dbo
  
  
• Create the connections to the source(pubs2) and replicant(pubs2_rep) databases using rs_init, marking pubs2 as being replicated.
  
  
• After the connections are created you need to disable the firing of any triggers on the replicate side with:
  Alter connection to <replicate server>.<replicate db>
  Set ‘dsi_keep_triggers’ to ‘off’
  go

• Mark the whole Primary/Source database (e.g pubs2) for replication with
  Sp_reptostandby pubs2, ‘all’
  go
  You can also just mark individual table(s) for replication with;
  use pubs
  go
  sp_setreptable table_name, true;
  
  
• If you are setting the whole database to replicate (sp_reptostandby) then you also need to enable warm standby transactions which enables/allows replication of DDL statements.
  sp_config_rep_agent pubs2,
  send_warm_standby_xacts,true
  go

• Make sure the repagent is up and running as that is needed for the dump marker to trigger below. This can be checked in the Primary Dataserver with sp_help_rep_agent and also on the RepServer with admin who. Another thing to check is for a valid secondary truncation point in the primary database. You can check this with dbcc gettrunc and look for the secondary trunc state, it should be 1 for valid. If its not then stop the rep agent, then run dbcc settrunc (‘ltm’, ‘valid’), then in the RSSD run rs_zeroltm <primary server>, <primary db> and then back in the primary db start up the repagent and again check for the secondary truncation point.

• Assuming the above is fine we now Create the Database replication definition using RepAdmin login:
  create database replication definition pubs2_rdf
  with primary at <Primary server>.<Primary DB>
  replicate ddl
  go

• Define subscription to database rep def using RepAdmin login:
  define subscription pubs2_sub
  for database replication definition pubs2_rdf
  with primary at <Primary server>.<Primary DB>
  with replicate at <Replicate server>.<Replicate DB>
  use dump marker
  go
  
  One very important thing to note with create or define subscription is that the Login you use to run this, either interactively or through a script, must exist on the primary (and secondary) dataserver and the RepServer with the same name and password.
  You can check the creation of the subscription in the RepServer errorlog, there should be a line saying “Placing marker in <Primary server>.<Primary db> log”
  
  

• Dump the Primary Database
  Once the dump is complete you should check the RepServer errorlog again, as soon as the dump marker gets processed there should be an entry in the log like:
  “Distributor for ‘<Primary server>.<Primary DB>’ received and processed enable marker. Distributing commands to replicates.
  Distributor for ‘<Primary server>.<Primary DB>’ received and processed dump marker.
  The DSI thread for database ‘<Replicate server>.<Replicate DB>’ is shutdown.”
  
  

• Now Load the dump into the Replicate DB and Online the database
  
  
• You now add the maint user as an alias to dbo (or equivalent permissions) for the replicate database, unless it has exactly the same suid on both servers in which case it will already be aliased to dbo but check this.
  
  
• Now remove the secondary truncation point from the replicate database with
  dbcc settrunc (‘ltm’, ‘ignore’)

• Resume connection to the replicant database dsi
  Resume connection to <Replicate server>.<Replicate DB>
  
  
  
• That should be it, you should now have database replication set up between pubs2 and pubs2_rep, which you can test by creating a new table and entering some data in pubs 2 and checking it gets created and sent across to pubs2_rep.
  
  
  
  BTW to allow replication of DDL statements you might need to enable ‘ddl in tran’ in the replicate database but I think this is already covered in the “Alter connection to <replicate server>.<replicate db>” Step but in case of any errors/issues just be aware of it.
  Use master
  go
  sp_dboption pubs2_rep, ‘ddl in tran’, true
  go