|
Heavy Duty LiteSpeed Log Shipping Part 2
Configuration and Deployment
By
Bill Wunder
In this
article we will turn our attention to the configuration,
initialization, and processing of a SQL LiteSpeed based log
shipping.
In the
previous article I left you pondering the sizing requirements
for your backup file space and other planning considerations.
Exactly how much disk space you’ll need for file system staging
of backup files is obviously a tough call and will vary
considerably from application to application, database to
database, shop to shop. The good news is, what ever you needed
for native log shipping you can probably get by with one forth,
one eighth, or even less - if your careful - of that space when
using SQL LiteSpeed and will undoubtedly still be able to store
as many – or more backup files. Perhaps the best strategy is to
try to share the potential space needed with other disk space
consumers that also have a small actual disk space requirement
when compared with potential disk usage. The thinking in that
suggestion is that it is less likely that two or three processes
will all dip into their potential usage allocation at the same
time. This is made easier because both the backups and restores
done by this log shipping utility are intended to work from a
network share.
With your disk
allocation planned the next step is to
download the Heavy Duty LiteSpeed Log Shipping stored procedures and save them all to a location
that you and your SQL Server Service account have permission to
access from the network. Be sure to save each procedure script
to a file named as indicated in the download script. If the
names aren’t just as indicated the configuration process
automation won’t work.
As with native
SQL Server log shipping, it is necessary to create a share and
give it the correct permissions. The user that needs access to
the share will be the SQL Agent service account. That means,
first of all, you need the permissions necessary to create the
share and properly set permissions to it. It also means that the
SQL Agent service account must be a domain user. Make sure that
account has change access to the network share. If both you and
the SQL Agent login account are local administrators on both SQL
Servers and you have sufficient room to stage backup files on
the standby server getting permissions working should be
relatively easy. If you want to stage files on a file server on
your network, you may want to work with your network admins to
make sure the share and permissions meet their standards.
Permissions can get complicated depending upon the security
model in your environment.
It should go
without saying – but I’ll say it anyway – that you need to make
sure SQL
LiteSpeed backup software is installed and properly working
on both servers before you can use it to ship logs.
And of course,
as with any database administration tool I create, the scripts
expect to find a database named admin on each SQL Server
involved with log shipping. It shouldn’t take more than a MB of
allocated database storage to hold the three tables and 7 stored
procedures used to ship logs.
The final
thing that needs to be done before you can use the stored
procedures I use to configure, initialize and process log
shipping is to define a SQL login that is a member of the
sysadmins role on both servers to be the owner of the scheduled
job that will ship your log backups. You can use “sa” or you can
define a new SQL login. Either way the user must be a member of
sysadmins in order to perform the SQL LiteSpeed operations used
in log shipping processing. I use a SQL user because a trusted
user would require delegation through Active Directory in order
to get this job done and I don’t have Active Directory. There
are several operations that will require sysadmin level SQL
Server permissions. I suppose you could always make [NT
AUTHORITY\ANONYMOUS LOGON] a member of sysadmins on the
destination server and use a trusted connection, but I certainly
wouldn’t recommend it! One final recommendation is to give the
SQL login the same name and password on both servers. This will
allow you to use the admin.dbo.AddLinkedServer stored procedure
as provided in the script Library when configuring the linked
servers. If you use different passwords be sure to provide a
password mapping in your linked server configuration.
OK, now that
you have a share, SQL LiteSpeed is installed, the admin database
exists on both servers and you’ve created a SQL login to be the
owner of your log shipping job your ready to compile and execute
the DBAConfigSLSLogShipping stored procedure. You’ll need to log
in to the server you’ll be shipping logs FROM – the Source
server - through Query Analyzer using a trusted connection that
is a member of sysadmins on both the source and destination
servers. We’re going to use a trusted connection for this
procedure only because in this procedure we will make sure the
SQL login that will be executing all other procedures is member
of sysadmins on both servers. Delegation won’t come in to play
in the DBAConfigSLSLogShipping procedure because we will make
used of osql via xp_cmdshell to get our cross server work done.
Open the script dbo.DBAConfigSLSLogShipping.PRC, compile it,
then execute it using the information you’ve already determined
for the parameters. Here’s a brief description of each
parameter:
@StandByServer – The destination SQL Server
@ConfigFilesPath – UNC path to the stored procedures that you
have down loaded and save using the exact naming convention
indicated in the download script
@logShippingUser – The SQL login we just discussed that will own
the log shipping job
@logShippingShare – UNC share (and path if needed) that you
created to stage all backup files.
@DropExistingTables - If you ever need to completely re-init the
log shipping installation you can specify the fifth
parameter as true to wipe out any existing log shipping control
tables. Otherwise leave it at the default value of False.
Normally, you won’t need to specify this parameter. It’s purpose
is primarily for the case where the structure of a table needs
to change.
Upon
execution, the procedure will verify that your configuration
efforts are acceptable, create the three tables used by the log
shipping process and compile the other six log shipping
procedures on the correct server. Be sure to review the output
generated by procedure as any problems in your configuration
should be pinpointed for you. Make corrections as necessary and
re-execute until the procedure runs without complaint.
On the source
server in the admin database you’ll end up with three tables:
admin.dbo.SLSLogShippingConfig – contains the parameters provided
by the DBAConfigSLSLogShipping procedure. You can configure log
shipping to multiple serves. Each destination server would get a
new row in this table
admin.dbo.SLSLogShipping – contains the parameters provided for
the procedure admin.dbo.InitSLSLogShipping. You can init log
shipping for multiple databases. Each database would get a new
row in this table
admin.dbo.SLSLogShippingLog – contains information about the log
backups in the “active set”. Each backup file will get a new row
in this table.
And the stored
procedures:
InitSLSLogShipping - establishes the basic settings for log
shipping of a database. These are discussed below. This
procedure also sets the database to the full recovery model,
does the full backup and restore necessary to attain initial
sync between the log shipping pair, creates a scheduled job that
will move log backups between servers, and creates a linked
server to the destination server.
GetSLSLogPrimary – executed by the scheduled job at the interval
you have specified during the initialization to do the log
backups and call the restore procedure via a linked server call.
This procedure is also used to fail over to the destination
server. The failover will also include any jobs with identical
names that you have established as enabled on the source server
and disabled on the destination server prior to failover.
AddLinkedServer – called by InitSLSLogShipping to create the
linked server used by GetSLSLogPrimary to call the restore
procedure on the destination.
NotifySLSLogShippingFailure – executed as a second step of the
log shipping job to notify of any errors that occurred. Please
see my previous articles
“from xp_sendmail to
safe_sendmail” and “From
SQLMail to SMTP in the SQLAgent” for a full explanation of
the SMTP based email protocol used by this procedure. Note that
if you’re not using the mail subsystem described in that article
you’ll need to modify this procedure by removing any references
to “sysmon.dbo.safe_sendmail” else the procedure may not compile
for you if the sysmon database doesn’t exist on your server.
On the
destination server you’ll have the procedures:
ApplySLSLogSecondary – Restores backups to the destination to
StandBy Mode to allow read only access and when directed to do
so recovers the destination database. See SQL Server Books On
Line RESTORE command topic for a full description of Standby
Mode. The procedure will also attempt to remove any users from
the destination database through a call to ExpungeUsers before
each restore and it will recover the database for read/write
activity when told to do so.
ExpungeUsers –
removes all connections from the destination database before
each restore
Once you have
successfully executed admin.dbo.DBAConfigSLSLogShipping you have
a fully configured log shipping installation ready to ship logs
from any database on the source server to a database with the
same name on the destination server.
In the next
article we’ll get into the day to day processing of Heavy Duty
SQL LiteSpeed Log Shipping.
Bill
|