Mirrored MSSql servers possible?

I have a Server running SQL Server 2008 R2 that holds a single database that about 150 people access through a custom application. There is a service that runs every Friday that slows access to the database to a crawl while this service is running. What I'd like to do is to deploy a new server that has crazy fast IOPS and processor power, and use the old server as a "mirror" server that this service can use, instead of the new server. I basically want all users to connect to the newer, faster server, and have this service that runs every Friday, do it's thing on the older, mirror server. I've read up on how to do this, but so far, it only looks like you can mirror for fail over. I'm looking for a solution that will allow updates to both the database and log files, on a two way partnership. I'm not sure if this is going to be possible or not.

Thank you for any help.
Who is Participating?
Anthony PerkinsConnect With a Mentor Commented:
If you need to update both databases then consider something like peer-to-peer replication.
I think you are looking for merge replication.  There are issues with it that can make it not so fun, but I think it may be your only choice if you need to make changes at both locations:


You'll want to pay special attention to this part: http://msdn.microsoft.com/en-us/library/ms151749%28v=sql.105%29.aspx
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Use database mirroring and set up a snapshot on the mirrored database so that you can use it in readonly mode.
WetjetAuthor Commented:
I forgot to mention that these servers are on the same network. Replication should go fairly quickly.

Also, the service that runs does need to make updates on the 2nd or mirror database server, and replicate changes to the main server.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.