June 28, 2010 at 11:52 am
I got it working. I needed to Grant Connect on endpoint on the principal side, that's what was screwing it up.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\someuser];
WOW. Thanks for your help everyone!!!!!
June 28, 2010 at 12:29 pm
alex.kolesar (6/28/2010)
I got it working. I needed to Grant Connect on endpoint on the principal side, that's what was screwing it up.GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\someuser];
WOW. Thanks for your help everyone!!!!!
Glad to see you figured out what I was trying to tell you. By the way, I had to do it on each of the servers, not just the principal server. If you didn't, then great.
June 29, 2010 at 5:56 am
I have two more questions for everyone.
1). Say my principal box dies. Do I go to the mirror box and simply run the command, "RESTORE DATABASE <dbname> WITH RECOVERY" to make the database usable?
2). What IF i reboot one of the SQL servers? Will this screw up the mirror? Will the mirror automatically restart once the machine that was rebooted comes back on line?
Thanks again!
June 29, 2010 at 6:15 am
hehe, I couldn't wait to use this answer 😀
It depends.
It depends on whether or not you are using a witness in your setup. With a witness, High Safety, either mirror or principal can go down without affecting availability(there will be a disconnect for whoever is logged on). When they come back online, they should rejoin the mirroring session and synchronize without issue.
If you are not using a witness, High Performance, if either goes down, you'll need some manual intervention either removing mirroring or restoring a mirror to principal.
June 29, 2010 at 6:17 am
Okay, you have configured database mirroring, what next.
First, is you mirror configured with a witness to allow automatic failover?
Two, have you configured your application database connections so they are aware of the mirror?
If the answer to both questions is yes, then you don't need to do anything really if your principal database goes down, the failover is automatic. If you should accidemtally reboot the principal server, the mirror server will take over and when the pricipal server comes back up it will become the mirror server until you manually failover back to it or the mirror server is subsequently rebooted or goes down.
I highly suggest that you read as much as you can about database mirrors in Books Online, and if you have more questions, come back and ask.
June 29, 2010 at 6:34 am
Thanks for the quick replies. There is no witness server setup between the two but the mirror is setup as "High safety without automatic failover". The applications are also not configured to look at the mirrored box if they cant connect to the principal box. I'll read up as much as I can on this but it looks as I'll have to recreate the mirror anytime one of the boxes has been rebooted, am I correct?
June 29, 2010 at 7:57 am
Rebooting a server does not break the mirror, it will reestablish when the server comes back online. My question is what is the purpose of the mirror if your application isn't going to use it?
June 29, 2010 at 7:59 am
The application will use it. The developer will have to go in and change the code on the program to point to the new database or he can change the software to have failover.
June 29, 2010 at 12:20 pm
Without knowing the application, if it uses an ODBC connection you can add a mirror database server through the ODBC configuration in windows.
If the application uses an ADO .NET connection, you can add a partner (mirror) server connection string.
If the application developer would need to change the connection, this might be the connection type they are using. Ask them, see what they say.
In agreement with Lynn, if mirroring isn't used by the application, wouldn't you agree it is a waste of resources to have a mirrored database that would require an application change to utilize?
June 29, 2010 at 12:53 pm
It's a simple code change for him to point the software at a new ip. I guess the reason it's a mirroed database is so it's easy to have a fail over. If we just created a back up every day, I think it would take longer to restore the backup and transaction log file than to simply type in RESTORE DATABASE <dbname> WITH RECOVERY, or not?
June 29, 2010 at 1:44 pm
Alex,
I'm in the same boat as you are. I'm putting together a mirrored server with a witness for automatic failover. I have multiple applications that range from webservices to VB6 apps that connect to our DB. I started looking at how to make applications "mirror aware" for automatic failover and although I'm not a application developer the resources I read stated it was essentially a few lines of code to add this functionality into the apps.
I'm not sure if your application is a production app but if it is I would add the failover connection strings. Your going to do it now or when the DB Server fails over. Plus the benefits of doing it now is you can test the failover to make sure it works and you can also test under a stress free environment instead of a bunch of really pissed off users/bosses/customers yelling it's not working. Also you having to code it and roll it out may cause the outage to take even longer (if it works the first time). And yes it may be easy to code a new connection string but I've seen many a failed attempts at simple coding. Also what happens when you failback? Gotta code it again... up to you of course.
April 2, 2014 at 6:06 am
Hi,
Recently we have faced same problem . but through SSMS Mirroring across the edition in same version not supported. you may use script for the same.
For further detail pleas chck this.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply