John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

AZCopy for SQL Backups and other stuff

17th July 2019 By John McCormack 1 Comment

AZCopy for SQL Backups and other stuff

AZCopy is a useful command line utility for automating the copying of files and folders to Azure Storage Account containers. Specifically, I use AZCopy for SQL Backups but you can use AZCopy for copying most types of files to and from Azure.

In this blog post example (which mirrors a real world requirement I had), the situation is that whilst I need to write my SQL backups over a network share, I also want to push them up to Azure Storage (in a different region) to allow developers quicker downloads/restores. This is why I need to use AZCopy. If I only needed my backups to be written to Azure, I could have used BACKUP TO URL instead.

Authentication

I use Shared Access Signatures (SAS token) to authorise access to the storage account container. You can create an SAS token for any Access key by clicking Generate SAS and connection string button from storage accounts/Shared Access Signature.

For this scenario, my SAS token is limited to write / add / create operations and cannot be used to read or delete data. This is just to limit the scope for security reasons, but you can decide whatever meets your needs.

SAS tokens have an expiry date. I’ve set mine to expire on 16th August 2019 so I will need to create a new token at this time and update my AZCopy command if I want to continue with the automated copying via a scheduled script.

sas configuration options
SAS Configuration options 
Command
Storage Account: johnbox
Container: instance1

The basic copy command is simple. An example is:
[shell]azcopy cp “<source location>” “<destination location>”[/shell]

For using an SAS token, we append this to the destination url:
[shell]azcopy cp “C:\temp\johns_backup.bak” “https://johnbox.blob.core.windows.net/instance1?sas-token”[/shell]

However, the azcopy sync option is more efficient because it won’t force a copy over the network if the source and destination already match. The example below also syncs the full folder, not just a specific file.
[shell]azcopy sync “C:\temp” “https://johnbox.blob.core.windows.net/instance1?sas-token” [/shell]

 Schedule

I chose to schedule this in a sql agent job. The job step is directly after the step where I back up to my network share. You could also use windows task scheduler if you prefer.

If you haven’t used AZCopy yet?

Download AZCopy from https://docs.microsoft.com/en-us/azure/storage/common/storage-use-azcopy-v10. The page also gives instructions on how to run and authenticate AZCopy commands.

If you have time, please have a look at my other Azure posts.

Filed Under: Azure, front-page, Guides Tagged With: azcopy, azure

Put tempdb files on D drive in Azure IAAS

21st March 2019 By John McCormack 7 Comments

Data loss warning Azure VMTempdb files on D drive you say?

Azure warn you not to to store data on the D drive in Azure VMs, but following this advice could mean you are missing out on some very fast local storage. It’s good general advice because this local storage is not permanently attached to your instance, meaning you could lose data or log files if your VM is stopped and restarted but what if you could afford to lose certain files? Say files that are recreated during startup anyway.

TempDB is the ideal candidate for this. No other database is suitable! Putting the tempdb data and log files onto D drive can be achieved quite easily with a little bit of effort. And you will most likely see a big improvement in tempdb read/write latency.

Just look at these results! Even for someone like me who sometimes looks at charts and often can’t see the obvious, I don’t think there is any denying when the change took place here.

tempdb latency azure IAAS
Blazing fast now

 

Overview of how to configure SQL Server to use D drive for tempdb on Azure VM

  1. Run the ALTER DATABASE command for all of the files you need to change e.g.
    • ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = 'D:\TempDB\tempdev.mdf')
  2. Create Folder D:\TempDB or something similar
  3. Restart SQL Server and make sure your files are created in D: as expected
  4. Manually remove any old tempdb files still lying around

Now to make sure tempdb is created when the VM restarts

  1. Create a Powershell script which will create the D:\TempDB folder on startup and start the SQL services
    • You could use this script at https://community.idera.com/database-tools/blog/b/community_blog/posts/configuring-tempdb-on-azure-iaas-for-sql-server 
    • Schedule this script with a Task Scheduler startup job
    • Change your execution policy to remote signed if it isn’t that. Set-ExecutionPolicy RemoteSigned
  2. Change the startup type of your SQL Services to Automatic (Delayed Start)
  3. Test this thoroughly in dev before you rely on it in production. If it doesn’t work, you’ll need to remember how you configured tempdb and manually create the folders, then start SQL Server. Not a fun task under pressure.
More tempdb goodness

Configuring TempDB for SQL Server 2016 instances

IT Certification Category (English)728x90

Filed Under: Azure, Azure VM, front-page Tagged With: azure, azure iaas, sql server on Azure, tempdb

  • « Previous Page
  • 1
  • 2
  • 3
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2025

 

Loading Comments...