## Copying SQL Logins with #Powershell

Managing SQL server logins, especially in the days of availability groups, can be quite the pain in the butt. Usually we’ll make use of scripts like sp_help_revlogin to manage this, copying logins en masse from one instance to another.  This works, but it’s pretty brute force and there might be logins you may not want to copy across.

So let’s use Powershell to be a little more…elegant. There’s a couple things we can use to get a nice login copying function that is flexible and robust.  The first is the .Script() method in the SMO’s Login object. This is actually a piece of functionality many of us use daily, whenever we script out objects in SQL Server Management Studio. The trick is to access this programmatically, which is a snap in Powershell.  The basic syntax is something like this:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$smoserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server localhost$smoserver.Logins[‘SHION\mike’].Script()

What’s awesome about this method is the SMO will also script out all of the additional server level options, like default database and server roles.  Very handy for maintaining the consistency of the logins from one instance to another.

There are a couple of gotchas. First , if we want to include the SID in our script, we have to create a  ScriptingOptions object and specify that the LoginSid should be included. Second, if the login is a SQL Server login, the .Script() method will add in a line to disable the login and give it a completely random password. This is done for security reasons, so that any login scripted out can’t create a possible security hole. As an FYI, you’ll get the same if you script out the login in SSMS.

So what do we do? Managing passwords across instances is tricky, but must be done, otherwise things will start to break. With Windows based logins it’s simple, since that’s all managed in the Active Directory. SQL logins require more care. To handle this in T-SQL, you’ll want to create the login with the SQL Server password hash. Unfortunately, the SMO doesn’t provide an easy way to do this, so we have to get clever. To be clever, we have to:

1. Retrieve the binary hash value from the source SQL instance.
2. Convert the binary hash to a string for use in a CREATE LOGIN statement.
3. Insert the password hash string into our CREATE LOGIN statement in the correct place.

Easy enough, right?  Honestly, all it takes is some string manipulation, which Powershell is pretty darn good at. Certainly better than SQL Server itself.  Turns out this is the biggest hurdle to the process and, once solved, the rest of the script comes together quite nicely.

The following function brings all these concepts together. It’s a little more complex than other scripts I’ve posted, but here’s the highlights:

• You must declare a source instance. This is the instance from which all logins will be scripted from.
• You can specify a string array/collection of logins. The function will only script out the logins you list. If you don’t list any (argument is null), the function will script out all non-system logins (everything but sa, pretty much).
• Because I like the pattern of scripts to build scripts, the function will create a .sql script of all your create login statements. By default this will go to your My Documents folder, but you can specify another directory if you want.
• If you provide an instance to the ApplyTo parameter, the function will attempt to create the logins on that instance if they don’t exist. Pass the -Verbose switch if you want to see which ones it creates and which ones it skips.
function Copy-SQLLogins{
[cmdletbinding()]
param([parameter(Mandatory=$true)][string]$source
,[string] $ApplyTo ,[string[]]$logins
,[string] $outputpath=([Environment]::GetFolderPath("MyDocuments"))) #Load assemblies [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null #create initial SMO object$smosource = new-object ('Microsoft.SqlServer.Management.Smo.Server') $source #Make sure we script out the SID$so = new-object microsoft.sqlserver.management.smo.scriptingoptions
$so.LoginSid =$true

#set output filename
$filename =$source.Replace('/','_') + '_' + (Get-Date -Format 'yyyyMMddHHmm') + '_logins.sql'
$outfile = Join-Path -Path$outputpath -ChildPath $filename #If no logins explicitly declared, assume all non-system logins if(!($logins)){
$logins = ($smosource.Logins | Where-Object {$_.IsSystemObject -eq$false}).Name.Trim()
}

foreach($loginname in$logins){
$login =$smosource.Logins[$loginname] #Script out the login, remove the "DISABLE" statement included by the .Script() method$lscript = $login.Script($so) | Where {$_ -notlike 'ALTER LOGIN*DISABLE'}$lscript = $lscript -join ' ' #If SQL Login, sort password, insert into script if($login.LoginType -eq 'SqlLogin'){

$sql = "SELECT convert(varbinary(256),password_hash) as hashedpass FROM sys.sql_logins where name='"+$loginname+"'"
$hashedpass = ($smosource.databases['tempdb'].ExecuteWithResults($sql)).Tables.hashedpass$passtring = ConvertTo-SQLHashString $hashedpass$rndpw = $lscript.Substring($lscript.IndexOf('PASSWORD'),$lscript.IndexOf(', SID')-$lscript.IndexOf('PASSWORD'))

$comment =$lscript.Substring($lscript.IndexOf('/*'),$lscript.IndexOf('*/')-$lscript.IndexOf('/*')+2)$lscript = $lscript.Replace($comment,'')
$lscript =$lscript.Replace($rndpw,"PASSWORD =$passtring HASHED")
}

$lscript | Out-File -Append -FilePath$outfile

#if ApplyTo is specified, execute the login creation on the ApplyTo instance
If($ApplyTo){$smotarget = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ApplyTo if(!($smotarget.logins.name -contains $loginname)){$smotarget.Databases['tempdb'].ExecuteNonQuery($lscript)$outmsg='Login ' + $login.name + ' created.' } else{$outmsg='Login ' + $login.name + ' skipped, already exists on target.' } Write-Verbose$outmsg
}
}
}

There is one additional piece to this, and that’s a helper function for converting the binary hash value to a string. It’s pretty simple, taking the value passed to it and doing some formatting magic to get a value for the SQL statement.

function ConvertTo-SQLHashString{
param([parameter(Mandatory=$true)]$binhash)

$outstring = '0x'$binhash | ForEach-Object {$outstring += ('{0:X}' -f$_).PadLeft(2, '0')}

return $outstring } Bring this all together and now we have a straightforward function we can call to help us manage our logins. Some examples: #Create a SQL Script in My Documents for all logins Copy-SQLLogins -source 'localhost' #Create a SQL Script in My documents for the passed logins Copy-SQLLogins -source 'localhost' -logins @('SHION\mike','test','pcuser') #Create a SQL Script in My documents for the passed logins and try and create these logins on the target SHION\ALBEDO Copy-SQLLogins -source 'localhost' -logins @('SHION\mike','test','pcuser') -ApplyTo 'SHION\ALBEDO' -Verbose Hopefully this helps you in your day to management of your instances. I’ve used this script for migrations and managing my Availability Group nodes. In my opinion, it’s a heck of a lot cleaner than sp_help_revlogin (a venerable tool in its own right, to be sure). As always, this along with my other scripts, can be found up on my GitHub repository ## #tsql2sday 63 – Security, Default Databases, and #Powershell This month’s T-SQL Tuesday is about how we manage database security, hosted by the ever effervescent Kenneth Fischer(@sqlstudent144). While I didn’t have a lot of time to work on this post, I wanted to share with you all a quick little nugget I used for one of my most recent presentations. So, default databases. This is a security bit that usually gets skipped over, unfortunately, but I think it’s a pretty important practice that is easy to implement and maintain. Let’s be honest, most of us leave the default database as ‘master’ when we create them. This then will usually lead us to accidentally doing work in the master, which potentially could be disastrous. Security is more than just locking people out or letting people in. While most of the situations with master can be be limited by reduced permissions, it doesn’t do anything to help or manage accounts that need to be able to work in that database. Security is not just about active policies, but also passive rules to reduce and prevent mistakes. A lot of damage can be prevented by directing people out of dangerous places by default. Fixing this is easy: ALTER LOGIN [foo\bar] WITH DEFAULT_DATABASE = [tempdb]; Also, we’d ideally create the logins with that default database set to begin with: CREATE LOGIN [foo\bar] FROM WINDOWS WITH DEFAULT_DATABASE=[tempdb]; However, this can be difficult to manage and keep up to date, especially since SQL Server doesn’t help by always defaulting to master. One thing that can be done is to have a way to set all your logins’ default databases to an appropriate database (I really like tempdb for this). So how about some Powershell? Using the SMO, we can use the DefaultDatabase property of the Login class for a quick and easy way to alter our default database: $login.DefaultDatabase = ‘tempdb’

Then, using the Logins collection in our Server object, we have an easy loop to go through an alter these default databases. Wrap that up in a fucntion and Bob’s your uncle. Well, he’s my uncle, not yours. Anyway, we’ll get a little something like this:

#load assemblies
$ErrorActionPreference = 'Inquire' function Set-SQLDefaultDatabases{ param([string[]]$Instances = 'localhost'
,[string]$defaultdb = 'tempdb') foreach($InstanceName in $Instances){$smosrv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $InstanceName if($smosrv.Databases.name -contains $defaultdb){ foreach($login in $smosrv.Logins){ Write-Verbose "Altering$login on $InstanceName to$defaultdb"
$login.DefaultDatabase =$defaultdb
}
}
else{
Write-Warning "Database $defaultdb is not valid on$InstanceName."
}
}
}

Set-SQLDefaultDatabases

So this simple function will set all the logins (SQL, Windows, and AD Groups) to the default database of choice. It’s fairly brute force, but gives you a good starting point. At this point, you should be able to see the value of writing it in this function format and use techniques I’ve blogged about earlier to set this enterprise wide.

Thanks out to Ken for hosting this month’s T-SQL Tuesday! Looking forward to reading the other blogs!

## Am I Alive? SQL Connectivity Checks with #Powershell

Inevitably, there will be the call from a developer or admin saying “I can’t connect to the MORDOR SQL Server!  Everything is terrible!  All is darkness and shadows!  HELP!”.  After explaining that one does not simply connect to MORDOR, I fire up SQL Server Management Studio and attempt to log in.  It’s not particularly onerous, but it takes time and can’t be particularly automated for more proactive connectivity checks.

The connectivity check, at the heart of it, isn’t really that complex.  Usually it’s just a case of connecting and running a simple select statement.  I’ve done this proactively in the past using a central management server and linked servers for all the instance in my environment.  While this works, it’s difficult to manage, automate, and isn’t very mobile.  Me being me, I turn to Powershell for a better way.

## Test-SQLConnection

The function here is pretty simple: Loop through a collection of instance names and try to query the server name and TempDB creation time.  If I get a result set, then my connection test is successful.  If it errors out, then no connection.  Essentially, the test is whether or not my return set has a StartupTime (TempDB creation date) or not.  So now I can pass single instance or collection and test them all with the one function.

function Test-SQLConnection{
param([parameter(mandatory=$true)][string[]]$Instances)

$return = @() foreach($InstanceName in $Instances){$row = New-Object –TypeName PSObject –Prop @{'InstanceName'=$InstanceName;'StartupTime'=$null}
try{
$check=Invoke-Sqlcmd -ServerInstance$InstanceName -Database TempDB -Query "SELECT @@SERVERNAME as Name,Create_Date FROM sys.databases WHERE name = 'TempDB'" -ErrorAction Stop -ConnectionTimeout 3
$row.InstanceName =$check.Name
$row.StartupTime =$check.Create_Date
}
catch{
#do nothing on the catch
}
finally{
$return +=$row
}
}
return $return } Test-SQLConnection -Instances 'localhost'  Why do I return the TempDB creation date? Well, a boolean value is easy enough to return, but I figure I could use more info to work with. After all, if data comes back, then I know the connectivity is working. So let’s do a little more. By returning the TempDB creation date, I can find out if my instance restarted recently (or if it’s been up for a really long time). This gives me a little more information to work with. Extending the functionality While calling this function adhoc is easy enough, the reason to put it in to Powershell is not so we can just have another way to test our connections. Automation is the key here. So what if we took this and combined it with another technique, getting a server collection from CMS? $CMS='SHION'
$servers=@((dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$CMS").Name)

$servers+=$cms
Test-SQLConnection -Instances $servers Using this, we can quickly test the connection of all the servers in our CMS. We can also check to see if any have started up recently, perhaps warning us of a restart of our SQL Services. From here, it’s not a tremendous leap to load it into a database table for regular health checks. Another tool for the toolbox What’s great about these functions is that once I write it, I have a new tool I can use. Initially I wrote this function so I can verify which servers in my active directory are actually SQL Servers (assuming the default port, of course). As we can see here, though, the function can be used for a variety of purposes. ## Growing Log Files Responsibly One of the commonly referenced blogs out there is Kimberly Tripp’s(@KimberlyLTrippseminal post on Virtual Log Files. If you haven’t read it yet, I highly recommend it. It provides a good description of one of those quirks with your log files and how you should manage it. Of course, while folks are familiar with this, it’s often not a practice that gets followed. Why? Because it’s hard to manage. Usually it’s just easier to set the autogrowth settings and let the log find its own size. This is where I hope I can help you out. Just like anything we do, if we need to execute it more than once it should be script. As such, I figured I’d share a T-SQL script I’ve put together for growing my log files out in 8GB chunks. I’ve used this quite a bit when managing my own systems for when I either need to expand a log file or I need to resize it after an accidental blowout. --Set variables declare @dbname sysname = 'test' ,@limit int = 32000 ,@filename sysname ,@currsize int ,@growth int ,@v_sql nvarchar(1000) --Get initial settings select @currsize = convert(int,floor(size/128.0)) ,@filename = name ,@growth = 8000 from sys.master_files where database_id = db_id(@dbname) and file_id = 2 --Grow file while @currsize < @limit begin select @growth = CASE WHEN @limit - @currsize < 8000 THEN @limit - @currsize ELSE 8000 END select @currsize += @growth select @v_sql = N'alter database '+@dbname+' modify file (name='+@filename+',size='+convert(nvarchar(10),@currsize)+'MB);' exec sp_executesql @v_sql end This is a fairly handy script and pretty easy to follow. I set the database name and limit, then let the loop keep adding 8000 MB chunks until I get to the size I want. No strange black voodoo magic, right? That part is next. Hopefully everyone reading this blog knows that I’m a big fan of Powershell. If not, well…I’m a big fan of Powershell. So I gave myself a little challenge to re-write that T-SQL script as a Powershell function to see how it would work. Here’s the end result: #load assemblies [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null$ErrorActionPreference = 'Inquire'

function Expand-SqlLogFile{
param(
[string]$InstanceName = 'localhost', [parameter(Mandatory=$true)][string] $DatabaseName, [parameter(Mandatory=$true)][int] $LogSizeMB) #Convert MB to KB (SMO works in KB) [int]$LogFileSize = $LogSizeMB*1024 #Set base information$srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $InstanceName$logfile = $srv.Databases[$DatabaseName].LogFiles[0]
$CurrSize =$logfile.Size

#grow file
while($CurrSize -lt$LogFileSize){
if(($LogFileSize -$CurrSize) -lt 8192000){$CurrSize =$LogFileSize}
else{$CurrSize += 8192000} logfile.size =$CurrSize
$logfile.Alter() } } #Call the function Expand-SqlLogFile -DatabaseName 'test' -LogSizeMB 35000  You’ll note that it’s about the same number of lines, does more or less the same thing. For those of you familiar with the SMO, you’ll also know that at behind the scenes all it’s doing is running T-SQL. So why write it this way? First, it’s an interesting challenge just to help understand Powershell and how to write it. It’s these kind of challenges that help me learn and expand my knowledge. The second, though, is to explore the differences between the methods. We know in the IT field, you can solve a problem in any number of ways. While I love Powershell, I wouldn’t use it everywhere. The only way to know where to draw the line, though, is to experiment and solve the same problem in different ways. ## DSC – The Custom SQL Resource Last post I gave the rundown of Desired State Configuration and how it worked, but I skipped over the “secret sauce” around SQL Server. Of course, that was the tease to get you to come back for this week’s post. Let’s dig in to the process and see how we can use DSC to install SQL Server without ever having to log in to the box. ## Installing the Resource The first thing to understand is that your custom resource will be contained within a Powershell module. This means it lives in your WindowsPowershell\Modules directory. The structure of the resource is specific and requires a couple pieces: • <Folder – Your Resource Name> • Your Resource Name.psd1 (Powershell Data file describing the module) • Folder – DSCResources (Contains all resources in the module • Folder – Your Resource Name (folder containing your specific custom resource) • Your Resource Name schema file (descibes the resource) • Your Resource Name script Now, if you’re using the Resource Designer toolkit, these will all get created for you. I highly recommend doing that, because you miss one piece and you’ll be bashing your head against this for a week or so. Ask me how I know. :) Another thing to setup is your execution policy. As this is a custom script, the local server needs to it is trustworthy. I set the execution policy to RemoteSigned for this (Set-ExecutionPolicy RemoteSigned). If you don’t do this, you’ll get an invisible failure, where your configuration will fail but you will have no feedback on the reason. Again, ask me how I know! ## Custom Resource When you actually create the resource script, you need three functions: • Get-TargetResource • Test-TargetResource • Set-TargetResource Get-TargetResource is the function that will return the resource you’re checking for. It returns a hash table to represent the key values of the resource. Test-TargetResource is a boolean check, returning true if the resource exists, false if it does not. Set-TargetResource does all the work, as it is the function that is called if the resource needs to be created. You can have other internal functions if you want to further modularize your process, but these three must exist for DSC to work. The internal operations must be written by you, but as long as the names and outputs are consistent you are set. The other key piece is not in the resource itself, but up in the data file describing your module. Within that file you need to have a GUID set for the module so that the DSC engine can reference it when it is installed on other machines. I tripped up on this many times, so be careful. ## cSqlInstall So let’s talk about the resource I constructed. As I said in my previous post, I was frustrated with the limitations of the Microsoft resource. The biggest limitation was the inability to use a config file, which is pretty much how I do SQL installs now. So that’s how I approached writing mine, leveraging the .ini file for most of the installation detail. The resource accepts the following parameters: • InstanceName – (required) The name of the instance you plan to install, MSSQLSERVER if you want the default. • InstallPath – (required) The UNC path for where the setup files are. • ConfigPath – (required) The UNC path for the config.ini file. • UpdateEnabled – Boolean, defaults to false. If true, the setup will check for and attempt to install available SPs and CUs. • UpdatePath – If UpdateEnabled is true, this is the path where the update files reside. • MixedMode – Boolean, defaults to false. If set to true, the install will set authentication to mixed mode and create ‘sa’ with a randomly generated password. I’ve tried to keep the parameters to a minimum and allow for customization to happen within the the config file. The biggest gap right now is that the service accounts and their passwords will be contained in plain text in that file. Ideally, you’d use managed service accounts to get around this, but I still am working on setting those up. We then look at the functions within the resource. Get-TargetResource should return a hash table, so what will return from this resource is the InstanceName, InstallPath, and ConfigPath. This is because these are the required parameters for the function, but really we only care about the InstanceName. To get that, it’s a simple check of the services on the target machine to find a service with the desired InstanceName. If we find it, it returns that name. If we don’t, it returns NULL. Test-TargetResource is a lot simpler. Since we want a boolean, all we do is use Get-TargetResource to get the resource hash table. Then we check the hash table’s InstanceName with the desired InstanceName. If they match, we return true and indicate the resource exists. If they don’t match, the resource doesn’t exist and we return false. Set-TargetResource is fairly straightforward. Using these arguments along with some default ones, the resource will build out a call to setup.exe. Once this string is built, the resource will invoke the setup.exe call just like any other unattended install. After the setup run is complete, the script finds the most recent Summary.txt file and checks it to see if the installation was successful. If the install was successful, it restarts the server and we’re done. Otherwise, it will report an error. ## Work To Be Done There’s still some other gaps in the code that I will be working on. First, it assumes the local machine account that the Local Configuration Manager runs under has permissions to the file shares for the SQL install. If your environment is more locked down, this could be a problem. There’s also the issue of logging that still needs to be addressed, because the current logging is not useful. All of these items (and others that come up) will be addressed eventually. I also plan to add other resources to this. I’d like to get one to handle service pack and cumulative updates outside of the SQL install so that you can use it to keep everything to the correct version as well. I’ve posted the code to my GitHub repository As with the other scripts there, it is a work in progress and can use a fair amount of improvement. If you have any suggestions or recommendations for this code, I’d love to hear of them. ## Desired State Configuration As a DBA, I’m always concerned with consistency in my environments. From maintenance to code deploy, I want things to be done the same way every single time. Usually this is a process question, making sure that you harness the power of check lists and repeatable steps. You then make it better by wrapping your process in scripts and leveraging other tools. When I go into a new shop, one of the first things I usually have to do is bolt down the server build process. Most environments will manually build servers, where an engineer goes in and manually installs the appropriate packages and features. Then a DBA will install SQL Server, adding maintenance jobs and deploying admin databases. Even with building scripts and sketching out steps, there will be a lot of spots where configurations can be missed and the process can breakdown. Fortunately, we now have a tool in Powershell that helps us solve these problems. You might have heard of Chef or Puppet. Well, now Powershell has its own answer for managing server configurations: Deired State Configuration or DSC. DSC is an engine included in Windows Management Framework 4 that allows folks like us to create declarative configurations for our servers which can then be acted on by our servers. This brings the next level of automation, infrastructure as code, to our servers. ## How does it work? The process for DSC relies on two things: Your configuration and your resources. You create a configuration, which specifies the resources you want (or don’t want). You then use DSC via Powershell to deploy the configuration to a target server, which then runs the configuration through a Local Configuration Manager (LCM) to check those resources. If those resources are present, the LCM takes note and moves on. If the resources is not present as declared in the configuration, the LCM will then attempt to install or configure the resource as specified in the configuration file. Simple, eh? It’s meant to be that way. The idea is to have an intuitive way to deploy servers in a standardized method, whether it’s 1 or 100 (or 1000 for that matter). It’s also meant to be flexible. DSC ships with some basic resources (things like File, Windows Feature, and Registry Key), but because it’s built in Powershell you have the ability to create your own resources. This means the sky’s the limit with what you can do. ## What do I need? Let’s dig a little more into the detail of how DSC works. First off, if we want to use DSC, we need a couple things: • Windows Management Framework 4.0 on all machines involved • Permissions to run custom scripts on the targets (if we’re using custom resources, more on this later) • Custom resources need to be deployed to the target ahead of deploying the configuration Note I call out some requirements around custom resources. These are scripts you write yourself. We’ll talk about those in a bit, just file these bits away for later reference. Now, the configuration. This is a configuration I created for deploying a base installation of SQL Server, so the resources are designed around that. Here’s the script: Configuration SQLServer{ param([string[]]$ComputerName)

Import-DscResource -Module cSQLResources

Node $ComputerName { File DataDir{ DestinationPath = 'C:\DBFiles\Data' Type = 'Directory' Ensure = 'Present' } File LogDir{ DestinationPath = 'C:\DBFiles\Log' Type = 'Directory' Ensure = 'Present' } File TempDBDir{ DestinationPath = 'C:\DBFiles\TempDB' Type = 'Directory' Ensure = 'Present' } WindowsFeature NETCore{ Name = 'NET-Framework-Core' Ensure = 'Present' IncludeAllSubFeature =$true
Source = 'D:\sources\sxs'
}

cSQLInstall SQLInstall{
InstanceName = 'MSSQLSERVER'
InstallPath = '\\HIKARU\InstallFiles\SQL2014'
ConfigPath = '\\HIKARU\InstallFiles\SQL2014\SQL2014_Core_DSC.ini'
UpdateEnabled = $true UpdatePath = '\\HIKARU\InstallFiles\SQL2014\Updates' DependsOn = @("[File]DataDir","[File]LogDir","[File]TempDBDir","[WindowsFeature]NETCore") } } } SQLServer -ComputerName MISA  This looks like (and is) a Powershell function, just a special one using the ‘Configuration’ key word. When you call the function, it will create a special file for the Node(s) declared within the configuration, a Managed Object Format (.mof) file. This is a standardized file type that defines our configuration. Note, this file is not Windows specific, as the design for DSC is to ultimately be used with non-Windows OS machines and hardware. It’s the .mof that will be deployed to our target machines and acted upon by the LCM. Next up in the file, the Node. This is the target for our configuration. You’ll see that in the script, it is parameterized as an array, meaning we can run the script for as many different targets as we want, we just have to pass the array of machine names we want. We could also have multiple nodes within the configuration script, if we wanted this configuration to have different types of targets. Within the Node section, we have our resource declarations. This is the meat, the pieces of code that identify what needs to be configured. The nice thing is reading them is fairly intuitive. Let’s look first at the File resource. These three declarations are for my default directories. I have to declare specify that it is a directory (the Type), the path (the DestinationPath), and that is must exist (Ensure = Present). As an aside, I could get a lot more involved with the File resource, copying things from a net share down to the target if I wanted, things like config files or web source directories. The Windows Feature resource is also easy to understand. Using this, I can make sure certain Windows features exist on the target. Since I’m using this particular configuration to install SQL Server, I’ve declared that I want the Net-Framework-Core feature installed. This is a prerequisite for installing SQL Server. Finally, I have the cSQLInstall resource. I’m going to save the explanation of this detailed custom resource for the next blog post, but I want to call attention to the DependsOn argument. With this, we can declare prerequisites for our resources. This give us some additional resilience for our install, allowing us to create some precedence within the configuration. ## Where do I run it from? Once you have the configuration written, you simply need to run it. It will create a directory named after your configuration and contain a .mof file for each node within it. The next step is to use the Start-DscConfiguration cmdlet and call the path where all our files live. After that, a Powershell background job kicks off and DSC handles the rest. ## Any not cool parts? So not everything is unicorns and rainbows with DSC. First off, it’s a stone cold female canine to debug, because it’s difficult to capture any logging around it. Many times I would try and run something and it would fail before it even entered my script. Most of this was around my custom stuff, but it was extremely frustrating. As for the custom scripts, DSC is still fairly immature. Microsoft has some resource kits out there, but most of the resources in there are pretty weak. The SQL resource provided has several gaps, such as being designed specifically for SQL 2012 and not being able to use .ini files. So you’re probably going to write resources on your own, which has a LOT of gotchas. You’re going to want to start with the Resource Designer Tool, which will solve most of the gotchas for you. Also, since it’s immature, there’s not a lot written about it. Especially for SQL Server. Powershell.org and MSDN are going to be your best bets for help, but this is a brave new world. Be prepared to walk a fair amount of the way on your own. If you want to start, go with this book from Don Jones. Infrastructure as code is the future. DSC gives us intuitive tools with a straightforward interface (once your resources are written, of course) to make our environments more consistent and easier to manage. I’m super excited about this feature and will be blogging more about it in the weeks to come. Definitely stay tuned for next week, where I’ll walk through my custom SQL Server resource (and tell you where you can get the code yourself). Also, hit me up if you’ve got questions or any experiences of your own around DSC, I’d love to hear about them. ## RestoreAutomation #Powershell Module When you become involved with the SQL community, you see a lot of great tools contributed to make life easier for the rest of us. The function of these tools range greatly, from helping to analyze your system state, collect data about your enterprise, or performing regular maintenance. I’ve used many of these tools over the years and can’t imagine being a DBA without them. Well, now it’s time to give back. A little over two years ago, I posted a Powershell script for building database restores. It has been very handy and I’ve used it a lot over the years. However, it always felt like it could use some polish. I began work a few months ago to add that polish and was able to debut this output at SQL Saturday Sacramento a few weeks ago. Now I want to share that same output with you here on my blog. ## RestoreAutomation.psm1 I’ve compiled the previous Powershell script into a module, making it easily transportable and allowing DBAs to use it more effectively as a tool. No messing with script files, all you need to do to use it is download the module file and then import it into your Powershell session. The file is now available on my new GitHub repository and can be accessed via the following links: To install the module, follow these steps: 1. Open one of your default Powershell module locations ($env:PSModulePath).  I recommend your user specific one.
2. Create a folder named RestoreAutomation in that locatoin
3. Place the RestoreAutomation.psm1 file in the new folder.

Full Microsoft install instructions can be found here.

To load the module once you’ve installed it, just open a Powershell session and enter Import-Module RestoreAutomation.  After that, you can see the available functions by using Get-Command -Module RestoreAutomation, which will provide the following list:

CommandType     Name                ModuleName
-----------     ----                ----------
Function        Get-DBCCCheckDB     RestoreAutomation
Function        Get-RestoreObject   RestoreAutomation
Function        New-Restore         RestoreAutomation
Function        Sync-DBUsers        RestoreAutomation

## Using the module

While five functions are listed, two of them are internal and used by the core restore process to generate the necessary code.  I’ll cover the primary three functions here, but I have made full use of Powershell’s comment based help, so you can use Get-Help on these functions at any time to get information on how to use them.

New-Restore
This is the core functionality that was created from my initial script.  When you run this function, it will create a .sql script in your Documents folder to restore the associated files.  The function takes a directory path and then search that directory path for all backup files within it, ordering them appropriately (latest full, then latest differential, then the right sequence of transaction logs).

You’ll see that the function has a lot of parameters.  The only two required are:

• dir – The directory where the necessary backup files live
• server – A SQL Server instance where the script generation commands will be run (but not necessarily restored to)

I will not cover the optional parameters in detail here, but review Get-Help New-Restore -full for a full listing of the parameters and what they do.

The function makes the following assumptions:

• All the backup files in the directory belong to one database only.
• Backup file types are defined by extension (.bak for full, .dff for differential, and .trn for transaction log).
• No striped backup files, meaning a database backup will be contained on only one file.
• No backup sets, meaning a backup file only contains a single database backup.

To use the function, simply get a directory of backup files and run a command similar to:

New-Restore -dir “X:\backups\db” -server “localhost”

Then, look in your Documents folder for a restore_db.sql file.  This will be your database restore script.

If you want to test drive it yourself using a dummy database, I’ve provided a full demo script and files for download.

Sync-DBUsers
This function I added to help me with database migrations.  Often, I’ll migrate a database via backup and restore.  Afterwards I always need to clean up the orphan logins.  This function is a wrapper for SQL queries that I use to do this, providing me an easy to use interface for managing the process.

The function accepts only two parameters:

• server – The SQL instance that the database resides on
• database – Database that contains users you wish to correct

To run the function, just call your server and database name:

Sync-DBUsers -server “localhost” -database “restoredemo”

The function will then find all orphaned users and correct them.  Any orphans it can not find corresponding logins for, it will return as an output list for review and correction.

Get-DBCCCheckDB
When building out my automated restore process, I always wanted to do a DBCC CHECKDB on the restored database to validate everything was ok.  So I wrote this function in order to give me a manageable interface for the process.  The function will then run a DBCC CHECKDB with PHYSICAL_ONLY on my target database and return the results.

The function has three parameters:

• server – The SQL instance that the database resides on
• database – Database that you want to check
• Full – Switch parameter.  If used, the function will execute a full DBCC CHECKDB.  If omitted, it will only perform a check with PHYSICAL_ONLY

To use the function, call the server and database name:

Get-DBCCCheckDB -server “localhost” -database “restoredemo”

The output will be a data table of the check results.

## Only the beginning

Hopefully this module can help you as much as it has helped me.  The point is to use this as a tool to facilitate other tasks.  Many of these tasks I will provide examples of over the next month or so, to demonstrate how you can leverage this in your own environment.

I already have a list of enhancements that I plan to add.  I expect that as folks out there review it and play with it, I’ll get more.  Please do not hesitate to post suggestions and comments to the GitHub repository.  The idea, as with many of these other tools, is to build a reliable offering that other DBAs can use in their day to day jobs to make things easier and allow them to work on more important tasks and projects.

## The Moving Van

A problem DBAs commonly face is moving database files around, especially when we’re trying to manage space on a drive.  I know that I’ve had to do it and, when you have a database instance with hundreds of databases, you need an automated way to manage this.  Recently, someone on DBA Stackexchange had this specific problem and I was able to provide a Powershell script that I’ve used in the past to manage this problem.  And when I haven’t blogged for a while, a good way back into it is to share a Powershell script.  Mostly because it’s easy.  :)

First, let me talk about a pattern I commonly use.  A lot of times, I could write a single script to take an action, but instead I build a script to write a script.  The main reason I do this is that it’s very hard, especially with many of our administrative tasks, to write a one-size-fits-all process.  So instead, I have a tool that gets me 90% of the way which I can then tweak or alter that script to address that final 10% to fit that specific need.

That’s what this script is doing.  It doesn’t perform the actual file moves itself, but writes out a script that will execute those files moves.  It’s fairly generic, creating a script that moves all the database files from their current location (wherever that may be) to two common directories: data and log. It’s rare that you would move all your database files in this fashion, but you can edit the generated script to suit your specific situation.

This script makes use of the techniques detailed here by Ben Miller(@dbaduck) with one significant exception. I’ve found that the .Offline() method for databases is very unreliable because you’re basically executing your normal “ALTER DATABASE [foo] SET OFFLINE;”. The main problem with this command is it has to wait for all connections to close gracefully before it can proceed, something that doesn’t happen to frequently. I prefer to use “ALTER DATABASE [foo] SET OFFLINE WITH ROLLBACK IMMEDIATE;” because it forces all connections to close and open transactions to rollback, no waiting involved. Since the SMO methods don’t have an option for that,  I call the SQL using the Invoke-SQLCmd cmdlet. I find this is a much cleaner option for my script.

Specifically, the script accepts 4 parameters:

• $newdata – New data file path •$newlog – New log file path

• $instance – SQL instance you want to move files for (defaults to local) •$outputfile – Output file name (defaults to Documents, FileMover.ps1)

When you run it, it will spit out a FileMover.ps1 script to you Documents folder (though, using that last parameter, you can change the output location and name).  This FileMover.ps1 will be the script you can then edit to suit your specific needs.  The script is below, but you can also download it here.

<#
.SYNOPSIS
Builds a Powershell script for moving many database files from one
set of directories to another.
.DESCRIPTION
Used to generate a Powershell script for moving many database files
from one location to another.  This will typically be used with a
large number of databases that need to be relocated.  Caveats for
this script include:
-Destination for all files will be the same.
-User that runs the script must have access to source and destination locations
-This uses the 2008 R2 version of the SMO.

The script generates a FileMover.ps1 script (by default in My
Documents).  The reason for generating a separate script is so
specific configurations can be edited/updated before actually
execyting the move.

.PARAMETER <paramName>
instance - Instance owning the databases to be moved
newdata - New data file location, no trailing '\'.  example: "C:\DBFiles\Data"
newlog - New log file location, no trailing '\'.  example: "C:\DBFiles\Log"
$outputfile - Full path and name of output file. By default, FileMover.ps1 in My Documents. .EXAMPLE .\Build-FileMover.ps1 -newdata "C:\DBFiles\Data" -newlog "C:\DBFiles\Log" #> param([parameter(Mandatory=$true)][string] $newdata, [parameter(Mandatory=$true)][string] $newlog, [string]$instance="localhost",
[string] $outputfile=([Environment]::GetFolderPath("MyDocuments"))+"\FileMover.ps1") #load SMO Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null #Create server object and output filename$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $instance #get your databases$db_list=$server.Databases #build initial script components "Add-PSSnapin SqlServerCmdletSnapin100" >$outputfile
"Add-PSSnapin SqlServerProviderSnapin100" >> $outputfile "[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') "$instance" | out-null" >> $outputfile "$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server " >> $outputfile foreach($db_build in $db_list) { #only process user databases if(!($db_build.IsSystemObject))
{
#script out all the file moves
"#----------------------------------------------------------------------" >> $outputfile "$db=$server.Databases[""+$db_build.Name+""]" >> $outputfile$dbchange = @()
$robocpy =@() foreach ($fg in $db_build.Filegroups) { foreach($file in $fg.Files) {$shortfile=$file.Filename.Substring($file.Filename.LastIndexOf('\')+1)
$oldloc=$file.Filename.Substring(0,$file.Filename.LastIndexOf('\'))$dbchange+="$db.FileGroups[""+$fg.Name+""].Files[""+$file.Name+""].Filename="$newdata\"+$shortfile+"""$robocpy+="ROBOCOPY "$oldloc" "$newdata" $shortfile /copyall /mov" } } foreach($logfile in $db_build.LogFiles) {$shortfile=$logfile.Filename.Substring($logfile.Filename.LastIndexOf('\')+1)
$oldloc=$logfile.Filename.Substring(0,$logfile.Filename.LastIndexOf('\'))$dbchange+="$db.LogFiles[""+$logfile.Name+""].Filename="$newlog\"+$shortfile+"""
$robocpy+="ROBOCOPY "$oldloc" "$newlog"$shortfile"
}

$dbchange+="$db.Alter()"
$dbchange+="Invoke-Sqlcmd -Query "ALTER DATABASE ["+$db_build.Name+"] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -ServerInstance "$instance" -Database "master""$dbchange >> $outputfile$robocpy >> $outputfile "Invoke-Sqlcmd -Query "ALTER DATABASE ["+$db_build.Name+"] SET ONLINE;" -ServerInstance "$instance" -Database "master"" >>$outputfile
}
}


## The Hierarchy of Monitoring Needs

For those not aware, I’ve been selected as a presenter at this year’s PASS Summit in Charlotte, NC.  I’m deeply honored by the selection and look forward to the experience.  The topic is something I’ve been working on for sometime now:  How should administrators approach monitoring?  This seems like a simple question, but go Bingle “SQL Server Monitoring” and see what you get.  Go ahead, I’ll wait.

The results are either tools or metrics.  While these are useful, it highlights a significant gap out there: a methodology.  The closest you’ll find out there is Adam Machanic’s (@adammachanic) Ten Commandments Of SQL Server Monitoring (which applies to all monitoring, really, but I digress).  The goal with my PASS presentation is to provide a “how to” plan for creating your overall monitoring strategy.

How does this methodology work?  Several years ago I was introduced to Maslow’s Hierarchy of Needs.  It’s a structured view of what humans need, built around a tiered view where a person addresses each level before they are concerned with the next set of needs.  The template carries well, such that Brent Ozar(@brento) even wrote about his own Hierarchy of Database Needs a while back.

So what’s my approach?  To get the detail, you’ll need to come to my PASS session, but I’ll let the overview whet your appetite:

Survival – There are some factors database administrators must be concerned with.  As guardians of the data, we have to ensure three things about our systems before we can worry about anything else:

• Reliable backups – You hear this ad nauseum, but it’s true.  Without backups, we’re crossing the high wire without a net.  Note, also, that these are “reliable”.  You need to know more than your backups are happening, but can you find them and are they usable.
• Service Availability – Is SQL Server up?  Is it responding to queries?  While this seems like a “duh” moment, if our users can’t access their data, then we’re in almost as much trouble as we were if we can’t recover from a backup.
• Space – This is not long range capacity planning that we will worry about later down the road, but a validation that we’re not “red lined” for space.  Because if we run out of space, we affect availability and use of our databases.

Knowledge  – In order to identify what to monitor, we need to know what to be monitoring for, otherwise we’re wandering in the mountains without a map.  We establish this by working with the business to create our Service Level Agreements.  I wrote about SLAs a year ago and my approach is still the same:

• Availability – Users want to know their data is queryable, protected, and secure.  We need to define our customer’s expectations for high availability, disaster protection, maintenance windows, and security.
• Performance – Users want their data fast, but we need to put definition around this.  What defines a query as to slow?  How long can things be blocked? What sort of resource usage is allowed within our environments?
• Support – While this only partially affects our monitoring, our users want to know who’s “throat to choke” if there’s an issue.  For monitoring, that defines escalation patterns and critical/non-critical alerts.

Direction – Metrics and whether they’re to high or to low are commonly what DBAs think of when they discuss monitoring.  Are my batches/second to high?  What’s my I/O throughput?  Page Life Expectancy? Wait types? The list goes on and on.  However, like many things, DBAs want to put the cart before the horse and just start looking at stats.  By dealing with SLAs first, we’ll be better equipped to monitor what’s important to the business rather than what’s important to us.

Actualization – After we know what to look for, it’s time to decide how we look for it, and this leads us into the discussion of tools.  The problem is, there are a load of tools out there you can use.  Do you go with a specialized third product?  Maybe some larger, more generic system that integrates with other parts of the enterprise?  Do you write your own, using what’s freely available and paying the cost in time to build?  Whatever the case, having a list of metric needs equips us with the knowledge necessary to identify what tool set best suits our situation.

Understanding – One of the trickiest parts of monitoring is knowing what’s a problem and what is just normal behavior.  Once our metrics are defined and our tools are set up, we need to collect baselines and map trends to understand the health of our enterprise.  While our SLAs can give us a starting point, we need to study our environments over time.

Refinement – Once everything is in place, we can start the cycle of fine tuning our monitoring.  Systems and processes change over time.  New storage will need to be added and new applications brought on line.  With we are able to periodically review our trends and anticipate future needs.  Managing our metrics based on these changes and adapting our monitoring is an ongoing process and is never set in stone.

Just so you know, this is just the 30,000 foot view that gives administrators the basic map.   At Summit I’ll discuss the territory in a little more detail as well as some scripts, templates, and additional information to help you with your own monitoring needs.  I’m very excited to be sharing this at Summit and hope that you’ll all join me for it.

## Server Inventories

Ok, I’ve been promising this to people for a while, time to get at it.  Brace yourself for a long one.

As enterprise DBA’s, we usually have many servers to manage.  Whether it’s around ten, a hundred, or a thousand (or more!), tracking this manually is a bear.  However, there are still many shops maintain convoluted spreadsheets and other manual documentation to track their inventory.  Manual sucks.  As John Sansom(@SQLBrit) says, the best DBAs automate everything, including their server inventories.

There’s a lot of approaches and third party tools you can use for your own environment.  In my mind, I’m totally cool with spending the money on a third party tool because of the depth they can provide in my tracking and monitoring.  A lot of times, though, we might not have access to those tools or we may need more information than those tools provide.  It’s at this point we sit down at our keyboard, crack our fingers, and start banging out some code.  Of course, the code I started banging out was Powershell (shocking, I know!).

I didn’t start from scratch, though.  When putting my own script together (which we’ll get to later), I found two great resources that got me about 80% of the way:

These are both great resources that will get you started, but I needed a little more.  The biggest challenge I had was a need to poll cluster information and collect SQL instance information separate from my machine information.  I also wanted to do some consolidation of my data sets.  So while I borrowed heavily from both Allen and Colleen, but then molded it to my own purposes.

## Process

Before we dig into the code, I first want to touch on the collection process and how I handle it.  It’s not complex, but code doesn’t make sense until you understand the “why” of the different components.

What I’m collecting

• SQL Instance information – Whether it’s an instance living on a cluster node, a physical stand alone, or a VM host, I want the SQL Instance without really caring about the machine it lives on.  At least not meshed in directly with the machine, though I DO collect the physical host name so I can identify which machine information I need.

• Physical Machine information – Since I’m not tying the machine info directly with the SQL Instance collection, I want the physical information for the servers my instances live on.  This is where I get everything that isn’t tied directly to the SQL Instance.

How I’m collecting it

• I’ve got 4 tables, 2 live tables and 2 stage tables, that I use for the collection.  Nothing out of the ordinary, but there’s an instance table with a stage table and a machine table with a stage table.

• I use a Powershell script to perform the collection itself.  It uses instance table as its primary driver, where the idea is that I provide the initial population of instance names to track into the table and the inventory process will fully populate the rest of the information.

• When the Powershell scripts complete, there is a stored procedure it executes to load data from the stage tables into the live tables.  The load is simple, where the instance table is updated with data from the stage and the machine information is deleted/replaced.

• The Powershell script is run by a SQL Agent job running under a specific monitoring Active Directory account created as a credential in SQL Server.  The gotcha here is that the monitoring account needs domain access to the clusters and machines it’s going to be querying.

## The Code

I won’t list the full scripts here, but you can download and review them here:

Let’s first talk about the dependencies:

• The SMO, so you’re going to need to have either your script or your profile load the 2008 snap-ins or import the 2012 sqlps module.

• Chad Miller’s Out-DataTable and Write-DataTable (I combined them into one file, DataTables.ps1).

• The FailOverClusters module, part of the Remote Admin pack if you’re not running the code on a machine with the Windows Server OS, part of the Fail Over Cluster components if you are.

There are two key functions I use, Get-Instance to gather the SQL instance information and Get-Machine to gather the machine information:

#Collects and returns SQL Instance information
function Get-Instance([string]$instcoll,[int]$id,[string]$name) { try {$smo = new-object ('Microsoft.SqlServer.Management.Smo.Server') $name$sname = $smo.NetName$iname = $smo.InstanceName if($iname.Length -eq 0 -or $iname -eq$null) { $iname = "MSSQLSERVER" }$managedcomp = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $sname$output = New-Object System.Object

$port =$managedcomp.ServerInstances[$iname].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value$ip = (Test-Connection $sname -count 1).IPV4Address.ToString()$output | Add-Member -type NoteProperty -name InstanceId -value $id$output | Add-Member -type NoteProperty -name SQLVersion -value $smo.VersionString$output | Add-Member -type NoteProperty -name SQLVersionDesc -value $smo.ProductLevel$output | Add-Member -type NoteProperty -name SQLEdition -value $smo.Edition$output | Add-Member -type NoteProperty -name MemoryMinMB -value $smo.Configuration.MinServerMemory.RunValue$output | Add-Member -type NoteProperty -name MemoryMaxMB -value $smo.Configuration.MaxServerMemory.RunValue$output | Add-Member -type NoteProperty -name MAXDOPVal -value $smo.Configuration.MaxDegreeOfParallelism.RunValue$output | Add-Member -type NoteProperty -name IP -value $ip$output | Add-Member -type NoteProperty -name Port -value $port$output | Add-Member -type NoteProperty -name PhysicalHost -value $smo.ComputerNamePhysicalNetBIOS return$output
}
catch
{
write-host "Error collecting $name" return$null
}
}#Get-Instance

#Get host machine information via WMI
function Get-Machine([string]$name,[string]$clst)
{
try
{
$comp = gwmi Win32_ComputerSystem -Computer$name | select Model,Manufacturer,TotalPhysicalMemory
$proc = gwmi Win32_Processor -Computer$name | select NumberOfLogicalProcessors,MaxClockSpeed
$os = gwmi Win32_OperatingSystem -Computer$name | select OSArchitecture,Name,Version,ServicePackMajorVersion,ServicePackMinorVersion

$output = New-Object System.Object$output | Add-Member -type NoteProperty -name MachineName -value $name$output | Add-Member -type NoteProperty -name Model -value $comp.Model$output | Add-Member -type NoteProperty -name Manufacturer -value $comp.Manufacturer$output | Add-Member -type NoteProperty -name Architechture -value $os.OSArchitecture$output | Add-Member -type NoteProperty -name PhysicalCPUs -value $(if(!$proc.Length){"1"}else{$proc.Length})$output | Add-Member -type NoteProperty -name LogicalCPUs -value ($proc | Measure-Object NumberOfLogicalProcessors -sum).Sum$output | Add-Member -type NoteProperty -name CPUSpeed -value ($proc | Measure-Object MaxClockSpeed -max).Maximum$output | Add-Member -type NoteProperty -name MaxMemory -value ($comp.TotalPhysicalMemory/1MB)$output | Add-Member -type NoteProperty -name OSName -value $os.name.split("|")[0]$output | Add-Member -type NoteProperty -name OsVersion -value $os.Version$SPMaj = $os.ServicePackMajorVersion$SPMin = $os.ServicePackMinorVersion$output | Add-Member -type NoteProperty -name SPVersion -value "$SPMaj.$SPMin"
$output | Add-Member -type NoteProperty -name Cluster -value$clst

return $output } catch { write-host "Error collecting$name"
return \$null
}
}#Get-Machine
`

Both leverage the SMO and the WMI to get relevant info.  I’ll let you sort through the individual elements I’m pulling, it’s fairly standard config info.  Data gathering, because I think in terms of tables, is a collection of objects that are essentially rows.  I append each new object with all the appropriate properties to a larger collection, then use Write-Datatable to push that data directly into the related stage table.

Look at the entire script for my full process.  I’ve been pretty happy with it and it’s been chugging away in my environments now for about 8-9 months without a whole lot of interaction from me.  If we stand up a new server, then I just pop that name in the instance table, run the job, and everything gets updated.  What made me most proud was that we had a recent meeting with a new VP and pulled up the SSRS report built off of this information to give him an idea of what our environments.  His comment was “This is the most documentation I’ve seen since I’ve got here.”  The beauty is that it was provided without a whole lot of manual maintenance on my part.

This is very much an evolving work on my part, but hopefully gives you some insight into how I manage my environments.  I’ve still got lots of ideas on improvements, including automatic population (or other use) of Central Management Server.  If you have any ideas on how this can be improved, I’d love to hear them.