I have always been extremely proud to be a Cloud and Datacenter Management MVP, and lucky enough to be involved with both the PowerShell community as well as the Data Platform community.
Today, July 1st is the date that many MVPs receive their renewal email to let them know that they have been awarded for another year. There is a lot of F5’ing and frequent checking of emails and “Have you heard yet?” DMs going around.
When I received the news, I was using Azure DevOps to run PowerShell and Terraform to build an Azure SQL Elastic Pool (yes, I will write a blog post about it!). I love technology and within my work, like many people, I work across many different disciplines. Azure, Azure DevOps, SQL Server and Microsoft Data Platform products are the main focus of my time.
I didn’t notice the significance of the information.
I was pleased as punch to be renewed again, proud that what I do is recognised by Microsoft, honoured to spend another year as an MVP. Then my friends pointed out the big news that I had missed.
I enjoying maintaining open source GitHub repositories such as dbachecks and ADSNotebook. I absolutely love it when people add more functionality to them.
To collaborate with a repository in GitHub you need to follow these steps
Fork the repository into your own GitHub
Clone the repository to your local machine
Create a new branch for your changes
Make some changes and commit them with useful messages
Push the changes to your repository
Create a Pull Request from your repository back to the original one
You will need to have git.exe available which you can download and install from https://git-scm.com/downloads if required
Fork the repository into your own GitHub
A fork is a copy of the original repository. This allows you to make changes without affecting the original project. It does not get updated when the original project gets updated (We will talk about that in the next post) This enables you to code a new feature or a bug fix, test it locally and make sure it is working.
Let’s take dbachecks as our example. Start by going to the project in GiHub. In this case the URL is https://github.com/sqlcollaborative/dbachecks You will see a Fork button at the top right of the page
When you click the button the repository is copied into your own GitHub account
Forking the repository has created a remote repository stored on the GitHub servers. Now that the repository has been forked you need to clone it to your local machine to create a local repository so that you can start coding your amazing fix. When you have finished you can then sync it back to your remote repository ready for a Pull Request back to the original repository.
In your browser, at your remote repository that you just created (https://github.com/YOURGITHUBUSERNAME/NameOfRepository if you have closed the page) click on Clone or Download and then the icon to the right to copy the url
You can clone your repository in VS Code or Azure Data Studio by clicking F1 or CTRL + SHIFT + P in Windows or Linux and ⇧⌘P or F1 on a Mac
then start typing clone until you see Git:Clone and press enter or click
Paste in the URL that you just copied and click enter. A dialog will open asking you to select a folder. This is the parent directory where your local repository will be created. The clone will create a directory for your repository so you do not need to. I suggest that you use a folder called GitHub or something similar to place all of the repositories that you are going to clone and create.
When it has finished it will ask you if you wish to open the repository
if you click Open it will close anything that you have already got opened and open the folder. If you click Add to Workspace it will add the folder to the workspace and leave everything you already had open as it was and surprisingly clicking Open in New Window will open the folder in a new instance of Visual Studio Code or Azure Data Studio!
and you will also be able to see the local repository files on your computer
You can clone the repository at the command line if you wish by navigating to your local GitHub directory and running git clone TheURLYouCopied
Now your local repository has been created, it’s time to do your magic coding.
Create a new branch for your changes
It is a good idea to create a branch for your amazing new feature This enables you to work on coding for that feature in isolation. It has the added advantage that if you mess it right royally up, you can just delete that branch and start again with a new one!
To create a branch in VS Code or Azure Data Studio you can click on the branch name at the bottom left.
Or open the Command Palette and type Branch until you see Git: Create Branch
You will be prompted for a branch name
I like to choose a name that relates to the code that I am writing like configurable_engine or removeerroringexample You can see the name of the branch in the bottom left so that you always know which branch you are working on.
The icon shows that the branch is only local and hasn’t been pushed (published) to the remote repository yet
Make some changes and commit them with useful messages
Now you can start writing your code for your awesome new feature, bug fix or maybe just documentation improvement. Keep your commits small and give them useful commit messages that explain why you have made the change as the diff tooling will be able to show what change you have made
Write your code or change the documentation, save the file and in Visual Studio Code or Azure Data Studio you will see that the source control icon has a number on it
Clicking on the icon will show the files that have changes ready
You can write your commit message in the box and click CTRL + ENTER to commit your changes with a message
If you want to do this at the command line, you can use git status to see which files have changes
You will need to git add .or git add .\pathtofile to stage your changes ready for committing and then git commit -m 'Commit Message' to commit them
Notice that I did exactly what I just said not to do! A better commit message would have been So that people can find the guide to forking and creating a PR
Push the changes to your repository
You only have the changes that you have made in your local repository on your computer. Now you need to push those changes to Github your remote repository. You can click on the publish icon
You will get a pop-up asking you if you wish to stage your changes. I click Yes and never Always so that I can use this prompt as a sanity check that I am doing the right thing
At the command line you can push the branch, if you do that, you will have to tell git where the branch needs to go. If you just type git push it will helpfully tell you
fatal: The current branch AwesomeNewFeature has no upstream branch.
To push the current branch and set the remote as upstream, use
git push --set-upstream origin AwesomeNewFeature
So you will need to use that command
You can see in the bottom left that the icon has changed
and if you read the output of the git push command you will see what the next step is also.
Create a Pull Request from your repository back to the original one
You can CTRL click the link in the git push output if you have pushed from the command line or if you visit either you repository or the original repository in your browser you will see that there is a Compare and Pull Request button
You click that and let GitHub do its magic
and it will create a Pull Request for you ready for you to fill in the required information, ask for reviewers and other options. Once you have done that you can click Create pull request and wait for the project maintainer to review it and (hopefully) accept it into their project
If you make more changes to the code in the same branch in your local repository and push them, they will automatically be added to this Pull Request whilst it is open. You can do this if the maintainer or reviewer asks for changes.
Shane has asked for a change
So I can go to my local repository in Azure Data Studio and make the requested change and save the file. If I look in the source control in Azure Data Studio I can again see there is a change waiting to be committed and if I click on the name of the file I can open the diff tool to see what the change was
Once I am happy with my change I can commit it again in the same way as before either in the editor or at the command line. The icon at the bottom will change to show that I have one commit in my local repository waiting to be pushed
To do the same thing at the command line I can type git status and see the same thing.
I can then push my change to my remote repository either in the GUI or by using git push
and it will automatically be added to the Pull Request as you can see
Now that the required changes for the review have been made, the review has been approved by Shane and the pull request is now ready to be merged. (You can also see that dbachecks runs some checks against the code when a Pull Request is made)
Many, many thanks to Shane b | t who helped with the writing of this post even whilst on a “no tech” holiday.
Go Ahead – Contribute to an Open Source Project
Hopefully you can now see how easy it is to create a fork of a GitHub repository, clone it to your own machine and contribute. There are many open source projects that you can contribute to.
You can use this process to contribute to the Microsoft Docs for example by clicking on the edit button on any page.
You can contribute other open source projects like
My previous post on interviews and a number of conversations this year inspired me to write this post. I am lucky enough to have been selected to speak at numerous events over the past few years and I am really lucky because I thoroughly enjoy doing them. The feedback I receive from those sessions has been wonderful and it seems that in general most people really enjoy them.
This leads to some misconceptions though. Recently people have said to me “Oh I am not like you, I get far to nervous to do a session” and also “I am so glad that you get just as nervous as me before presenting I thought it was just me” even though I have blogged about this before. I think it is important for newer speakers as well as more established ones to know that more presenters than you realise get very nervous before they speak.
Many don’t publicise this (which is fine) but I will. I get nervous before I speak. I know that it doesn’t show when I start my presentation but it is there. My stomach does back flips, my hands shake, I forget to bring things to the room. I worry that I will make a catastrophic mistake or that I’ll open my mouth and nothing will come out.
It’s ok. It doesn’t last very long, it’s gone at the moment I start speaking. Other speakers need a few moments into their session before they stop really feeling those nerves but it goes.
Whenever I am involved in a conversation about nerves and presentations on twitter I respond in the same way
— Rob He/Him robsewell@tech.lgbt & @counter.soci (@sqldbawithbeard) December 1, 2016
I love this quote by Joan Jett (young people link) To me it means that you should be nervous before speaking because that energy will ensure that you give a good presentation. If you get up to do a presentation and you are blasé or complacent about it this will be obvious to your audience and not in a good way.
So what to do?
Practice
You can’t just approach a presentation knowing that you will be nervous and expect it to be ok. You need to have a background of confidence that your presentation will turn out ok.
You need to practice.
You need to practice your presentation.
You need to practice your presentation out loud.
You need to practice your presentation out loud more than once.
You have to get used to hearing your own voice when presenting. It can be off-putting hearing yourself blathering on and you don’t want that to surprise you or interrupt your flow. This will also help with projecting away from your screen and into the room if you practice correctly. Imagine all the people in the room and try to speak in their direction with your head up and not pointing down at the screen.
You also need to practice your timings, so that you know that your session will fit in the allocated time. Make notes of your timings at certain points in your presentation so that when you are presenting your session you can be aware of whether you are still on your expected time. Some people will speak faster in their actual session than the practice and some slower. As you practice and learn you will understand your own rhythm and cadence and be able to alter it if required. This will help you to build that confidence that your presentation will be ok.
Run your demos with your machine set up as it will be for the presentation. If you need to have PowerPoint, SSMS, Visual Studio, Visual Studio Code and three SQL instances running then practice with them all running. You should do this so that your timings when running your demos are the same as when you actual present your session. This is even more important if you are doing a webinar as that software will require some of your machines resources which may slow your demo down.
Knowing that your demos are consistently repeatable and how long they will take will also help to give you the confidence that your presentation will be ok.
Deal with them
If we accept that you will have nerves and that’s not a bad thing you have to be able to deal with them, to use them to make your presentation rock.
This is a distinctly personal thing and I have no idea what will work for you. You will have to try some things and see if they work or not. Recently I found a new way for myself
— Rob He/Him robsewell@tech.lgbt & @counter.soci (@sqldbawithbeard) May 20, 2017
Normally I like to be in the room I will be presenting in before I do my session as this gives me something that I can listen to, I can see and feel the layout of the room and also usually prepare my laptop with the correct programmes and run Pester to make sure all is as it should be for my demos. In Portugal I was chatting with someone and missed the start of the session and because of the room layout I did not want to disturb the presenter before me. Slava Oks was giving a presentation which I started to watch and it was so mind-melting I completely forgot that I was presenting in the next time slot! Surprisingly, I had almost no time to be nervous and for this time that was a good thing. The fact that I had already opened my presentation and run my Pester tests also helped.
Some speakers like to be amongst the hustle and bustle of a common area. Some like the peace and quiet of a speaker room or work area. Some put their headphones on. Some go outside. Some pace up and down. Some sit quietly. Many sit in a session in the room. Find the one that works for you.
A few deep breaths
Then just before you are giving your presentation take a few deep breathes, reassure yourself that it’s all good and go and be amazing.
Deep breaths will also be useful if you start to feel nervousness overtaking you during your session. Stop, take a deep breath and carry on.
Incidentally, during a presentation in Exeter at my first SQL Saturday I felt decidedly light-headed and as if I was going to pass out. I had literally forgotten to breathe!
What about…… ?
Don’t forget to leave time for questions at the end. Don’t practice to fill all of the allotted time with your presentation. You will need some time for the audience to ask you questions about your presentations.
Having people ask you questions is a good thing. It means that people are engaged in your presentation and interested in what you have shared. Well done, you have achieved what you set out to do and this is some validation
Repeat the question
Repeating the question that you are asked is recommended best practice for presentations but it has another advantage to you. It allows you a little thinking time to organise your thoughts and calm your nerves if needed.
I don’t know
It’s ok to answer a question with I don’t know. Follow up by asking if anyone in the audience can add some value or say I will research that and find out for you come and give me your contact details afterwards.
Feedback
Some events will provide you with feedback from your attendees. You can also ask your friends or other friendly community members for feedback on your session. Use this to improve. Don’t take all the feedback to heart. Look for trends in the data. Don’t let the poor feedback get you down and don’t let the good feedback go to your head (Remember the complacent quote at the top of this post!)
On a side note, whilst providing a score for feedback is useful, what is more useful is some reasoning behind the score. Remember also that the speaker is a human being with feelings. Be kind whilst being constructive.
Your knowledge
Don’t let worry about nerves prevent us from hearing the great knowledge and experience that you have to share. You wont be alone in feeling nervous and you can help yourself to overcome those nerves and get as much out of speaking as I do.
You will find members of the SQL community wiling to help you if you visit the SQL Community Slack you can ask questions in #presentingorspeaking
Open VS Code and hit CTRL + P and type ext install twitter or hit CTRL + SHIFT + X to open the extensions and search
Hit install and then reload
Accept the prompt
and you will have a Twitter button in the bar at the bottom
If you click it then the top bar will change to the set up wizard
Follow it along and create a Twitter App.
Which will open up the website.
Just fill in the blanks
and then you will have this window
Click on keys and Access tokens
Check the App Permissions are set to read and write
and then click create my access token
Then go back to VS Code and click
Now you are shown the settings.json (which you can always find by File –> Preferences –> Settings )
This bit, the wizard doesn’t explain very well (hence this post) If you have already some settings between the curly braces, you will need to put a comma and then paste the below code. If you do not then paste the below code between the curly braces
and then paste the relevant keys and tokens from your twitter app between the double quotes
THEN PRESS CTRL + S to save the settings.json – Its in caps as when I showed someone they were too excited and didn’t save it!!
Now its all set up you can use the extension. Click the twitter button in the bar and
Now you can have your Home timeline in Code
Yes there are still a few seats left for the Europe PowerShell Conference You can search, see your mentions, your user page
and post 🙂 You can just press F1 and start typing twitter to get the commands
and
You can also message people using D message username. You can see the extension repo on GitHub which will be a good place to raise issues, bugs, feature requests
I’m not suire it will Increase my productivity !! but it really pleases the nerd in me!
I have a lab on my laptop running various servers so that I can problem solve and learn and recently I wanted to add several months of data into a database. I had created a stored procedure to take some parameters perform some logic and insert the data.
To execute the stored procedure in T-SQL I simply run this
which uses the server name, drive letter, date, capacity and free space to add the data
In my wisdom I decided to create some data that was more ‘real-life’ I was interested in storing drive space data and will be learning how to write reports on it. To do this I had pre-populated some tables in the database with 10 Server Names each with 5 drives so I needed 10*5*90 or 4500 statements
I wanted to populate this with about 3 months of data as if it had been gathered every day. I read this post about using CTEs to create sequences and I am sure it can be done this way but I don’t have the T-SQL skills to do so. If someone can (or has) done that please let me know as I am trying to improve my T-SQL skills and would be interested in how to approach and solve this problem with T-SQL
I solved it with Powershell in this way.
Created an array of Servers and an array of Drives to enable me to iterate though each.
I needed to create a date. You can use Get-Date to get todays date and to get dates or times in the future or the past you can use the AddDays() function. You can also add ticks, milliseconds, seconds, minutes, hours, months or years
(Get-Date).AddDays(1)
I then needed to format the date. This is slightly confusing. If you just use Get-Date to get the current date (time) then you can use the format or uformat switch to format the output
To replicate gathering data each day I decided to use a while loop. I set $x to –95 and pressed CTRL and J to bring up Snippets and typed w and picked the while loop. You can find out more about snippets in my previous post I started at –95 so that all the identity keys incremented in a real-life manner oldest to newest.
$x = -98
while ($x -le 0)
{
$Date = (get-date).AddDays($x).ToString('yyyy-MM-dd')
foreach($Server in $Servers)
{
foreach ($Drive in $Drives)
{
I could then use the while loop to generate data for each day and loop through each server and each drive and generate the T-SQL but I wanted more!
I wanted to generate some random numbers for the free space available for each drive. I used the Get-Random cmdlet If you are going to use it make sure you read this post to make sure that you don’t get caught by the gotcha. I decided to set the free space for my OS,Data and Log Files to somewhere between 70 and 3 Gb free as in this imaginary scenario these drives are carefully monitored and the data and log file sizes under the control of a careful DBA but still able to go below thresholds.
I set the TempDB drive to have either 4,7 or 11 Gb free so that i can try to colour code my reports depending on values and if one field only has three values it makes it simpler to verify.
I set the Backup Drive to somewhere between 50 and 0 so that I will hit 0 sometimes!!
Here is the full script. It generated 4500 T-SQL statements in just under 16 seconds
A DBA doesn’t want to run out of space on their servers, even in their labs! To avoid this happening I wrote a Powershell script to provide some alerts by email.
This is the script and how I worked my way through the solution. I hope it is of benefit to others.
The script works in the following way
Iterates through a list of servers
Runs a WMI query to gather disk information
If the free space has fallen below a threshold, checks to see if it has emailed before and if not emails a warning
Resets if free space has risen above the threshold
Logs what it does but manages the space the logs use
As you will have seen before I use a Servers text file in my scripts. This is a text file with a single server name on each line. You could also use a query against a DBA or MDW database using Invoke-SQLCMD2, which ever is the most suitable for you.
$Servers = Get-Content 'PATH\TO\Servers.txt' foreach($Server in $Servers) {
The WMI query is a very simple one to gather the disk information. I format the results and place them in variables for reuse
$Disks = Get-WmiObject win32_logicaldisk -ComputerName $Server | Where-Object {$_.drivetype -eq 3} $TotalSpace=[math]::Round(($Disk.Size/1073741824),2) # change to gb and 2 decimal places $FreeSpace=[Math]::Round(($Disk.FreeSpace/1073741824),2)# change to gb and 2 decimal places $UsedSpace = $TotalSpace - $FreeSpace $PercentFree = [Math]::Round((($FreeSpace/$TotalSpace)*100),2)# change to gb and 2 decimal places
Use a bit of logic to check if the freespace is below a threshold and see if the email has already been sent
# Check if percent free below warning level if ($PercentFree -le $SevereLevel) { # if text file has been created (ie email should already have been sent) do nothing if(Test-Path $CheckFileSevere) {} # if percent free below warning level and text file doesnot exist create text file and email else {
If it has not create a unique named text file and create the email body using HTML and the values stored in the variables
If the freespace is above all of the warning levels, check for existence of the text file and delete it if found so that the next time the script runs it will send an email.
# any logfiles older than 7 days delete Get-ChildItem -Path $Location *logfile* |Where-Object {$_.LastWriteTime -gt (Get-Date).AddDays(7) }|Remove-Item -Force
I run the script in a Powershell Step in an SQL Agent Job every 5 minutes and Now I know when my servers in my lab are running out of space with an email like this
No doubt you will have read this post by Kimberly Tripp and this one and maybe this one too and you want to identify the databases in your environment which have a large number of VLFs and also the initial size and the autogrowth settings of the log files.
There are several posts about this and doing this with PowerShell like this one or this one. As is my wont I chose to output to Excel and colour code the cells depending on the number of VLFs or the type of Autogrowth.
There is not a pure SMO way of identifying the number of VLFs in a log file that I am aware of and it is simple to use DBCC LOGINFO to get that info.
I also wanted to input the autogrowth settings, size, space used, the logical name and the file path. I started by getting all of my servers into a $Servers Array as follows
$Servers = Get-Content 'PATHTO\sqlservers.txt'
Whilst presenting at the Newcastle User Group, Chris Taylor b | t asked a good question. He asked if that was the only way to do this or if you could use your DBA database.
It is much better to make use of the system you already use to record your databases. It will also make it much easier for you to be able to run scripts against more specific groups of databases without needing to keep multiple text files up to date. You can accomplish this as follows
$Query = 'SELECT Name FROM dbo.databases WHERE CONDITION meets your needs'
$Servers = Invoke-Sqlcmd -ServerInstance MANAGEMENTSERVER -Database DBADATABASE -Query $query
I then create a foreach loop and a server SMO object (Did you read my blog post about snippets? the code for a SMO Server snippet is there) returned the number of rows for DBCC LOGINFO and the information I wanted.
It’s not very pretty or particularly user friendly so I decided to put it into Excel
I did this by using my Excel Snippet
$snippet = @{
Title = 'Excel Object';
Description = 'Creates a Excel Workbook and Sheet';
Text = @'
# Create a .com object for Excel
`$xl = new-object -comobject excel.application
`$xl.Visible = `$true # Set this to False when you run in production
`$wb = `$xl.Workbooks.Add() # Add a workbook
`$ws = `$wb.Worksheets.Item(1) # Add a worksheet
`$cells=`$ws.Cells
#Do Some Stuff - perhaps -
`$cells.item(`$row,`$col)=`'Server`'
`$cells.item(`$row,`$col).font.size=16
`$Cells.item(`$row,`$col).Columnwidth = 10
`$col++
`$wb.Saveas(`'C:\temp\Test`$filename.xlsx`')
`$xl.quit()
Stop-Process -Name EXCEL
'@
}
New-IseSnippet @snippet
and placed the relevant bits into the foreach loop
I had to use the ToString() method on the Type property to get Excel to display the text. I wanted to set the colour for the VLF cells to yellow or red dependant on their value and the colour of the growth type cell to red if the value was Percent. This was achieved like this
When I talk to people about Powershell they often ask how can they easily learn the syntax. Here’s a good tip
Open PowerShell ISE and press CTRL + J
You will find a number of snippets that will enable you to write your scripts easily. Johnathan Medd PowerShell MVP has written a good post about snippets on the Hey, Scripting Guy! blog so I will not repeat that but suggest that you go and read that post. It will show you how quickly and easily you will be able to write more complex Powershell scripts as you do not have to learn the syntax but can use the snippets to insert all the code samples you require.
Not only are there default snippets for you to use but you can create your own snippets. However there isn’t a snippet for creating a new snippet so here is the code to do that
$snippet1 = @{
Title = 'New-Snippet'
Description = 'Create a New Snippet'
Text = @"
`$snippet = @{
Title = `'Put Title Here`'
Description = `'Description Here`'
Text = @`"
Code in Here
`"@
}
New-IseSnippet @snippet
"@
}
New-IseSnippet @snippet1 –Force
I frequently use the SQL Server SMO Object in my code so I created this snippet
$snippet = @{
Title = 'SMO-Server'
Description = 'Creates a SQL Server SMO Object'
Text = @"
`$srv = New-Object Microsoft.SqlServer.Management.Smo.Server `$Server
"@
}
New-IseSnippet @snippet
I also use Data Tables a lot so I created a snippet for that too
$snippet = @{
Title = 'New-DataTable'
Description = 'Creates a Data Table Object'
Text = @"
# Create Table Object
`$table = New-Object system.Data.DataTable `$TableName
# Create Columns
`$col1 = New-Object system.Data.DataColumn NAME1,([string])
`$col2 = New-Object system.Data.DataColumn NAME2,([decimal])
#Add the Columns to the table
`$table.columns.add(`$col1)
`$table.columns.add(`$col2)
# Create a new Row
`$row = `$table.NewRow()
# Add values to new row
`$row.Name1 = 'VALUE'
`$row.NAME2 = 'VALUE'
#Add new row to table
`$table.Rows.Add($row)
"@
}
New-IseSnippet @snippet
Denniver Reining has created a Snippet Manager which you can use to further expand your snippets usage and it is free as well.
If you have further examples of useful snippets please feel free to post them in the comments below
Edit 16/12/2014
I am proud that this article was nominated for the Tribal Awards. Please go and vote for your winners in all the categories
A short post today to pass on a script I wrote to fulfil a requirement I had.
Which indexes are on which filegroups. I found a blog post showing how to do it with T-SQL but as is my wont I decided to see how easy it would be with PowerShell. I also thought that it would make a good post to show how I approach this sort of challenge.
I generally start by creating a SQL Server SMO Object You can use the SMO Object Model Diagram or Get-Member to work out what you need. As we are talking indexes and filegroups I will also create a Database object
Hopefully this has shown you how easy it can be to use PowerShell to get all of the information that you need from your SQL Server and how to approach getting that information as well as several ways to display it
An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to eight sets of corresponding secondary databases.You can read more about Availability groups here
There are situations where you may need to refresh these databases. Disaster Recovery is an obvious one but also during development to provide testing or development environments to test your High Availability implementations, run through disaster scenarios, create run books or ensure that the code changes still work with AG. There are other scenarios but this post covers the automation of restoring an Availability Group Database from a backup.
The steps that you need to take to restore an Availability Group Database are
Remove Database from the Availability Group
Restore the Primary Replica Database
Backup the Primary Replica Database Transaction Log
Restore the Secondary and Tertiary Replica Databases with no recovery
Add the Database back into the Availability Group
Resolve Orphaned Users – Not covered in this script
Check the status
Here is my set up for this post
I have 3 servers SQL2012SER08AG1, SQL2012SER08AG2 and SQL2012SER08AG3 with 3 databases in an Availability Group called AG_THEBEARD1. SQL2012SER08AG2 is set up as a secondary replica using Synchronous-Commit Mode SQL2012SER08AG3 is set up as a read only replica using Asynchronous-Commit Mode. I have three databases in my Availability Group and today I shall use the database called TestDatabase (I have no imagination today!) to demonstrate the refresh
The script requires some variables to be set up at the beginning. You can easily change this and make the script into a function and call it if you desire, but for this post I shall consider the script as a standalone. The reasoning for this is that I imagine that it will be placed into a run book or stored for use in a repository for specific use and therefore reduces any pre-requisites for using it.
Next Restore the Primary Replica Database, Backup the Primary Replica Database Transaction Log
and Restore the Secondary and Tertiary Replica Databases with no recovery using Restore-SqlDatabase and Backup-SqlDatabase (You can also use the SMO method in the previous post if you wish)
I also like to add some output to show the progress of the script. This can be logged using Out-File or displayed on the screen using Out-Host.
$EndDate = Get-Date
$Time = $EndDate - $StartDate
Write-Host "
##########################################
Results of Script to refresh $DBName on
$PrimaryServer , $SecondaryServer , $TertiaryServer
on AG $AGName
Time Script anded at $EndDate and took
$Time
" -ForegroundColor Green
Here are the results of my script
Here is the script
<#
.NOTES
Name: Availability Group Refresh
Author: Rob Sewell http://sqldbawithabeard.com
.DESCRIPTION
Refreshes an Availbaility group database from a backup
YOU WILL NEED TO RESOLVE ORPHANED USERS IF REQUIRED
#>
## http://msdn.microsoft.com/en-gb/library/hh213078.aspx#PowerShellProcedure
# http://msdn.microsoft.com/en-us/library/hh213326(v=sql.110).aspx
cls
# To Load SQL Server Management Objects into PowerShell
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’) | out-null
$LoadServer = "SQL2012Ser2012" # The Load Server
$Date = Get-Date -Format ddMMyy
$PrimaryServer = "SQL2012SER08AG1" # The Primary Availability Group Server
$SecondaryServer = "SQL2012SER08AG2" # The Secondary Availability Group Server
$TertiaryServer = "SQL2012SER08AG3" # The Tertiary Availability Group Server
$AGName = "AG_THEBEARD1" # Availability Group Name
$DBName = "TestDatabase" # Database Name
$LoadDatabaseBackupFile = "\\sql2012ser2012\Backups\GoldenBackup\LoadTestDatabase" + $Date + ".bak" # Load database Backup location - Needs access permissions granted
$DatabaseBackupFile = "\\sql2012ser2012\Backups\GoldenBackup\TestDatabase" + $Date + ".bak" # database Backup location - Needs access permissions granted
$LogBackupFile = "\\sql2012ser2012\Backups\GoldenBackup\TestDatabase" + $Date + ".trn" # database Backup location - Needs access permissions granted
# Path to Availability Database Objects
$MyAgPrimaryPath = "SQLSERVER:\SQL\$PrimaryServer\DEFAULT\AvailabilityGroups\$AGName"
$MyAgSecondaryPath = "SQLSERVER:\SQL\$SecondaryServer\DEFAULT\AvailabilityGroups\$AGName"
$MyAgTertiaryPath = "SQLSERVER:\SQL\$TertiaryServer\DEFAULT\AvailabilityGroups\$AGName"
$StartDate = Get-Date
Write-Host "
##########################################
Results of Script to refresh $DBName on
$PrimaryServer , $SecondaryServer , $TertiaryServer
on AG $AGName
Time Script Started $StartDate
" -ForegroundColor Green
cd c:
# Remove old backups
If(Test-Path $LoadDatabaseBackupFile){Remove-Item -Path $LoadDatabaseBackupFile -Force}
If(Test-Path $DatabaseBackupFile){Remove-Item -Path $DatabaseBackupFile}
If(Test-Path $LogBackupFile ) {Remove-Item -Path $LogBackupFile }
Write-Host "Backup Files removed" -ForegroundColor Green
# Remove Secondary Replica Database from Availability Group to enable restore
cd SQLSERVER:\SQL\$SecondaryServer\DEFAULT
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$SecondaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName
Write-Host "Secondary Removed from Availability Group" -ForegroundColor Green
# Remove Tertiary Replica Database from Availability Group to enable restore
cd SQLSERVER:\SQL\$TertiaryServer\DEFAULT
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$TertiaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName
Write-Host "Tertiary removed from Availability Group" -ForegroundColor Green
# Remove Primary Replica Database from Availability Group to enable restore
cd SQLSERVER:\SQL\$PrimaryServer\DEFAULT
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$PrimaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName
Write-Host "Primary removed from Availability Group" -ForegroundColor Green
# Backup Load Database
Backup-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $LoadServer
Write-Host "Load Database Backed up" -ForegroundColor Green
# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer
$srv.KillAllProcesses($dbname)
# Restore Primary Replica Database from Load Database
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $PrimaryServer -ReplaceDatabase
Write-Host "Primary Database Restored" -ForegroundColor Green
# Backup Primary Database
# Backup-SqlDatabase -Database $DBName -BackupFile $DatabaseBackupFile -ServerInstance $PrimaryServer
Backup-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $PrimaryServer -BackupAction 'Log'
Write-Host "Primary Database Backed Up" -ForegroundColor Green
# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $SecondaryServer
$srv.KillAllProcesses($dbname)
# Restore Secondary Replica Database
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $SecondaryServer -NoRecovery -ReplaceDatabase
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $SecondaryServer -RestoreAction 'Log' -NoRecovery -ReplaceDatabase
Write-Host "Secondary Database Restored" -ForegroundColor Green
# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $TertiaryServer
$srv.KillAllProcesses($dbname)
# Restore Tertiary Replica Database
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $TertiaryServer -NoRecovery -ReplaceDatabase
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $TertiaryServer -RestoreAction 'Log' -NoRecovery -ReplaceDatabase
Write-Host "Tertiary Database Restored" -ForegroundColor Green
# Add database back into Availability Group
cd SQLSERVER:\SQL\$PrimaryServer
Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database $DBName
Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $DBName
Add-SqlAvailabilityDatabase -Path $MyAgTertiaryPath -Database $DBName
Write-Host "Database Added to Availability Group " -ForegroundColor Green
# Check Availability Group Status
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer
$AG = $srv.AvailabilityGroups[$AGName]
$AG.DatabaseReplicaStates|ft -AutoSize
$EndDate = Get-Date
$Time = $EndDate - $StartDate
Write-Host "
##########################################
Results of Script to refresh $DBName on
$PrimaryServer , $SecondaryServer , $TertiaryServer
on AG $AGName
Time Script ended at $EndDate and took
$Time
" -ForegroundColor Green
Which will open up the website.
