Some info on the current setup:

We're currently using database replication - transactional replication, to be specific - between 2 servers. For sake of this post, we are replicating from a PRIMARY to a DR halfway around the globe. The goal is to have the ability to 'switch' to the DR in case the primary goes down, which would become the new PRIMARY. [For the sake of this post, I will continue to refer to the original server as the PRIMARY.] I like the idea of being able to 'swap' to a mirrored server using the Failover Partner keyword in the connection string (http://www.sqlservercentral.com/blogs/james-sql-footprint/2013/11/08/failover-partner-keyword-in-connection-string-qa/), but it seems that it's made for database mirroring, and the owner likes having the process "manual", probably from a fear of not 100% understanding the technology, and I am right there with him on that point. What I don't like about our current setup is that, for major schema changes, those changes are blocked unless I delete the subscribers and publishers, and after schema changes, set everything back up. I don't have a script to automate this yet so this process takes forever, and we're still making significant changes to the database schema.

So basically, I don't know if replication is the answer. It works great once it's set up, I have the setup process down (meaning, I spent hours and hours documenting the gotchas I have encountered setting it up), but it takes FOR-FREAKING-EVER to get set up, only for it to be dismantled if a schema change comes in that's more complex than a simple 'add a new column to the end of this table' type of change. Additionally, doing more reading of this online has brought up multiple sites stating that the process of switching the PRIMARY to be the new DR, then once the old PRIMARY comes back on, making the old DR sync back to the old PRIMARY and restoring the PRIMARY and DR back to the way they were is a hassle, putting it simply.

I need people with DR experience to chime in and tell me what options there are besides this. Some clients use standard; most use enterprise. This is a turnkey platform we have developed and we're pushing people to use enterprise, but I wouldn't be surprised if there was a standard edition floating around in use in production. Everyone is using at least SQL 2012, and almost everyone is on 2016. What options do I have to replicate the data to a different server in case the server/network to the server goes down? With those technologies, how easy is it to switch between primary and DR servers, and how easy is it to sync and switch them back once the original comes online? Should we code into the platform separate read-write and (collections of) read-only connection strings in the app itself?

Thanks in advance - I don't have enough DR experience, and the last time I using anything regarding mirroring, it was with a very old technology for SQL Server 7.0 that required a GUID in every table to reference - back in 2002. My knowledge is outdated.

EDIT: Summarized question: What would be the best mirror/replication strategy that would allow me to easily (but manually) switch the database to the secondary, and then easily 'switch' so the primary syncs back up, switch primary/DR and then swap back so the original primary is the primary again?

  • Might find better minds at dba.stackexchange.com for this sort of thing — but I think your question looks like it’s actually a dozen or so questions. That mightn’t fit the best with this format of site. – TZHX Dec 8 '17 at 9:32
  • I took what you said into account and summarized it into a single question. – Derreck Dean Dec 8 '17 at 15:43
  • You may want to look into how the Distributor works. – JeffO Dec 8 '17 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.