March 27, 2009 at 5:17 pm
I have a question which may be dumb. Please be patient. I understand that the database mirroring is for user database only!? How could I ensure the failover would run smoothly (a) when the principal database is down (b) when the server where the principal database is down (in this case do I have the copy/update the system database like master/model/msdb in advance!?). Thank you in advance.
March 27, 2009 at 5:27 pm
I am guessing you are thinking in terms of clustering. Mirroring is not Clustering. Honestly, in my opinion it's not a viable solution for existing applications because it requires ADO.net in order for applications to take advantage of the automatic failover. Otherwise you'll have to do system wide connection string changes (depending on how big your environment is) anytime a failover occurs.
But yes mirroring only works on the database level. Your jobs, system db's, logins, and everything that belongs on the instance level will not be failed over to the secondary server.
March 28, 2009 at 12:49 pm
francis (3/27/2009)
I have a question which may be dumb. Please be patient. I understand that the database mirroring is for user database only!? How could I ensure the failover would run smoothly (a) when the principal database is down (b) when the server where the principal database is down (in this case do I have the copy/update the system database like master/model/msdb in advance!?). Thank you in advance.
As Gabriel said the system databases don't fail over. But only things that must be same are jobs/logins in two servers. Login can be created on both server with no major issue; except when setting default database.
On new databases before you give them over to users ...
- If SQL Server login/AD (single) then failover to mirror set default and failback.
- If there are multiple AD logins, create and AD group and grant access that way.
- If it is multiple SQL Server logins; then you have to do make sure your connection string has the initial database setting.
For Jobs you have to add an extra step in them now on each server ...
- Check if database is online if not don't execute the job.
For backups, use Maintenance plans ...
- The maintenance plans are intelligent that is, if the database is mirrored partner it will all database that are set as mirroring even if you choose all databases. And if the database fails over it will automatically start getting backed up.
In programming to take full advantage of mirroring you have to use .NET because of connection string caching option. When application makes a connection to principal the principal replies back with Principal-Mirror server information. From that time froward if the connection fails the cache information is used to re-direct clients.
Thanks.
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 28, 2009 at 5:38 pm
First, thanks to all. I am concentrating on the mirroring database. I have two databases (where there are more databases but only two are my targets) which I need to them to setup to be mirrored. Still my concern is the system databases and logins (master/msdb/model), do I need to copy them constantly and as frequent as the mirroring!? Also, I could mirror the two target databases on the same server ?! Thanks a lot !
March 28, 2009 at 6:35 pm
francis (3/28/2009)
First, thanks to all. I am concentrating on the mirroring database. I have two databases (where there are more databases but only two are my targets) which I need to them to setup to be mirrored.
That is not an issue Mirroring will support some databases being mirrored because mirroring is database level only.
Still my concern is the system databases and logins (master/msdb/model), do I need to copy them constantly and as frequent as the mirroring!? Also, I could mirror the two target databases on the same server ?! Thanks a lot !
No you don't need to mirror system databases, you can't actually. If you are getting frequent new logins use AD groups and give that AD group access to the database. That way the SID will be same between both computers.
Thanks.
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 28, 2009 at 7:04 pm
Thanks again. In regards to the "SID", I have another question which I have not solved so far. I understand, as you have mentioned, within the same domain the "SID" remained the same as long as under the same tree. How about if I move the database somewhere else under other domain/server, for instance an individual box which does not have any network connection with the original, how do I change/switch the original SID so the application of the new box could "accept" the copied/moved database ?! Salute !
March 28, 2009 at 7:12 pm
I have some other people trying this I am not sure how well it works ...
1) Create SQL Server logins on both servers with same name.
2) Create job on that continuously monitors to see if the database is online.
3) If database is online it remapps the database user to the server login.
So this will cause up-to 1 min dis-connectivity...
So lets say...
Your get a User ID of 10 on Server 1 for User "JoeSmith" then you get User ID of 12 on Server 2 for User "JoeSmith"; make sure their passwords are the same.
You grant JoeSmith access to MirroredDB on ServerA, it creates a mapping between server A's user login.
Database fails over to Server B.
Job checks to see database is online.
Job executes "sp_change_users_login" to re-point the JoeSmith in MirroredDB to JoeSmith on Server 2 giving it SID of 12.
Thanks.
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 28, 2009 at 7:49 pm
Thanks again. This is clear.
The following is SID related but have nothing to do with mirroring.
If the database is moved/copied to another box/server, which has totally nothing to do with the original network/server/database, but does not recognize by the new box since SID differs. In this scenario, how could I change the SID (not the same logins) in order the application could associate with the copied database ?! Thanks.
March 28, 2009 at 9:30 pm
sp_change_users_login is a stored procedure that remaps the SID (SQL LOgins only)
So Server A:
You have a Username JoeSmith with ID12 ...
when you move it to Server B..
ID12 is JohnAnderson but ID10 is JoeSmith...
it will match the Name JoeSmith with JoeSmith and update the internal tables to change ID12 to ID10.
Thanks.
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 29, 2009 at 7:02 am
All right! You're the best. Thanks.
March 29, 2009 at 4:25 pm
Further to my previous response, could I run the sp to the new server's system Administrator or sa instead of "JoeSmith " in order for the application to recognize the new copied database, in which the new Administrator/sa is accepted !? Thanks again. (This could eliminate the problem of new server to be named/renamed as old server ==> OLDserver/JoeSmith / Newserver/JoeSmith)
March 29, 2009 at 7:01 pm
I am not understanding what you are asking? If you are thinking of mapping the SA account I wouldn't advice it; because that is a huge security risk.
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 12:02 am
Mohit (3/28/2009)
For backups, use Maintenance plans ...
- The maintenance plans are intelligent that is, if the database is mirrored partner it will all database that are set as mirroring even if you choose all databases. And if the database fails over it will automatically start getting backed up.
This is wrong. The maintenance plan will fail when it hits an offline database (the mirror). So any other databse you have on the server that comes in the list after the mirror database and any steps in the plan that come after the will not occur because the job failed.
You need to use T-SQL for the backups and have it check the state of the databases in sys.databases before backing up a database.
March 30, 2009 at 1:59 am
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.
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 7:39 am
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 !
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply