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 |
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-05-19 : 14:48:10
|
I'm trying to create a log shipping procedure to simplify (hopefully automate) the role change operation for SQL Server 2005 Standard. I'm at a point where I need to pass configuration information between the primary and secondary servers. Here's a snippet::setvar serverPri "serverA":setvar serverSec "serverB":setvar DBPri "myDB":setvar folderBak "\\serverC\backup\":connect $(serverPri)declare @backupfolder varchar(128)select @backupfolder=pd.backup_sharefrom msdb.dbo.log_shipping_primary_secondaries psinner join msdb.dbo.log_shipping_primary_databases pd on ps.primary_id=pd.primary_idwhere ps.secondary_server='$(serverSec)' and pd.primary_database='$(DBPri)'print @backupfolder -- this works like I want:setvar folderBak "@backupfolder" -- this doesn't work like I wantGO:connect $(serverSec)print @backupfolder --this errors out because it's a different server My question is: Is there a way to pass data from SQL Server into a SQLCMD variable that can persist across connections? Some options I've tried or considered are:- Query the log shipping tables via linked servers: REJECTED due to security issues (credit card processing, SAS70, PCI, etc.)- Write data to file share: REJECTED also for security reasons (xp_cmdshell disabled, UNC access restricted, SAS/PCI)- Existing script on the internet that does this already: LOOKED and couldn't find one for SQL Server 2005- Other option I haven't thought of: HOPEFUL that someone can provide an alternativeWe are going to use database mirroring but also need log shipping as an additional failover option for multiple locations. We would need to have a script to set up log shipping on the mirror in case we fail over to it. Service Broker is an option but will require full automation of log shipping config at failover and failback.Any help or advice is appreciated. Thanks. |
|
|
|
|