SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


mirroing - Restoring


mirroing - Restoring

Author
Message
arkiboys
arkiboys
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 264
Hi,
We have mirroing setup for DR purposes.
i.e.
server mdr is mirroing server m

Question:
1-
When I go to the mdr machine in sql server, I see the word restoring for all the databases and so when I try to do a select query from one of the databases, it says that the database is in the middle of a restore and therefore does not return data.
How can I then chek to see if the data inside mdr is indeed correct ?

2-
in machine m, there are sql server agent jobs.
in mdr machine, I do not see the sql server agents. How do I see them.

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225936 Visits: 46322
If the mirroring is working, the data in the mirror DB will be correct. You could take a snapshot off the mirror and query that if you want.

SQL Agent jobs are not included in mirroring, mirroring is just the DB you configure, jobs are in MSDB. If you want them, you have to transfer them.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


arkiboys
arkiboys
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 264
GilaMonster (10/25/2012)
If the mirroring is working, the data in the mirror DB will be correct. You could take a snapshot off the mirror and query that if you want.

SQL Agent jobs are not included in mirroring, mirroring is just the DB you configure, jobs are in MSDB. If you want them, you have to transfer them.

The applications (User screens) are pointing to the machine m (Prod server).
how do I know that if the machine m goes down, then automatically the applications are redirected to the mdr (DR database machine)?
arkiboys
arkiboys
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 264
GilaMonster (10/25/2012)

SQL Agent jobs are not included in mirroring, mirroring is just the DB you configure, jobs are in MSDB. If you want them, you have to transfer them.

How can the sql server jobs appear in the mdr machine?
I mean how can they be copied so that I see them as I do in the prod management studio in sql server?
Thanks
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54379 Visits: 17684
arkiboys (10/25/2012)
GilaMonster (10/25/2012)
If the mirroring is working, the data in the mirror DB will be correct. You could take a snapshot off the mirror and query that if you want.

SQL Agent jobs are not included in mirroring, mirroring is just the DB you configure, jobs are in MSDB. If you want them, you have to transfer them.

The applications (User screens) are pointing to the machine m (Prod server).
how do I know that if the machine m goes down, then automatically the applications are redirected to the mdr (DR database machine)?

Depending on your applications connectivity (only through ADO.NET or SQL Native client IIRC) there is a "Failover Partner =" option for the connection string which will automatically redirect to a mirror partner.

What mode is your mirroring session using, do you have a witness server?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54379 Visits: 17684
arkiboys (10/25/2012)
GilaMonster (10/25/2012)

SQL Agent jobs are not included in mirroring, mirroring is just the DB you configure, jobs are in MSDB. If you want them, you have to transfer them.

How can the sql server jobs appear in the mdr machine?
I mean how can they be copied so that I see them as I do in the prod management studio in sql server?
Thanks

script the jobs out and copy them same as the logins too.
Either that or use an SSIS package task to transfer the objects for you ;-)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225936 Visits: 46322
arkiboys (10/25/2012)
how do I know that if the machine m goes down, then automatically the applications are redirected to the mdr (DR database machine)?


Run a DR test. Fail the mirroring over and check that the apps reconnect properly. You can fail back afterwards. If you have the connection string set correctly it should work. Actually, should work even without that, read up on transparent client redirect.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


arkiboys
arkiboys
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 264
Perry Whittle (10/25/2012)
arkiboys (10/25/2012)
GilaMonster (10/25/2012)

SQL Agent jobs are not included in mirroring, mirroring is just the DB you configure, jobs are in MSDB. If you want them, you have to transfer them.

How can the sql server jobs appear in the mdr machine?
I mean how can they be copied so that I see them as I do in the prod management studio in sql server?
Thanks

script the jobs out and copy them same as the logins too.
Either that or use an SSIS package task to transfer the objects for you ;-)

I can script the job in production.
In the script, I see the name for the production server.
On running this script in the mdr (mirrored server), do I change the production server name to the mirrored server name?
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54379 Visits: 17684
arkiboys (10/25/2012)
I can script the job in production.
In the script, I see the name for the production server.
On running this script in the mdr (mirrored server), do I change the production server name to the mirrored server name?

Hmm, for a local server job you generally won't see the server name, is this a mutli server job?
Can you post the section of the script in question?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
arkiboys
arkiboys
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 264
Some of these jobs are executing ssis packages with parameters, etc...
That is fine, I just change the name of the server in the sql jobs.
Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search