It’s Been a Strange Two Years

November 14, 2021 Leave a comment

My last post on this blog was almost two years ago. That was coming out of PASS Summit 2019 and my last post recounted how much I enjoyed that experience. It was about the same time that we started hearing news of the COVID-19 virus. In late February 2020, I had the pleasure of attending SQL Saturday Tampa. It was a wonderful time. Sadly, it would be my last SQL Saturday.

The lockdown resulting from COVID-19 led to the demise of PASS. I won't go into detail here, but the lack of an in-person conference reduced revenue streams to the point that PASS was forced to declare bankruptcy. The day PASS ceased to exist was a sad one, but as many have said, PASS isn't the community, the community members are.

I have been incredibly fortunate to be a part of that community for 10 years. My involvement has provided immeasurable benefits, both personally and professionally. I have gotten jobs, presented across the country, and become friends with amazing people.

It is those benefits that have driven me to give back to the community. I've received so much -- and I'd never be able to pay it all back, but I'm sure going to try. The past two years have been strange and difficult in a lot of ways. Not being able to join the community in person is one of the most hardest things.

I am planning to become more active in the last days of 2021 and hope to hit my stride at the start of 2022. Watch this blog for posts and my first foray into video presentation.

I hope you are surviving and thriving in these strange times and hope to meet in person in the near future.

Categories: #sqlfamily, Blogging

PASS Summit 2019 – The Best One Yet

November 21, 2019 Leave a comment

In 2019, I attended my 11th PASS Summit.  Every year I say it is better than the last.  Usually this comes down to the fact that I know more people each year.  That was the case again this year, but I cannot remember being as excited about the content I learned at the conference.

If you had told me going in that a tool was the thing I would be most excited about, I would have thought you were crazy.  But it's true, and that tool is Azure Data Studio (ADS).  ADS is a fork off of the VSCode project and allows connections to databases on prem and in Azure.  It also provides a link to source control systems.  The thing that has me so excited about ADS is the ability to build notebooks.

Notebooks are files that allow code to be combined with text more robustly than a T-SQL or PowerShell script with comments.  The text is written using markdown, which is a simple markup language that allows formatting.  Code can be placed in line with markdown text and can be executed from within the notebook.  As of November 4, ADS supports both T-SQL and PowerShell.

The utility of notebooks was brought home when Bob Ward ran all of the demos for his SQL Server 2019 Summit precon using notebooks.  That was pretty cool, but what was amazing is that he made those notebooks publicly available via GitHub.  Once you have installed ADS, you can download that GitHub repo and run the demo code.  The markdown provides information about and instructions for executing the code.  An example of one of the notebooks is shown below:

NotebookScreenshot

In the example above, the markdown text is above the code cell.  The play button in the code cell allows execution of the query.  The query messages and results appear below the code cell.  When the notebook is saved, the query results are saved with it.  This makes them perfect for creating runbooks, wikis, and process documentation.

I've created demos for a presentation on two of the new features in SQL Server 2019, Accelerated Database Recovery and in-memory metadata for tempdb.  Because PowerShell is supported, I am able to run docker commands within the notebook to build out a demo instance in seconds.  You can find my demo notebooks here.

I hope you find this post useful.  Let me know if you have any questions.

Note: The code in my notebooks has been modified from the SQL Server 2019 notebooks from Bob Ward's precon.  Many thanks to Bob and the SQL Tiger Team for making these available.  Also, thanks to Vicky Harp, Alan Yu, and the rest of the Azure Tools team for ADS. Keep your eye out for updates to ADS, because additional functionality is coming.

Azure SQL Managed Instance – Migrate a Database Using Backup and Restore

November 15, 2018 2 comments

If you have run through my last Managed Instance blog post, you have a Managed Instance at your disposal.  The PowerShell script for creating the network requirements also contains steps to create an Azure VM in a different subnet in the same VNet.  Unless you have a site-to-site VPN or Express Route between your on-prem environment and Azure, you will use this VM to connect to your Managed Instance.

