How SQL Server Synonyms Help Database DevOps

Published On: 2021-03-10By:

Synonyms inside SQL Server are one of those useful but forgotten features. A synonym is a database level object that allows you to provide an alternative name for another database object such as a view, user defined table, scalar function, stored procedure, inline table valued function (tvf), or extended stored procedure. They can also be used for CLR Assembly related stored procedures, CLR tvf, CLR scalar functions or even CLR aggregate functions.  There are many practical uses for synonyms, and I’ll explain how to create them and some use cases.

Read the full article here at Red-Gate’s Simple Talk

The post How SQL Server Synonyms Help Database DevOps appeared first on A Shot of SQLEspresso.

Contact the Author | Contact DCAC

TDE, Azure SQL MI, and Failover Groups

Published On: 2021-03-09By:

Transparent Data Encryption (TDE) with a Customer Provided Key, Azure SQL Managed Instance (MI), and Failover Groups should all be easy to setup. Azure SQL MI with either of the other two is pretty straightforward to setup. All three of them at the same time require a little doing in order to get them all working correctly together. This is because you as the admin need to manually move the key which TDE is using to encrypt the databases to the secondary region manually, and you need to tell the Azure SQL MI in the secondary region where to find the key.

Having a customer-managed key for TDE requires some special setup to ensure that the key is available to both instances. This is done by placing the key within Azure Key Vault. In order to set up a failover group you need to have not just the Azure Key Vault in the primary site, but you also need to set up one in the secondary site which will be hosting your Azure SQL MI.

Once you have an Azure Key Vault in both sites, you can use the GUI to export the key from the primary site and restore it to the Azure Key Vault in the secondary site. After that is done you’ll need to give the Azure SQL MI in the secondary site access to the key, which is done through the Access Policies section of Azure Key Vault. The managed instance needs three permissions to access keys; which are Get, Unwrap Key, and Wrap Key. No other rights need to be granted to the MI.

To grant rights to the Managed Instance click the Add Access Policy link.

Then select the three rights needed in the Key Permissions drop down.

Then click on “None Selected” next to “Select principal”. This will bring up a list of all users and applications which are registered with Azure Active Directory. Find the Managed Instance for the secondary environment and click “Select” at the bottom of the screen, then click “Add” on the prior screen (where you selected the permissions).

Once this is done, go into the setting for the Managed Instance in the secondary site, and select Transparent data encryption from the menu. On this screen change the setting from “Service-managed Key” to “Customer-managed Key”. Then either select a key or enter a key identifier of the key which will be used for new databases from the key vault (if all your databases are protected with one key, just select this key).

Save these settings changes and edit the properties of your Azure SQL MI in the primary site. On this server select Failover Groups and click the “Add Group” button at the top of the blade that opens. Select all the correct information for the secondary instance and the group name and save the settings.

It’ll take a while, but when it is done the map should look something like this (depending on what regions you selected of course). Once you’ve gotten to this point you’ve set up your Azure SQL Managed Instance using TDE with a Customer-managed Key and a Failover group.

You should have a beer at this point, you’ve earned it.


Contact the Author | Contact DCAC

Azure Region Selection for Resource Group Ownership Matters

Published On: 2021-03-01By:

When creating resources and resource groups, the locations in which the resource groups are created rarely get a thought. Resource group locations matter, not when everything is working but when there is a failure at an Azure region.

When you select the location for your resource group, you aren’t limited to putting resources from that location in the resource group. Resources from any Azure region can be placed in an Azure resource group which is stored in any Azure region. When the Azure region that is hosting the resource group fails, no changes can be made to the resources within that resource group, or to the resource group itself.

Firewalls as an Example

Let’s look at an example that I was working on for a client. Production for this client was is North Central US and the Disaster Recovery environment for this client is in South Central US. This client wanted to use Azure Firewall and have that deployed for both their North Central US and South Central US environments. As we were discussing the firewall rules that we were going to put in place we wanted to have a common rule set for outbound rules so that the exact same rules would be in place both for both Azure regions. This meant that we needed to place all the Azure Firewalls in the same resource group (as Azure Firewalls Policies can only inherit rules from another policy, when that policy is stored in the same region the parent policy).

This presented a problem. If we stored the resource group and the policies in North Central US and North Central US failed, then we couldn’t edit the Disaster Recovery policy. If we stored the policies and the resource group in South Central US and South Central US failed then we couldn’t edit or change the production firewall policy until our DR site came back up. The same applied to the resource group location. If we put the resource group in North Central US and the policies somewhere else, and production failed, there’s no guarantee that we’d be able to make firewall changes if those changes required being able to update the resource group itself.

What we ended up going in this case was putting the resource group and the Firewall Policies in a third Azure region, US West 2 in this case. This was if North Central US fails we can still edit our Azure Firewall Policies for our Disaster Recovery environment, and if South Central US fails we can still edit our Azure Firewall Policies for our Production environment.


This same process applies to a web farm that is spread out across multiple Azure regions. If for example, you had a WebApp in two Azure regions with one in Central US and the second one in North Europe you wouldn’t want these to both be in the same resource group. If they were in the same resource group, you’d want that resource group to be in a third site. Because whatever site holds the resource group needs to be online for you to be able to make changes to the resources within the resource group, provided that those changes need to update the resource group directly.

The Solution

The solution to this is to either have one resource group for each region or put the resource group itself in a third region so that a failure of either region won’t affect it.


Contact the Author | Contact DCAC

Identifying SQL Server Performance Problems Part 3

Published On: 2021-02-24By:

Continuing with our Performance Tuning series as you have learned baselining and knowing your normal system performance metrics is an important part of performance tuning. In part 1 we discussed collecting vital system metrics such as CPU Utilization and Memory usage. In part 2 we took a glimpse into how we can gather those metrics and other using the different tools that are available to use like Performance Monitor, Profiler, Extended Events and DMVs. In this part 3 we will look at how identifying the appropriate waits events and corresponding resolution will be critical for resolving performance issues. But first you need to understand what waits are and where to find the information needed to identify waits in your system.

When a thread is being executed and is forced to wait on something this is called queueing. The thread waits for system resources, lining up in a first come first served fashion waiting for things like a CPU scheduler. The time waiting between actively running and suspended (waiting) states is the measurable wait event that SQL Server keeps track of via using. internal metrics. The database engine tracks why executed waits must wait and how long they are queuing for resource threads. These are commonly called wait statistics. These metrics are easily discernible using the dynamic management view (DMV) sys.dm_os_wait_stats (or in Azure SQL Database sys.dm_db_wait_stats).  This information is important to understanding what baseline performance of your database and can help you identify specific performance issues both with query execution and hardware limitations. Data gained from this view can be aggregated and tracked to provide a clearer picture on what might be causing the executed thread to wait. Knowing the top waits that occur in your environment is a key task for all DBAs. Below you will learn common actionable waits as well as those that are benign and occur normally as part of SQL Servers thread queueing process.

Check out the full part 3 article on the Idera Community Site.

The post Identifying SQL Server Performance Problems Part 3 appeared first on A Shot of SQLEspresso.

Contact the Author | Contact DCAC
1 2 3 4 5 487


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link