Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Log Shipping Fail Over & Questions

Author  Topic 

spepi
Starting Member

17 Posts

Posted - 2005-06-20 : 16:36:54
I just set up Log Shipping. Im new to SQL, so excuse me if I get some terms wrong.

Log Shipping seams to be working...I can see the .trn files backed up the secondary machine, secondry machine db is in read only mode.

1. What steps do I need to do to use the secondary server? I want to implement a server failure and see how the failover method works, but I dont understand what I need to do.

Need help..going blind with all the different ways to do this...Im looking for a solution that is not too painful and has a quick turnaround to use secondary server with manual intervantion.

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-20 : 16:45:17
Once you have determined you need to move to your secondary server, you need to see if you can perform a final transaction log backup on the primary server. If you can, then great, you won't miss any data. Do that, then copy the file over to the secondary server, then restore the tlog using the WITH RECOVERY option of the RESTORE command. If you can't perform a final tlog backup, then you'll need to use the RESTORE command using the WITH RECOVERY option on your last tlog. So that one will have restored twice. First time via log shipping using WITH NORECOVERY option. Second time via manual RESTORE command using WITH RECOVERY option. Your secondary server is now ready. You might need to unorphan your user accounts using sp_change_users_login. Some of our applications hard code a few things in the database that we need to update when we switch database servers, so now is the time to run these if you have this "issue".

And don't forget to drop log shipping once the secondary server is up. If you are unable to do this since the primary is unavailable, you'll need to manually delete data from the msdb..logshipping* tables, then you'd be able to drop the maint. plan.

Tara
Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-20 : 16:50:06
Hi Tara..I was hoping you would get this. Seems you are the goddess, but I am so new to SQL, and this log shipping....can you dorect me to a Log Shipping for Dummies area. DO I need to create Backup devices, then jobs owned by the sa..BCP Out...Copy File...Transfer logins..as you can see..im lost..almost qitting time, but I will continue to use this forum, as it seems to be quite active.

thanks again Tara

Newbee in MA
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-20 : 16:54:06
I'm confused. Are you referring to log shipping via SQL Server 2000 Enterprise Edition or custom log shipping via scripts?

If you set it up via Enterprise Edition, then log shipping already setup everything. You are now ready to practice the failover.

If you set it up via your own or someone else's custom ls scripts, then I probably won't be of much use as I've never done it that way.

There is no good Enterprise Edition log shipping documentation that I have found. Another DBA should me how to configure and troubleshoot it. I've learned the rest by being in the trenches with it. Books Online has some information about it, so you might try there.

Tara
Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-20 : 16:59:37
I am working with SQL 200 ENT. Nothing more. nothing custom...in the trenches, but th elanguage is what baffles me...there are no good docs out there...each one is dofferent. if this helps...my servers are
Pri- Web-test1
Sec - Web-test2
Monitor - Web-Dev
DB is WebReg
Used all defaults when setting up Log Shipping
Backup share location on each server is Logship-backup
Connectivity is fine with each
Time is all synced up

Im just not sure how to do the Failover. What do I type...where do I run these commands..should I just shut down the Primary...

Well, im off for the day..hopefully you'll be around tomorrow.

thanks
Scott in MA
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-20 : 17:05:15
If you can get to the primary server in your failover scenario, then to perform your final tlog backup, you run BACKUP LOG inside a Query Analyzer window while connected to that server. Then manually copy the file over to the secondary server using your favorite copy method. Then connect to secondary server in Query Analyzer and run RESTORE LOG using the WITH RECOVERY option. Check out BACKUP LOG and RESTORE LOG in SQL Server Books Online for details on both commands.

If you turn off the primary server, nothing will happen. Failover is not automatic with log shipping. If you want automatic failover between two servers at one site, then you need to implement clustering and not log shipping. We actually use both. We use clustering at the main site in case of hardware problems. We then log ship to a disaster recovery site that is over 300 miles away. We use this for disasters like San Diego separates from the main land due to recent earthquakes .

Tara
Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-21 : 08:21:18
Good Morning Tara,

OK, I understand I would need a cluster for auto failover. This isnt an issue. I know its a manual thing. Well...I can get to the Primary server as it is in house. When I follow the above steps, how does the BACKUP LOG command know which log to back up? Does it back everything up? When restoring log, will this turn the read only DB to active, so it can be written to? The I can turn off log shipping on that DB? Where can I find a recent copy of SQL Server Books Online. I have done a search...which one would you recommend?

thanks
Scott
Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-21 : 09:03:10
Well...thank goodness I am working with a test environment. Looks like Log shipping failed, so I have removed it from the server. What is the best practice to keep the DB syncronized? Should I detach the primary server webreg db and attach it to the secondary server and set up log shipping, or should I just create a new DB with the log shipping wizard?

Again, back to DTS jobs. Do I need one for transfer login? Do I need a linked server? Do I need backup devices configured? Am I getting in too deep. Is it a simple procedure to failback?

Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-21 : 12:00:10
OK Im back...I dont know what happened, but my log shipping is hosed//I tried to set it up again, but I keep getting an error
14261
http://support.microsoft.com/default.aspx?scid=kb;en-us;q298743&sd=tech
but it doesnt clear out.....Help is needed....
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 12:36:42
It appears that you are missing some fundamental knowledge about DBA tasks. Before even setting up log shipping, you need to have a firm grasp of BACKUP DATABASE/BACKUP LOG/RESTORE DATABASE/RESTORE LOG. I'd suggest getting up to speed on those, testing them out with all sorts of different scenarios, then trying out log shipping again.

I don't use DTS to transfer users.

Backup devices are not required. I haven't used them since SQL Server 6.5.

Once you have failed over to the secondary server, you can not easily fail back over. You either have to resetup log shipping in the other direction, or you'll need to do full backups.

The SQL Server Books Online version that you need is the one that was updated for service pack 3a. Check out www.microsoft.com/sql for details.

Tara
Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-21 : 16:09:39
Im am totally stuck at this point. I have removed databases, reinstalled, attached databases and I still get Error 14261.
I can set up log shipping from secondary to primary, but not the other way around any longer.
Any tips on how to clear this up?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 16:13:34
Error 14261 occurs when log shipping wasn't properly deleted. To properly delete it, you must go to the maintenance plan, go to the Log shipping tab, and click delete. You then delete the maintenance plan. If you didn't do it this way, then you'll need to manually delete data from the msdb database. Go to the msdb database on both servers (or all 3 servers if you've got the monitor on a third server) and delete all of the data found in the log_shipping* tables. Then you should be able to setup log shipping again in either direction.

Tara
Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-21 : 16:23:06
I found the Log_shipping tables, but when I run a query on them, I get an error. How do I delete
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 16:27:12
You need to delete them in the order of child then parent. Take a look at the SQL Server 2000 system map to determine the correct order. Or you can do it the lazy way by once you get an error, try a different table, then go back to the error table. Eventually, you won't get any errors and the data will be gone.

Tara
Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-21 : 16:28:37
I went through each one, found data, deleted it...now back to the drawing board.....Hows the weather on the Left Coast?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 16:31:58
Weather is absolutely terrific! 75 degrees, blue skies, small white clouds...spectacular past few days.

Hot in Boston?

Tara
Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-21 : 16:47:59
Weather is nice today..not too hot..but we have had a weird weather pattern lately. I live in AZ for 4 years...what a difference..but I grew up in MA.
So, I right clicked on each table, and returned all rows. When I saw some data, I delted it. Are there more things to do? I recreated the log shipping, with another error...but I am too tired to recreate it. I will do it first thing in the AM...I'll let you know.
Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-21 : 16:55:14
Ok..couldnt wait....Error "Unable to copy initialization file to the secondary server "web-test2"
then
None of the destination servers could be set up for log shipping. This maintanence plan will not be created.


Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-21 : 16:59:05
I get that error occassionally too on our larger databases. So what I do is setup the secondary server's database first. So perform a full backup on the primary server, copy to secondary server, restore it to secondary server using an UNDO file (see SQL Server Books Online for details). Then setup your maint. plan, but during setup, tell it to use an existing database instead of having it create one for you. This will avoid that error.

Tara
Go to Top of Page

spepi
Starting Member

17 Posts

Posted - 2005-06-22 : 08:24:41
Hi Tara...thanks for your help so far. I figured out that I didnt have the same security set up on the secondary. I only had NT authentication and on the Primary, I had SQL and NT, so once I did that, I verified that each log)shipping table was clear of data and created the log ship maintanence plan with success.
Now that I know how to remove or trouble shoot creating log shipping plans, I will read up on my fail over techniques.
Talk to you later. Do you have a website of your own?

Scott
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-22 : 12:21:08
Nope. Just my blog:

http://weblogs.sqlteam.com/tarad

Tara
Go to Top of Page
    Next Page

- Advertisement -