Install Management Studio on the Azure VM.  To connect to your Managed Instance, you will need the host name for your Managed Instance.  You can find the Managed Instance host name on the resource page for your Managed Instance in the Portal.

GetMiHostName

Open Management Studio and copy the instance name into the Server field in Management Studio and enter the admin account and password you specified on creation.  Click connect.

ConnectToMi

Once the Managed Instance opens, you will be able to navigate it using Object Explorer.  The experience will look very familiar if you are used to using SSMS.  You will see all four system databases and SQL Server Agent.  If you expand the Agent entry in Object Explorer, you will notice that Alerts do not currently exist.  Also, note that the SQL version number is listed as 12.0.2000.8.  This is NOT SQL Server 2014.  Azure SQL Database and Azure SQL Managed Instance are currently at version 12.

ObjectExplorer

To create your first database, you will need to upload a backup file to Azure blob storage. If you do not have a Storage Account, click the Add button at the top of your Resource Group.

AddStorageAccount.png

In the search box, enter Storage Account.   Select Storage account - blob, file, table, queue.  On the next screen, click Create.

EnterStorageAccount.png

Confirm your subscription and resource group are correct. Select and name your storage account.  Select the same location as your Managed Instance, Standard performance, and Storage V2 (general purpose V2).  For this example, I selected Locally-redundant storage (LRS) because it is the least expensive option. Click the Review and Create button.

NameStorageAccount.png

Once the storage account is provisioned, you will need to create a container to hold the backup file.  I think of the storage account as a drive and containers as folders on that drive.  Under Services, click Blobs.

ClickBlobs.png

Once the Blob service screen opens, click Containers at the top of the screen.  Name your container and leave the Public access level set to the default.  Click OK.

ClickContainer

NameContainer.png

 

After your container is created, click the container name to open it.  Click the Upload button at the top of the screen.

UploadFile.png

Click the folder button to the right of the Files text box and select your backup file.  Once the Upload button becomes active, click it.  Once the upload is complete, you are ready to restore a database to your Managed Instance.

SelectFile

Go back to your VM and connect to your Managed Instance.  Right-click on Databases in Object Explorer and select Restore Database.

REstoreDatabase.png

Click the Device radio button.  Click the ellipsis button to the right of the Device text box.  Notice that URL is the only entry available in the Backup media type dropdown.  This is because you must restore a Managed Database from Azure blob storage.

SelectBackup.png

 

Click the Add button.  You will be prompted to sign into your Azure subscription.  Click the Sign In button.SignIn.png

Enter the login and password for your Azure account.  On the next dialogue, select your subscription, storage account, and container.  Click the Create Credential button to create a Shared Access Signature to allow SQL Server to access Azure storage.

SelectContainer.png

Once the credential populates in the Shared Access Signature Generated text box, click OK.

SelectCredential.png

You will return to the Select a Backup File Location dialog with the Azure storage container and Shared Access Signature boxes populated.  Click OK

OkBackupLocation.png

If you have used Management Studio to select a backup file to restore, the Locate Backup File in Microsoft Azure will look familiar.  Expand Containers in the left-hand pane and select your container.  You should see your backup file in the right-hand pane.  Click the file name and click OK.

SelectBackupFile.png

The Select backup devices dialog will display with your backup file selected.  Click OK.

BackupSelected.png

The Restore Database dialog will display with your backup file listed in the Backup sets to restore text box.  Note that there are no pages in the Select a page pane for Files or Options.  Because storage is managed for a Managed Instance, you cannot select where the files will be placed and you cannot set a database to NORECOVERY.  Click the Script button.

DatabaseRestore.png

A query window will open in Management Studio containing the RESTORE script that SQL Server will execute if you click the OK button on the Restore Database dialog.  Navigate to that script.  The RESTORE statement is extremely simple:

RestoreStatement.png

You cannot add any WITH clauses to the statement.  If you try you will receive the following error:

ErrorMessage

To complete the restore of your database, execute the restore script.  Once the restore completes, you will be able to access it using Management Studio. In my next post, I will provide additional options for migrating databases and instance-level objects to Managed Instance.