March 30, 2009 at 10:07 am
Mohit (3/30/2009)
Hmmm I heard they changed it in one of the SP; but my backups been working successfully. I'll get back to you on that...But I will note databases as mirrored partner are not OFFLINE; if it was offline the backups do fail. The databases are in restoring state, that is why I believe they are skipped. But I'll confirm again I been running mirroring for now 2-3 years without having to design custom backup routine...
Thanks.
Mohit.
No, technically it is not offline, but that is the error that the maintenance plan's job used to return when it tried to backup the restoring database.
Based on your reply, I tested this with SQL 2008 with full backups, log backups, Check Database task, and reorganize index task. It worked like a charm. so I did some further digging and determined that this was fixed in CU 5 for SQL Server 2005 SP2.
Reference KB: http://support.microsoft.com/kb/946036
I'm very happy to learn that they fixed this!!
March 30, 2009 at 10:14 am
francis (3/30/2009)
In regards to my suggestion to map the Admistrator/sa to the new SID, the new box/server is just for testing purpose and it is an individual body by itself. So instead of "JoeSmith", COULD I run the sp on Administrator/sa in order for the application to communicate with the copied database. Another issue for my original mirroring request, I understand that the master has the databases info under it (same instance) and if the original server is down that I need to switch to the mirrored database as a principal, how could the mirrored box recognize this new guy without its existence in the mirrored "master" database? If this holds true, do I need to backup the system databases too !? Thanks guys !
The problem with doing it this way is that you're not really testing what you are going to pur into production. I have seen many many production deployments fail because it was tested using sysadmin access, and as a result, they never realized that they needed to grant some special permissions for it to work. If "JoeSmith" needs to have access to it in production, then you should test it as "JoeSmith," not as sysadmin.
As a general rule of thumb, yes, you should always be backing up your system databases whether you are using mirroring or not!!
When you talk about the "guy existing in master," you are talking about server logins. You just need to make sure that the logins are in sync between the two servers. You don't need the whole master database. The other information in the master databases won't be in sync. For example, sys.databases will have different information about the mirrored database becasue they are in different states. So only worry about logins in master.
For msdb, you only need to worry about jobs and DTS/SSIS packages.
March 30, 2009 at 11:23 am
Robert Davis (3/30/2009)
No, technically it is not offline, but that is the error that the maintenance plan's job used to return when it tried to backup the restoring database.Based on your reply, I tested this with SQL 2008 with full backups, log backups, Check Database task, and reorganize index task. It worked like a charm. so I did some further digging and determined that this was fixed in CU 5 for SQL Server 2005 SP2.
Reference KB: http://support.microsoft.com/kb/946036
I'm very happy to learn that they fixed this!!
Heh yaa it would be a pain if I had to do it manually; I mirror almost 100 databases. New added constantly, I always lose that KB link I'll add it to my Mirroring FAQ on the Blog. Thanks for the link and confirmation :D.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 30, 2009 at 11:58 am
100 databases to be mirrored! I got my response from the right guy. Thanks again guys !!!:-)
March 30, 2009 at 12:10 pm
Mohit (3/30/2009)
Robert Davis (3/30/2009)
No, technically it is not offline, but that is the error that the maintenance plan's job used to return when it tried to backup the restoring database.Based on your reply, I tested this with SQL 2008 with full backups, log backups, Check Database task, and reorganize index task. It worked like a charm. so I did some further digging and determined that this was fixed in CU 5 for SQL Server 2005 SP2.
Reference KB: http://support.microsoft.com/kb/946036
I'm very happy to learn that they fixed this!!
Heh yaa it would be a pain if I had to do it manually; I mirror almost 100 databases. New added constantly, I always lose that KB link I'll add it to my Mirroring FAQ on the Blog. Thanks for the link and confirmation :D.
Mohit.
What is the link to your blog? I have a mirroring FAQ on my blog as well.
March 30, 2009 at 12:19 pm
Blog Address: http://sqllearnings.blogspot.com (Primary), http://sqllearningsdmvdmf.blogspot.com (DMV/DMF Only)
I just started the FAQ section so it's still small ;-). What is your Blog link I'll added it to me Ref list :D.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 30, 2009 at 1:47 pm
My blog is at: http://qa.sqlservercentral.com/blogs/robert_davis/default.aspx
March 30, 2009 at 1:52 pm
Heh should pay more attention to your signature LOL. Thank-you!
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 31, 2009 at 5:34 pm
Mohit,
One more question, if the original database has userid [sever01\JoeSmith] and the copied/moved database is not named under [Serve01] but under [Server02] with user id [Server02\JoeSmith], does the mentioned sp still works that the application allows to open the copied database version !?
*** Server01 and Server02 has no connection AND NOT under the same domain. the only common is the user database***
Thanks and apologize for the knocking your door again.
April 1, 2009 at 8:47 am
Mohit,
I got it. Thanks for/to ALL !:-)
April 1, 2009 at 9:10 am
Cheers :). Sorry didn't get back to you, have fun with Mirroring. I absolutely love Mirroring :blush:.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply