Solved

Transactional Replication using SQL Server 2008

Posted on 2016-09-28
20
47 Views
Last Modified: 2016-09-30
Hello,

I got a very critical SQL database which is the main database for our Public Safety 911 software it is approximately 160 GIG DB.  I am trying to create a transactional replication of this data over to another SQL Server.  I am only trying to replication about 20 of the 800 + tables in the database.  Every time I create the publisher and subscriber and do the initialization, the 911 software slows down or crashes and I have to stop the initialization.  I am fairly new to replication but I don't understand why it would slow the vendor software down.  Am I overlooking something or is this the only way to get the data over to the subscriber?  Any help would greatly be appreciated.
0
Comment
Question by:UncleT
  • 8
  • 7
  • 5
20 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
ID: 41820128
The problem IS that during the initial Snapshot SQL up to version 2008 R2 inclusively it will lock all those tables until snapshot is done so from my experience your options are:

1. Setup replication by Initialize from Backup instead of using the UI or T-SQL to do that like described here.

https://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/
http://www.sqlpassion.at/archive/2012/08/05/initialize-a-transactional-replication-from-a-database-backup/

Few notes here... one thing is that the full backup you take must be copied quickly from "publisher" server to the "subscriber" to be able to use it otherwise will expire (been there done that...) so you'll need to start over - I can search what/how I did to change settings so this won't expire in default 12 hours or so if I'm not mistaken but in few days or so like it was in my case but..you MUST have enough room for T-Log or T-log backups as they'll grow until you manage to set up the actual replication and they get shifted/restored/cleared on subscriber.

2. Upgrade to SQL 2014 where they (Microsoft SQL team) got wayyyyy smarter and they lock the table for very few hundreds of milliseconds to max 1 second (I suspect while they read/apply metadata changes from publisher to subscriber) instead of the whole shebang while snapshot is taken.

Tried/tested and trusted both methods on 600-800 GB databases backend to e-commerce sites for BI reporting purpose.
0
 

Author Comment

by:UncleT
ID: 41820316
lcohan,

Thanks for your answer.  The replication set I am looking at doing is a Filtered Replication.  From reading the post you supplied it looks like that even if you are replicating a filtered set of data you will still have the full database on the subscriber side.  Am I reading this correctly.  If so, is there another alternative short of upgrading to 2014?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41820429
Right and sorry I missed that aspect (is a while ago since I set 2 servers like that) as initialize from backup cannot be done on filtered table/article set however...I believe once established you can remove articles from replication via T-SQL commands and drop them from subscriber if not needed however...I cannot say 100% for sure when you do that if blocking does not occur.
Alternatives are - set it up via SSMS GUI or T-SQL during some down time on the primary if you (hopefully) have that. I mean if is a critical system then you mast have some periodic patching window right? This can be done during that downtime however...make sure the initial snapshot can be taken and I mean the size of 20 or so tables can be delivered safely during that downtime in the allocated time. Is like you need to bacdkup/copy/restore to subscriber a smaller backup set - hate for whatever reason they removed backup selected tables with first major SQL re-write in SQL 2000 if I'm not mistaken.

If you can't take a downtime...the end users must be prepared for timeouts/slowdowns as there is no other way (but upgrading) as SQL Replication will lock those objects while initial snapshot is taken.

Although they say here
https://msdn.microsoft.com/en-us/library/ms151740(v=sql.105).aspx
that:
"•For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation."
and
"Because locks prevent other users from updating the tables, the Snapshot Agent should be scheduled to execute during periods of lower activity on the database, especially for snapshot publications."

from my experience (and as you noticed the hard way as well) the locks up to this version aren't quite brief. In 2014 yes I did it and comparing to 2008 blocking is a "breeze" - merely got 10-12 timeouts in 5-6 minutes while snapshot was generated VS 38/40 minutes of 100% locking -and all this on exact same database and almost identical hardware/same SAN storage - just different SQL versio.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41823071
Can't you create the snapshot during a period with low on no activity in the database? For example, during night. The snapshot is a SQL Agent job so you can schedule it to run during a better period for you.
0
 

Author Comment

by:UncleT
ID: 41823379
The problem with that is, we are a fairly large county and the database is the 911 database so there isn't much downtime.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41823388
I understand that but even a 911 database will need a periodic maintenance window.
Try to find when it has less activity and try to schedule the snapshot to that period so it will have a minimum impact possible.
0
 

Author Comment

by:UncleT
ID: 41823399
OK.  One more question.  The last time I ran the snapshot it took about 1.5 hours.  If a snapshot locks the tables I can't have the tables locked for that long even at slow times.  I tried to run it the other day and it actually brought the 911 software down.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41823408
Not really good news for you. Here in this MSDN article it's written how to estimate the necessary time:
Because the snapshot is created using the bcp utility, perform a test bulk copy of your data set and time how long it takes to complete. If your data set is very large, perform the bulk copy on a sample of the data set and extrapolate the elapsed time to the entire data set
0
 

Author Closing Comment

by:UncleT
ID: 41823521
Thanks guys for your help.  I'm going to probably try to schedule some downtime like I do when we have software upgrades.  911 will just have to go to paper for a couple hours.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41823525
There is no other way unfortunately and only alternative if in sight would be to upgrade to 2014.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41823538
I don't have experience with Replication in SQL Server 2014 (we are moving all to AlwaysOn on this SQL Server version) but by the documentation for SQL Server 2016 I think the are not big improvements:
(...) if the dataset set being replicated is very large, it will require substantial resources to generate and apply the snapshot. Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to utilize snapshot replication.
0
 

Author Comment

by:UncleT
ID: 41823541
Thanks.  We are looking at upgrading to 2014 as an alternative too.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41823550
Test it on SQL 2014 if you can an't you'll be pleased - even RTM works great VS SQL 2008 and earlier.
0
 

Author Comment

by:UncleT
ID: 41823597
Icohan,

Our 911 database is quite large and I've also been exploring trying to replicate it somehow to another server to use as a reporting database.  We are currently on SQL 2008 and have looked at Mirror Replication and Log Shipping but with those, it is my understanding that when the next mirror or log shipping starts that all the other users will be kicked off.  Is there a way to have an exact (LIVE) copy of a database that can be accessible with SQL 2014?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41823612
Is there a way to have an exact (LIVE) copy of a database that can be accessible with SQL 2014?
AlwaysOn. But with that you'll need to replicate all database and not only some tables. You can have a Secondary Replica available for Read Only (very good for Reporting solutions).
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41823620
I'm pretty sure that can be done by setting up replication from 2008 to 2014 to Initialize from Backup and let me do quick test to 100% confirm that.
Only thing again - make sure the FULL backup can be taken/copied/restored before it expires onto the SQL 2014. I'll check if this can be done using a copy-only full backup or you need to have the actual full backup in order to do it.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41823786
OK so I just did a test as I needed to review the documentation and all steps for myself because I will have soon a new SQL 2008 R2 database repository to replicate to our BI server and all works great if you follow step by step instructions from a new article I submitted "How to replicate SQL 2008 database to SQL 2014 by using Initialize from Backup." - should be available soon and will post the link here as well.

https://www.experts-exchange.com/articles/28734/How-to-replicate-SQL-2008-database-to-SQL-2014-by-using-Initialize-from-Backup.html
0
 

Author Comment

by:UncleT
ID: 41823900
Thanks Icohan,

Just to be sure, what you just posted can't be done if my replication has filtered tables, correct?  I would have to upgrade to SQL 2014 and just do a snapshot.  Am I thinking correctly?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41823961
That is correct and I'll update the article as well - thanks for reminding me. What I posted works as reply to one of your latest comment " Is there a way to have an exact (LIVE) copy of a database that can be accessible with SQL 2014?" so if you need a FULL/ALL tables replica on another server (2014) that's the way to do it without having to take a downtime.

Now that I'm thinking....I believe that can be used as a "staging" server (if you have enough resources like that) and you can use that as a source to replicate only a group of tables but I believe you can't go back to SQL 2008 version - just same 2014 or up.
0
 

Author Comment

by:UncleT
ID: 41824069
Makes sense.  Thanks for all your help!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now