A Fine Slice Of SQL Server
Rob Sewell shows how to use dbatools to create test user accounts quickly:
Of course we can use any source for our users – a database, an excel file, Active Directory or even just type them in.
We can use the Add-SQLLogin command from the sqlserver module to add our users as SQL Logins, but at present we cannot add them as database users and assign them to a role.
Rob includes a demo script as well, thereby making it even easier.
Comments closedRequirements
- Automation – Complete autopilot, no human interaction.
- Report job status – Accurate reporting in the event the job failed, the CHECKDB failed or the restore failed.
Solution
- Use dbaltools cmdlets for restore and CHECKDB operations
- Use SQL Agent Job automation, logging and alerting
So let’s walk through this implementation together.
You won’t get a turnkey solution from this blog post, but you will get a good process to follow.
Comments closedShane O’Neill explains what the $error variable does:
Now ignoring the fact that you already know what is wrong, this tells me that there is either something wrong with the
$Databasevariable, the$sqlvariable or the syntax statement. Maybe even something else though!
This is not helpful and I’m going to have a bad time.I encountered this lately and thanks to Chrissy LeMaire ( b | t ), I was introduced to the
$errorvariable.
Shane also has an interesting side note around error colors.
Comments closedJana Sattainathan shows how easy it is to change the colors for Powershell warnings and errors:
How could such a nice tool have such a terrible choice of colors for errors and warnings? I am talking about the ISE and the unreadable contrast of colors.
You can fix it. Open and update your profile by running this from your ISE (which opens your profile in NotePad)
It’s Powershell, so you can do it in a couple lines.
Comments closedRob Sewell has an interesting post on cross-platform configuration using dbatools in Powershell:
This weekend I set up some SQL vNext virtual machines, two on Windows and one on Linux so that I could test some scenarios and build an availability group.
IMPORTANT NOTE :- The names of dbatools commands with a Sql prefix WILL CHANGE in a later release of dbatools. dbatools will use Dba throughout in the future as the sqlserver PowerShell module uses the Sql prefix
I used PowerShell version 5.1.14393.693 and SQL Server vNext CTP 1.3 running on Windows Server 2016 and Ubuntu 16.04 in this blog post
There’s some fancy footwork in this post; if you’re looking for ways to compare instance configurations (specifically, sp_configure settings), check it out.
Comments closedThis is a function that activates a tab based on a partial search string that you type in the CLI portion of the ISE. Among the numerous tabs you have open in the ISE, to activate a tab that has the word “register”, you would just type in
popt registerThat’s it. The tab that has the word “register” will become active.
Why “popt” instead of a standard name?
“popt” is the alias you can create for the real “search and activate function” Pop-FileInISETab. It stands for “poptab”. You don’t want to trade one problem with another by having to type that long name, so, there is functionality to optionally create an alias named “popt” for the function.
At first I said to myself, “Why would anybody have this many tabs open?” Then I looked at my instance of SQL Server Management Studio. Then I decided I wanted this for SSMS.
Comments closedSander Stad has made changes to his Export-DMVInformation Powershell module:
Last Friday I had the chance to show the Export-DMVInformation module to the Dutch Powershell user group. After the presentation I got a couple of suggestions and wanted to put them in place them into the module.
Changes:
Possibility to execute the module using the pipeline
Get all the databases in one statement by assigning the value “ALL” to the database parameter.
Replaced messages with verbose
Read on for more information, including where you can get the module and its Export-DMVInformation cmdlet.
Comments closedThis code was run on PowerShell version 5 and will not run on PowerShell version 3 or earlier as it uses the where method
I put all of our jobs that I required on the estate into a variable called $Jobs. (You will need to fill the $Servers variable with the names of your instances, maybe from a database or CMS or a text file
Click through for the script and line-by-line explanation.
Comments closedJana Sattainathan gives a few options for getting around hard-coded paths in a Powershell script:
What if the KillerApp’s home folder suddenly moves?
Now, how do you make your app work with all its scripts without having to change code if you move it to a different folder?
You could now change the initial script that that dot-sources all the functions to alter the path and you are all set. This is still not ideal because you have to make a change when the location changed.
Click through for several options, including PSDrives and even automatically dot-sourcing Powershell scripts in the current and all child directories.
Comments closedWe will use the sqlserver module, so you will need to have installed the latest version of SSMS from https://sqlps.io/dl
This code was run using PowerShell version 5 and will not work on Powershell version 3 or lower as it uses the where method.
Lets grab all of our jobs on the estate. (You will need to fill the $Servers variable with the names of your instances, maybe from a database or CMS or a text file)
One oddity with SQL Agent jobs is that you absolutely need to call the Alter() method at the end or else the changes will not actually take effect.
Comments closed