locked
Access Mirror Database for Read and Write (OR another solution to my problem) RRS feed

  • Question

  • I have a live database that updates constantly. I have to move it from server to a different server at another location. Before that I want to create a synchronized copy at my new  server, so i can access it from a client so as to test if its working fine. 

    I Created a MIRROR but i can;t access the mirror without failover. What should i do ?

    IS there any other way to maintain a synchronized copy and still be able to access it as if it live . 

    Monday, July 29, 2013 10:54 PM

Answers

  • Hi

    yes transactional replication would allow you to write to the subscriber database for testing and would keep published articles in the subscriber database up to date near realtime.

    However its worth noting that replication doesn't offer the same features as mirroring e.g. database users are not transferred etc, and if you update the subscriber tables it might cause conflicts with updates being received from the publishing database.

    If you want to only perform a test, it might be better setting up mirroring to the DR server. Break the mirror when you want to test writing to the DR server. After testing is complete, remove the mirror database on the DR server and re-establish mirroring. This way you don't impact the production database and users can still use the production database while you test the DR database.

    Further reading on transactional replication http://msdn.microsoft.com/en-us/library/ms151706(v=sql.105).aspx

    If I reading your question wrong and you want to write to both databases e.g. both are Production, then you'll need to use something like merge replication.

    • Proposed as answer by Fanny Liu Friday, August 2, 2013 10:38 AM
    • Marked as answer by Fanny Liu Monday, August 5, 2013 12:12 PM
    Tuesday, July 30, 2013 1:08 AM
  • There is no easy way to do that. You can of course use replication, but with that you have a lot of implications on the database... If you use Mirroring (or AlwaysOn Availability Groups in case of SQL 2012) what I would do is just do a Failover and Redirect the Clients. If you Encounter a Problem with that you can immediately fail back without loosing any data.

    Unfortunately the only alternative I have for you would be getting a downtime for your tool, so you can ensure that no more updates are made while you are testing. (But I don't think that this is necessary... I have done hundreds of Server migrations using Mirroring or AGs, and rarely ever have Problems with it...)

    Rick

    • Proposed as answer by Fanny Liu Friday, August 2, 2013 10:39 AM
    • Marked as answer by Fanny Liu Monday, August 5, 2013 12:12 PM
    Wednesday, July 31, 2013 5:02 AM

All replies

  • Hi

    the only way you can access the mirror (read only) without failing over is to create a database snapshot ref: http://technet.microsoft.com/en-us/library/ms175511.aspx

    NB: Snapshots require SQL Server Enterprise Edition.

    If you have SQL Server 2012 you could use availability groups to read the replica (mirror) database. This also requires Enterprise Edition.

    Tuesday, July 30, 2013 12:16 AM
  • i basically want to write to the database , should i try replication than mirroring ? PLease advise 
    Tuesday, July 30, 2013 12:38 AM
  • Hi

    yes transactional replication would allow you to write to the subscriber database for testing and would keep published articles in the subscriber database up to date near realtime.

    However its worth noting that replication doesn't offer the same features as mirroring e.g. database users are not transferred etc, and if you update the subscriber tables it might cause conflicts with updates being received from the publishing database.

    If you want to only perform a test, it might be better setting up mirroring to the DR server. Break the mirror when you want to test writing to the DR server. After testing is complete, remove the mirror database on the DR server and re-establish mirroring. This way you don't impact the production database and users can still use the production database while you test the DR database.

    Further reading on transactional replication http://msdn.microsoft.com/en-us/library/ms151706(v=sql.105).aspx

    If I reading your question wrong and you want to write to both databases e.g. both are Production, then you'll need to use something like merge replication.

    • Proposed as answer by Fanny Liu Friday, August 2, 2013 10:38 AM
    • Marked as answer by Fanny Liu Monday, August 5, 2013 12:12 PM
    Tuesday, July 30, 2013 1:08 AM
  • If you want the clients to test the database you can simple backup and restore the database on the new server and redirect them to the new location... Is it so critical to  have a synchronized copy of the database?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 30, 2013 6:44 AM
  • the database gets updated every second as queries are made from all across the globe. we are planing to move that database and the server from one location to another. BUT before we direct all our clients to the new database and the server i want to make sure that the server that talks to the new database is configured properly and works fine like the OLD server at the OLD location talks to the current DB. SO basically i want to test the new system using a new DB that is updating the same way the current one is updating.
    Tuesday, July 30, 2013 4:26 PM
  • There is no easy way to do that. You can of course use replication, but with that you have a lot of implications on the database... If you use Mirroring (or AlwaysOn Availability Groups in case of SQL 2012) what I would do is just do a Failover and Redirect the Clients. If you Encounter a Problem with that you can immediately fail back without loosing any data.

    Unfortunately the only alternative I have for you would be getting a downtime for your tool, so you can ensure that no more updates are made while you are testing. (But I don't think that this is necessary... I have done hundreds of Server migrations using Mirroring or AGs, and rarely ever have Problems with it...)

    Rick

    • Proposed as answer by Fanny Liu Friday, August 2, 2013 10:39 AM
    • Marked as answer by Fanny Liu Monday, August 5, 2013 12:12 PM
    Wednesday, July 31, 2013 5:02 AM