Time to talk about a subject that most DBAs hate, but some DBAs like me have a love-hate relation with it. Replication. Not storage replication or Availability groups. That old workhorse SQL Server data replication. Limit the number of articles per publication: Having to occasionally rebuild replication is a humongous pain in the backside, but…
Tag: Replication
New Whitepaper: Deciding Between Merge and Peer-to-Peer Replication
Replication is still a major component of SQL Server today even with Availability Groups as an alternative to certain scenarios. Availability Groups does not allow you to have more than one writable version of the data at any given time. Replication is the only native component or feature in SQL Server that will allow you…
Replication–Beware Reuse of View Names When Filtering Articles
I came across a very strange issue with replication this weekend. As a part of a release a new table was being created with extra columns and a view was created with the old table name that referenced the old column set (so as not to break old legacy applications). This table was replicated. The…
Mass Updating Job Step Retry Attempts
Sometimes I find that the SQL Server team make curious choices as regards default operations. When setting up replication by default the log reader and distribution agent job steps will retry 2,147,483,647 times before failing. Doing the math on the retry interval of 1 minute that means that the job wouldn’t actually hit a failed…
T-SQL Tuesday #10 – Applying Indexes To Replicated Tables
It’s T-SQL Tuesday time again! This month Michael J. Swart (blog|twitter) is hosting and has given us the topic of indexes. I’ve previously written about how you can find indexes that are not being used and get their sizes. Not being allowed to reuse that meant that I had to come up with something else.…
Calculating Replication Schema Options
I recently evaluated using page compression on some of my production data (see my post on estimating data compression). I’m using transactional replication to send this data out to a few other servers for reporting purposes and I wanted to adjust replication so that the data compression settings on the tables would also be replicated.…