March 1, 2012 at 4:31 pm
I'm looking for a way to script our deployments. We have multiple databases and our deployments include dropping and creating stored procedures and creating new tables. Currently we do this manually via SQL Server Management Studio.
As we grow and add databases, this is unsustainable. Is deployment best done via PowerShell, via SSIS, via something else? Can you point me to a resource I can use to learn more?
Thanks
March 1, 2012 at 7:08 pm
Red Gate's SQL Compare product would pretty much do it all for you. If data is also involved, the use of Red Gate's Data Compare product would do that. And, no... I'm not an employee of Red Gate. I just like their stuff.
--Jeff Moden
March 1, 2012 at 8:07 pm
Thanks. I'll give them a look.
March 6, 2012 at 2:58 pm
SQL Compare looks like a great tool.
But, assuming I now have a script I can run on all my databases, I'd like to schedule it during off hours. The Red Gate SQL Multi-Script tool looks good for multiple databases and multiple servers, but I can't schedule it, unless I'm missing something.
March 7, 2012 at 4:41 am
We use dbghost and you can run this from the command line. It's a very good tool.
March 7, 2012 at 7:03 am
I used to do deployments by .bat file, but PowerShell is SO much simpler -- OK, it's a little complicated at first, but once you get the hang of it, it's AWESOME!!!
Basically, I loop through an array of servers, and on each server, a list of databases, and in each database, run a list of sql scripts.
in pseudo-code,
foreach ($server in $Servers)
{
foreach ($database in $Server.Databases)
{
if $database <meets some criteria, is not system DB, for example>
SQLCMD script 1
SQLCMD script n
}
}
March 7, 2012 at 12:23 pm
How do you log errors?
ie: If a script #6 dies on database #20, how do you know that?
March 7, 2012 at 12:37 pm
SQLCMD has an -o outputfile parameter, to which output and errors can be written.
I actually have a function which:
1) Receives servername, dbname, infile, outfile
2) Writes to outfile the time, server, db, and infile name
3) Connects to the server.database, applies the infile, and writes whatever output to the outfile.
So in my previous example, the "SQLCMD script 1" is in fact a call to this function.
You can read up on BOL - SQLCMD - about sqlcmd utility.
And there's also a section SQLCMD - Poweshell.
March 7, 2012 at 12:51 pm
It looks like DBGhost is more of a source management tool than a deployment tool. It also uses VSS, which we're moving away from.
It looks like the Packager Plus Professional updates to just 1 database, but it sounds like you're saying that the command line may get us past that. Looking at the forums, it looks like the compare and update process are intertwined, such that I can't get to the change script to then apply it to my mulitple production databases. The forum isn't very widely used, so I don't know if there's been an improvement in the software since I read about those restrictions. If you have experience doing things this way, I'd be interested in hearing about it.
Thanks
March 7, 2012 at 1:05 pm
Can you include your function (obviously substituting <username> for the username and other sensitive information)? I'm new to scripting outside SSMS and new to PowerShell, so any information you can share would be helpful.
March 7, 2012 at 1:10 pm
Sure, here you go. I use Windows security, which appears to be the default for sqlcmd, as I'm not setting that switch in the call below.
function ApplyScriptFile ()
{
param ($DstSrv, $DBName, $Inputfile, $Outfile)
[string]$Dsrv = $DstSrv.Name
[string]$time = Get-Date
$Message = "`r`n$time - $Dsrv - " + [string]$DBName + ": Applying " + [string]$Inputfile
Add-Content $Outfile $Message
&sqlcmd -S $Dsrv -d $DBName -i $Inputfile -m1 | Add-Content $Outfile
}
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply