July 22, 2014 at 5:31 am
Hi all
I'd like some advice (and I can't find anything on Google) before I attempt this one.
First some background.....
Server A - main server that holds the SQL databases that the application writes to (set up as the Principal in the mirror setup)
Server B - Receiving Mirror (the databases are listed as "(Mirror, Synchronized/Restoring)"
Server C - The server I want to change
All the above servers have each other listed under linked servers (if that makes sense).
Server B is the receiving mirror and we snapshot the mirrors and run queries off those snapshots to build various reporting tables.
The snapshot is created as follows:-
create database testfocdb_daily
on (name = testfocdb, filename = 'c:\MEDIFILES\testfocdb_daily.ss')
as snapshot of testfocdb
Server C currently gets its data from Server B and has to transfer quite large amounts of information acros the network.
What I'd like to do is get Server C to use something along the lines of the code above and get its snapshot from the same place (to save setting up another mirror).
Three questions:-
1) Is what I want to do possible?
2) If so, can I just repoint the code above to the mirror on Server B and take the snapshot?
3) Can you take two snapshots of the same mirror at the same time to two different places (assuming the answer to question 1 is "yes")?
July 22, 2014 at 7:41 am
It is not possible to create a snapshot of a database on another instance. You could setup some kind of back-up/restore technique (perhaps logshipping?) to create a available database on instance C.
To minimize the downtime of the database on instance C you should initially create the database using a restore of a FULL back-up and leave the database in STANDBY mode. Next you can use a restore of a DIFF back-up on a daily(?) base to bring the database up-to-date.
July 22, 2014 at 7:56 am
I'd just found that out as the notification appeared.
Looks like it'll have to be log-shipping to move things piecemeal. Should still cut down on network traffic (or least the same traffic is spread through-out the day).
Thanks for confirming.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply