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.
| Author |
Topic |
|
kish
Starting Member
45 Posts |
 Posted - 2004-02-26 : 01:47:52
|
| Hi All,Does anyone have a readymade presentaion on Log Shipping.I need to present it this weekend.Your early help will be highly appreciated.Thanks. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-02-26 : 07:22:34
|
| What are you paying ?Damian |
 |
|
|
kish
Starting Member
45 Posts |
Posted - 2004-02-26 : 07:32:55
|
| hey come on....i am just asking for help.If i had to pay..i could have approached Microsoft directly.pls. help |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-26 : 07:42:00
|
| Have you tried Books Online yet? Have you assembled any information on it at all? What exactly is your presentation supposed to cover?It's one thing to help someone, it's something else to do their job for them. No one here gets paid for their time or effort, it's done purely out of generosity. Your attitude is not gonna warm anyone towards helping you.Show that you've made some effort towards the project and you're a lot more likely to get help. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-26 : 08:26:28
|
| (grin)Log ShippingIn Microsoft® SQL Server™ 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. This allows you to have a backup server and also provides a way to offload query processing from the main computer (the source server) to read-only destination servers.Log Shipping ModelThe illustration shows the log shipping model.In this example, an enterprise has five servers: server A, server B, server C, server D, and server E. Server B is the source server, the server on which log backups and restores are performed and copied. Server C, server D, and server E contain the destination databases on which the log backups from server B are restored, keeping these servers in synchronization with server B. Server A is the monitor server on which the enterprise-level monitoring of log shipping occurs. Each destination or source server is maintained by only one monitor server. The Database Maintenance Plan Wizard is used to define an appropriate delay between the time server B backs up the log backup and the time server C, server D, and server E must restore the log backup. If more time elapses than defined, then server A generates an alert using SQL Server Agent. This alert can aid in troubleshooting the reason the destination server has failed to restore the backups.Do not use the monitor server as the source server, because the monitor server maintains critical information regarding the log shipping system. The monitor server should be regularly backed up. Keeping the monitor server independent is also better for performance, because monitoring adds unnecessary overhead. Also, as a source server supporting a production workload, it is most likely to fail, which would disrupt the monitoring. The source and destination servers can be on the same computer. However, in this case, SQL Server 2000 failover clustering may provide better results. For more information, see Failover Clustering.Configuring Log Shipping with the Database Maintenance Plan WizardTo easily configure log shipping, use the Database Maintenance Plan Wizard. With this wizard, you can: Define how often the logs are generated, the time between a backup and a restore operation, and when a destination server is out of synchronization with a source server. Register any new servers.Create the source databases on all destination servers. When adding a destination database through the Database Maintenance Plan Wizard, you have the option of creating the databases on the destination server or using existing databases. Any existing databases must be in standby mode before you can configure them for log shipping.Specify which destination servers might assume the role of the source server.Set a restore delay. This delay defines how old a transaction log must be before it is restored. If something goes wrong on the source server, this delay provides an extra time before the corrupted log is restored onto the destination server.Create a schedule that sets the backup schedule. Before using the Database Maintenance Plan Wizard, consider the following: The user configuring log shipping must be a member of the sysadmin server role in order to have permission to modify the database to log ship. You can configure log shipping only on one database at a time. If you select more than one database, the log shipping option on the wizard is disabled. The login used to start the MSSQLServer and SQLServerAgent services must have access to the log shipping plan jobs, the source server, and the destination server.When you use the Database Maintenance Plan Wizard to configure log shipping, you can log ship only to disks. The backup-to-tape option is not available. Configuring Log Shipping ManuallySQL Server 2000 supports manual log shipping from a SQL Server version 7.0 Service Pack 2 (SP2) transaction log if the pending upgrade option is enabled on the computer running SP2. To enable this option, execute the following code:EXEC sp_dboption 'database name', 'pending upgrade', 'true'However, when you are restoring the database after log shipping, you can recover only with the NORECOVERY option.Note When you manually configure log shipping between a computer running SP2 and a computer running an instance of SQL Server 2000, you cannot use SQL Server replication.For more information, see the SP2 documentation.To configure log shippingEnterprise ManagerHow to configure log shipping (Enterprise Manager)To configure log shipping with the Database Maintenance Plan Wizard Note Before you configure log shipping, you must create a share on the primary database to make the transaction logs available. This is a share off of the directory that the transaction logs are dumped to. For example, if you dump the logs to the directory e:\data\tlogs\, you could create the \\logshipping\tlogs share off the directory.In the Select Databases screen, select the These databases check box, and then select the database to log ship. If you select more than one database, log shipping will not work, and the log shipping option will not be available. You are not allowed to select a database that is already configured for log shipping.Select the Ship the transaction logs to other SQL Servers (Log Shipping) check box.Continue through the wizard, specifying the rest of the database maintenance options, until you get to the Specify the Log Shipping Destinations screen.Click Add to add a destination database. For this option to be available, you must have selected to use log shipping earlier in the wizard.In the Add Destination Database screen, select a server name. The server must be registered and running Microsoft® SQL Server™ 2000 Enterprise Edition to appear in the drop-down list. If you want this destination to become an available source destination, you must select the Allow database to assume primary role check box. If this box is not selected, this destination database will not be able to assume the source destination role in the future. If you have selected the Allow database to assume primary role check box, you must also specify the Transaction Log Backup Directory on the destination database to which the logs will be backed up.To change the transaction log destination database from the default location, enter a location in the Directory box.If the source database does not exist on the destination database, select the Create New Database check box. The Database Name box will default to the source database name. If you want a different database name on the destination server, specify a new name. If you have chosen to allow this destination database to assume the source role, you cannot change the database name from the default.If you have selected the Create New Database check box, you must specify the file directories for the data and log on the destination database in the For Data and For Log boxes.If the source database already exists on the destination database, select the Use Existing Database check box. If the database name on the destination server is different, enter it in the Database Name box. This database must have been restored using the WITH STANDBY option to properly accept logs.In the Initialize the Destination Databases screen, either: Click Take full database backup now. –or-Click Use most recent backup file to initialize the destination database. In the Log Shipping Schedules screen, view the default log shipping schedule. If you would like to alter the schedule, click Change.In the Copy/Load Frequency box, set the frequency, in minutes, with which you want the destination servers to backup and restore the transaction logs from the source server.In the Load Delay box, set the delay, in minutes, you want the destination database to wait before it restores the transaction log from the source server. The default for this box is 0 minutes, which indicates that the destination database should immediately restore any transaction log backups.In the File Retention Period box, specify the length of time that must elapse before a transaction log can be deleted.In the Log Shipping Thresholds screen, set the Backup Alert Threshold. This is the maximum elapsed time since the last transaction log backup was made on the source server. After the time exceeds this specified threshold, an alert will be generated by the monitor server.In the Out of Sync Alert box, specify how long a time has passed between the last transaction log backup on the source server and the last transaction log restore on the destination server. After the time exceeds this specified threshold, an alert will be generated by the monitor server.In the Specify the Log Shipping Monitor Information screen, type the name of the server that will monitor log shipping.Click either Use Windows Authentication or Use SQL Server Authentication to connect to the monitor server. The log_shipping_monitor_probe login name is fixed and must be used to connect to the monitor server. If this is a new account, choose a new password. If the account already exists on the monitor server, you must specify the existing password. Note Using the Database Maintenance Wizard to set up log shipping, you can log ship only to disks; the backup to tape option is not available.To remove log shippingEnterprise ManagerHow to remove log shipping (Enterprise Manager)To remove log shipping Expand a server group, and then expand the primary server.Expand Management, and then click Database Maintenance Plans.In the details pane, right-click the database maintenance plan to delete, and then click Properties.Click the Log Shipping tab, and then click Remove Log Shipping. This stops log shipping on the primary server, removes all secondary servers, and removes the monitor server. You must delete the database maintenance plan to remove additional jobs.Changing Log Shipping RolesSQL Server 2000 allows the changing of log shipping roles through system stored procedures. Before roles can be changed, several preliminary tasks must be performed. For more information, see How to set up and perform a log shipping role change (Transact-SQL).See AlsoFailover ClusteringDatabase Maintenance Plan Wizard©1988-2000 Microsoft Corporation. All Rights Reserved.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-26 : 12:34:39
|
| If you have Enterprise Edition for SQL Server than you already have log shipping. You just need to configure it. Derrick posted the information regarding the configuration from SQL Server Books Online.If you don't have Enterprise Edition, then you'll have to write scripts to do it. I think if you've got the SQL Server Resource Kit, you can also get log shipping from there. It's not the same one as in Ent. Edition, but does similar stuff.Tara |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-02-26 : 17:20:32
|
quote:
Originally posted by kish
hey come on....i am just asking for help.
I believe there is a subtle difference between asking for help, and asking someone to send you a ready made presentation. Especially if doing a presentation is part of your job.It's like the difference between helping someone write a query or sending them a shrinkwrapped application.Damian |
|
|
|
UddinF
Starting Member
5 Posts |
Posted - 2004-11-29 : 05:19:30
|
I have completed Log Shipping in my primary server log files are generated as per schedule, now I want to restore my log in my secondary server. This restore must be automatically.plz help me How I will do this |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-11-29 : 11:05:52
|
| That's great! Was there a question there?Start a new thread if there is....also, use the references which allowed you to set up the log shipping in the first place to find the commands which will help you figure out how to restore automatically. |
|
|
|
|
|
|
|
|
Log Shipping