October 27, 2014 at 5:30 am
Good Morning Everyone,
Our production data is on a development server. I am looking at moving everything to production so we can have a dev/prod environment. I was originally thinking of setting up database mirroring until everything that connects to dev server is updated to point to prod server.
My problem is that the prod database name differs from the dev database and mirroring requires them to be the same.
Are there any other alternatives?
Thank you!
October 27, 2014 at 5:32 am
Log shipping might be the tool for this job.
EDIT: subsequent post clarifies the request. Log shipping is not suitable for this job.
-- Gianluca Sartori
October 27, 2014 at 5:52 am
Edit: ...
You're moving the prod database somewhere else and want something to automatically redirect requests made to dev to go to prod instead?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2014 at 6:01 am
I'm moving a db on dev to a prod server. All our applications (3rd party tools, access dbs, ssrs/ssis pkgs, etc) all point to dev. I guess I'm looking for a way to have changes made the db on dev to also appear on prod immediately.
Our team will be working on updating all the connection strings of our applications to point to prod, but dev will still be live and prod needs to have all changes that are being made to dev.
Hope this clarifies.
Thx
October 27, 2014 at 6:50 am
And prod needs to be read-write and to accept schema and data changes? If so, those data changes need to sync back to dev?
Mirroring wouldn't have worked anyway, the mirror database is unavailable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2014 at 7:39 am
I wouldn't worry about that, I'd take the 'remove the band aid' approach myself. Stand up Prod, and say the cut over is of X date, and have everyone on board during a maintenance window to ensure things are connecting to the new server by temporarily shutting down the Dev one and running some regression tests in the environment.
October 27, 2014 at 7:56 am
The only technology that supports multiple writeable copies of the same database is replication. P2P replication looks like the best fit.
However, I would not use this approach.
If your only issue is switching connections from dev to prod, you could use a CNAME in your DNS to make all connections go to prod when the dev server is specified.
This requires shutting down or renaming the dev server. If this is not possible, you could use a SQL Server native client alias to point to the prod server and deploy it with an active directory policy to all the client machines.
-- Gianluca Sartori
October 29, 2014 at 6:53 am
A lot of good info here.
Can't use the remove band-aid approach. I already got shut down on that idea.
Can't shut down the dev server. Programmers still need to test there projects.
"use a SQL Server native client alias to point to the prod server and deploy it with an active directory policy to all the client machines"
--I don't have access to make changes in AD. Seems like this is on the server level. My server has multiple DBs and only one needs to be mirrored or have requests redirected.
October 29, 2014 at 7:04 am
louie1487 78804 (10/29/2014)
Can't use the remove band-aid approach. I already got shut down on that idea.
To be honest, I'd push back on this, hard.
Peer-to-peer replication is about the only thing that can do what you want, it requires Enterprise edition and it's not trivial to implement. To avoid problems (including data modification conflicts) you need to ensure 'locality of changes', that certain data is modified from certain servers and other data from other servers.
It's not instant, there is a delay before stuff gets to the other servers
You need to either have no identity columns or to go and manually reseed the columns to avoid the same value being inserted from multiple nodes.
Making schema changes requires that the entire replication topology be quiesced (no changes anywhere and a few replication stored procs run before and after, plus stopping and starting the log reader jobs)
All tables need to have primary keys (it's a form of transactional replication)
Tables cannot be truncated
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2014 at 7:24 am
This is why its critical to document and define business-critical systems and data paths. Sounds like you don't have this, if you did, it would be a simple matter of a quick regression test to know everyone was talking to the new server.
This is best done in a maintenance window, where the programmers can quickly update connection strings they missed and migrate out fixes.
That being said, you'll never be able to know you are completely cut over without some sort of shutdown test. I would not want to be you, this would totally suck to support.
If you do this, they won't change, they will think they won't have too. This is my experience. Its not been proven wrong yet.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply