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 writeable version of the data at any given time. Replication is the only native component or feature in SQL Server that will allow you to have multiple writeable databases that maintain data in sync to any near-real-time degree. Once you have determined that you need a solution that provides
read more

Error Creating Remote Distributor When Creating a Local Distributor

Recently, I was working on a Peer-to-peer replication automation project. When I switched from my local test servers to some lab servers to test my scripts, I ran into some issues. The thing that confused me at first was that it was complaining about a missing remote login for the replication linked server that gets created when you run sp_adddistributor. The reason the error was confusing at first is because I was creating local distributors (the best practice for peer-to-peer
read more

Day 30 of 31 Days of Disaster Recovery (T-SQL Tuesday #40): Using Partial Availability and Initialize from Backup to Replicate a Partial Database

It’s been a tough and long road to 31 Days of Disaster Recovery. It’s been very difficult coming up with quality topic ideas for the series as we near the end. For day 30 of the series, I am combining a post on performing piecemeal restores with a post on filegroups for T-SQL Tuesday #40 and a post on replication. In case you’re not familiar with T-SQL Tuesday, let me enlighten you. This blog entry is participating in T-SQL Tuesday
read more

Day 29 of 31 Days of Disaster Recovery: Using Database Snapshots to Restore Replicated Databases in Test

For day 29 of my 31 Days of Disaster Recover series, I want to talk about restoring replicated databases from database snapshots. Someone asked me recently if I had any recommendations for speeding up their unit testing on replicated databases. He has to run unit tests over and over on replicated databases to ensure that they deploy successfully in a replicated environment. His current process was adding too much time to the process and automating it was difficult for him
read more

SQL Saturday 108 (Redmond, WA) Session Files

SQL Saturday 108 (Redmond, WA) Session Files Session files are now available for download. The zip file contains the PowerPoint slide deck and demo code. Zip file: ReplicationMagic.zip (5.2 KB) Included files: Demo: Initializing replication from a log backup InitializeFromLogPub.sql InitializeFromLogSub.sql Demo: Initializing replication from a filegroup backup using named filegroups FGByNameBackup.sql FGByNameRestore.sql Demo: Initializing replication from a filegroup backup using read-write and read-only filegroups (not demoed during session) ReadWriteFGBackup.sql ReadWriteFGRestore.sql Slidedecks PowerPoint 2010: ReplicationMagic.pptx PowerPoint 2003-2007: ReadWriteFGRestore.sql
read more

Undocumented Gotchas of Transactional Replication

Undocumented Gotchas of Transactional Replication This discussion started on Twitter via the #sqlhelp hash tag. The question that was asked is, “What are the undocumented gotchas of Transactional Replication? #SQLHelp”. I responded with several things that came to mind. I thought these would be good to capture in a blog post and hopefully others would contribute some more gotchas to the list. If you can think of any other gotchas about any type of replication, not just transactional, please add
read more

SQL PASS Summit 2011: Session Files

SQL PASS Summit 2011: Session Files This past week was the SQL PASS Summit 2011 in Seattle, WA. I presented three sessions (regular session, lightning talk, panel discussion) plus ran a focus group on advanced certification and hosted a Birds of a Feather lunch table on disaster recovery. It was a busy, busy week. Below are the slide decks and demo files for the three presentations I made. All of these files have been uploaded to the SQL PASS Summit
read more

Session Files for Replication Magic Presentation to WSSUG

Session Files for Replication Magic Presentation to WSSUG I presented my session on Replication Magic: Initializing from Backup to the Wichita SQL Server User Group last night. I took a little longer to post my session files so I could add comments to the SQL scripts. Session files are below. Powerpoint silde deck: ReplicationMagic.pptx (1.27 MB) Demo 1- Using Read/Write filegroups:       ReadWriteFGBackup.sql (3.63 KB)       ReadWriteFGRestore.sql (1.25 KB) Demo 2- Using filegroups by name:       FGByNameBackup.sql (3.53 KB)       FGByNameRestore.sql (1.3 KB)
read more

3 Log Shipping Techniques

3 Log Shipping Techniques My new article for SQL Server Magazine titled 3 Log Shipping Techniques has been posted online and will appear in an upcoming print issue. This article is a look at some very interesting techniques that use log shipping in interesting ways. Mastering these tehcniques can save you a great deal of time and frustration. You will learn how log shipping can be a very useful tool. I wrap it up with a tale of how I
read more

Counting Unreplicated Commands and Transactions

Counting Unreplicated Commands and Transactions Someone asked me a question via Twitter today that I couldn’t possible answer 40 characters at a time. The question was about how to get the count of unreplicated transactions and commands from teh distribution server without using system views. Well, I do use one system view, but only because replication relies on the same view, sys.servers. The Question @SQLSoldier – Was told you were the guru … lookin for query to get undelivered cmds
read more