January 6, 2009 at 11:07 am
The actual error for the transaction log (I've gotten it on two other db's now too) is:
The mirror database, "UserDefinedDatabase", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)
January 6, 2009 at 11:09 am
Zuke,
About the log file, my guess is that another transaction log backup had been taken before you took one, maybe run by a scheduled job. You should be able to find out by using one of the Reports on the context menu for your database, there should be one that reports for backup and restore events.
Sounds like you have the steps right though. Is there alot of activity on the database between the time you took the backup from the primary and restored to the mirror?
1. Take a full backup of the primary database
2. Copy the .BAK file to the mirror server
3. RESTORE with NORECOVERY on the mirror server
January 6, 2009 at 11:19 am
Yes, there is a lot of activity, and for the life of me, I can't seem to take the DB offline. My guess is the application that is using it has exclusive rights to it, and to take the DB offline would require taking the application down first.
I tried this on other DB's that aren't as active, and was able to get past the transaction log error, but then I get:
Database is not configured for database mirroring (Error 1416).
Which seems like a real kick in the teeth after everything else that's been going on.
Also, when this is all over and (hopefully) completed, do I change the application that's using the DB to look at the witness server instead of the principle?
January 6, 2009 at 11:37 am
How about taking a step back. Try setting up mirroring without the witness first using a test database. Then after you get that working add the Witness. Better to start with fewer pieces.
1. Create a new database; make sure it's in full recovery model.
2. Backup the database
3. Restore to mirror
4. Setup mirroring
Take a look at this paper on mirroring in SQL 2005. Some of the terms for the availability modes have changed but everything else is relevant.
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
As far as the client goes you do need to use the SQL Native Client (SNAC). For the failover to be transparent from the applications point of view it would already have to include some type of retry logic.
January 6, 2009 at 11:46 am
I'm afraid I don't quite understand what you mean with the retry logic. The witness server has native SQL client running, but it's not the principle database. If I'm understanding what that paper you sent me is saying, if the prinicple goes down, the mirror will switch itself to principle. However, what's to tell the application to look in the new server address?
I was under the impression that it was the witness that switches between the principle and mirror.
January 6, 2009 at 12:02 pm
The witness doesn't switch the roles of principal and mirror, it's just there so that 2 out 3 agree on who's who.
With the SNAC client you can configure the connection string to have the names of both the principal and mirror servers, or when the application connects it will get the mirror's name.
As far as retry logic that just means the application would have the ability to reconnect on failure. Even though the database can failover the client still has to do something, such as make the user restart the application or have the application retry connecting.
January 6, 2009 at 12:20 pm
Yes, I know it does that automatically (so at least that's taken care of).
It seems I won't be able to do this until I can take everything offline first. Our end users are rather anal about that, so I'll hold off till our scheduled service next Tuesday night before doing it all. Just so I have a proper to-do list, here's my plan:
1) Offline the principle databases (there are 4 total), back them each up
2) Move all 4 backups to the new server (mirror)
3) Restore all 4 backups on the mirror
4) Create the 4 principle backups for transaction logs
5) recover them to the mirror in the same way as before
6) run the mirroring wizard
7) tell the Native SQL Client on the principle to give both its address, and the full address of the mirror
8) put everything back online
Did I get all that right?
Here's my problem, and then my question:
Problem- I created a small, unused test DB and tried it using all the steps above, and I got that same 1416 error "Not configured for mirroring". Didn't I just configure it? Wasn't that the wizard I went through? What's missing?
Here's the question- It seems that the whole principle-mirror-witness relationship requires the principle to still be up and running enough to redirect to the mirror in the even of a failover. What about if the failover is needed because the server caught on fire or something? If the server isn't there anymore, then it isn't there to tell the client to go to the mirror. That is why I always figured the witness was the one that switched it.
EDIT: Rereading everything, I might have answered my own question; I don't tell the Native Client on the PRINCIPLE where the mirror is, I tell the CLIENT server (or the one hosting the app that uses the DB) where the mirror is. Right?
January 6, 2009 at 1:19 pm
1) Offline the principle databases (there are 4 total), back them each up
Before you go any further with mirroring check the section in that article titled Multi-Database Issues. If you have one application that uses 4 databases mirroring might not be what you want. 4 seperate applications to 4 seperate databases would be a different situation.
All the other steps look reasonable. However, if you get your test database working then you'll already have your mirroring endpoints. In that case all you have to do (excluding the witness)
1) On the mirror server run:
[font="Courier New"]ALTER DATABASE myDatabase1 SET PARTNER = N'tcp://myprincpal.domain.com:3022'[/font]
On the principal server run:
[font="Courier New"]ALTER DATABASE myDatabase1 SET PARTNER = N'tcp://mymirror.domain.com:3022'[/font]
7) tell the Native SQL Client on the principle to give both its address, and the full address of the mirror
The SQL Native Client woudl be configured where the application is run from. (From reading your edit I think you got that.)
Problem- I created a small, unused test DB and tried it using all the steps above, and I got that same 1416 error "Not configured for mirroring". Didn't I just configure it? Wasn't that the wizard I went through? What's missing?
If you have the endpoints already setup then try setting the partners like above.
If the server isn't there anymore, then it isn't there to tell the client to go to the mirror. That is why I always figured the witness was the one that switched it.
Only 2 out of 3 roles have to agree. Using your example of the principal being down, since neither the witness and the mirror can see the principal then the mirror takes over the role of principal.
January 6, 2009 at 1:56 pm
What I hope to be the last question (and probably the dumbest sounding), how do I issue commands directly like that? I've been using SSMS the whole time.
January 6, 2009 at 2:04 pm
From a query window. Same thing you used when checking the endpoints before.
January 6, 2009 at 2:45 pm
:ermm:
I didn't use a query window last time. A friend showed me how to test the endpoint using the ODBC panel in Control Panel.
January 6, 2009 at 2:52 pm
In SSMS right click on your database then select New Query.
You might find this video on SSMS helpful:
http://www.sqlservercentral.com/articles/Video/65066/"> http://www.sqlservercentral.com/articles/Video/65066/
January 6, 2009 at 4:08 pm
I ran those queries as you wrote them, and I get "Incorrect syntax near 'n'."
January 6, 2009 at 5:46 pm
If in doubt about the syntax you could always check Books Online.
Did you change the database name, server name, domain name and port numbers to match your configuration?
January 6, 2009 at 8:41 pm
I have no idea about the syntax 🙁
I changed it to:
ALTER DATABASE Webapp SET PARTNER = N'tcp://agrippa.fchhh.local:5022'
AGRIPPA is the mirror and JERUSALEM is the principle (ATHENS is the witness).
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply