Need Info for Transaction Replication

Question

Hello All 

I am newbie to Replication stuff . We have huge database say around 400 GB of data and we want to have reporting server, so we have choose the replication stuff. 

As for the large databases , it is preferred to use transaction replication with initial backup from publisher i.e nothing but having the setup equivalent in subscriber as that of Publisher and then start replication. we have done this and it is faster also .

But i see one issue here  say for this release  deployment team went and have done this. Now do we need to do this same stuff for every release ?

Second question is i see that sometime a there is failure of one transaction due to many issues in this case it will keep retrieving this and try to finish, which in turn increasing the size of logs and failing every time . Let say i am okay with data loss, so can i skip this  step and continue with other steps ? If this is possible how can i acheive this ?

Regards,

Phani

Answer 1

if you're using SQL Server upto 2008R2 it is probably easier to use log shipping to get the data to the reporting server as it has less overhead on the OLTP server as there's no replication (publisher, distributor, subscriber) involved and you've do transaction log backup anyway

if you're using SQL Server 2012 you can use AlwaysOn and and readonly server

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Thanks Daneil for your comments 

But we cannot go with the option of logshipping , so only option that we can go is Transaction Replication 

Answer 3

could you tell us the reason why you can't use log shipping ?

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

For issue 1.

You dont have to re-setup the replication everytime as it is one time until and unless you keep changing the article characteristics.

For Issue 2,

You should be seeing the replication errors which when not fixed will blow up the transaction log. When you are Ok with the data loss, you should set the distribution agent profile to skip the consistency check errors.

---

Thanks, Adi

Answer 5

I am not sure why you are having to reinitialize your subscriptions and push snapshots with every release.  Only reason you should have todo this is if you are manipulating the PK on a published table or if you are changing publicatio or article properties during the release.  All new schema changes should flow through just fine.  If you are adding new tables to replication due to the release I would suggest making them a new publication.

For the errors, you can track down and find the problematic transaction and delete it from the distirbution tables or like Adi said, you can modify your agent profile to continue on consistency errors by adding values to the -SkipErrors agent profile parameter or use the built in agent profile "Continue on data consistency errors". If you want to manually delete the problematic transaction you can use the following SQL as replication monitor should provide you with a transaction sequence number as well as a command id.

--This shows the table the failed command is trying to run against
SELECT  
      CASE 
           WHEN CHARINDEX('{',Convert(nvarchar(15), substring(command, 1, 15)), 1) =  1
               THEN CONVERT(nvarchar(500), substring(command, CHARINDEX('CALL', convert(nvarchar(500), substring(command, 1, 500)), 1) + 1, len(command)))
               ELSE 
                   CONVERT(nvarchar(500), substring(command, CHARINDEX('[', convert(nvarchar(500), substring(command, 1, 500)), 1) - 1, len(command)))
      END as CommandIssued
    , xact_seqno
    , command_id
    , publisher_database_id
    , type  -- if this is a negative #, trans is a snapshot
    , article_id
FROM   msrepl_commands 
WHERE    Command_ID = 2 -- Command ID
  and xact_seqno =  0x005270DE00000F73006E00000000  -- Transaction sequence number


-- Remove records from both the appropriate tables

DELETE FROM   dbo.msrepl_transactions
      WHERE   xact_seqno =  0x005270DE00000F73006E00000000                    

DELETE FROM   dbo.msrepl_commands 
      WHERE  Command_ID  = 2    
      and  xact_seqno  = 0x005270DE00000F73006E00000000                       

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz