Press "Enter" to skip to content

Category: Administration

Azure SQL Hyperscale Auto-Scaling

Published 2020-02-05 by Kevin Feasel

Davide Mauri explains how automatically to scale Azure SQL Hyperscale:

Azure SQL Hyperscale is the latest architectural evolution of Azure SQL, that has been natively designed to take advantage of the cloud. One of the main key features of this new architecture is the complete separation of Compute Nodes and Storage Nodes. This allow for independent scale of each service, making Hyperscale more flexible and elastic.

In this article I will describe how it is possible to implement a solution to automatically scale your Azure SQL Hyperscale database up or down, to dynamically and automatically adapt to different workload levels without the requiring manual .

Davide has some test measures of how much downtime you see and give you a couple thoughts on how you can track when it’s time to scale up or down.

Comments closed

Burn the Database Down

Published 2020-02-04 by Kevin Feasel

Jana Sattainathan has a script to drop almost all user objects in a database:

I hope this script does not become infamous for the wrong reasons! Please use caution.

I had to help a team recreate everything in a database and test their scripts but leave the roles and role grants in place. Basically, this meant that I could have scripted out the permissions and recreated the database but I thought it would be easier and more re-runnable to just drop everything else except the permissions.

Caution here means making sure you have good backups beforehand, ensuring that you pick the right database, and double-checking everything.

Comments closed

Big Data Clusters and Fixed IP Addresses

Published 2020-02-04 by Kevin Feasel

Denny Cherry warns you about Big Data Clusters and keeping a particular IP address:

No problem, we just added in the correct IP range to the possible addresses for the vNet, added a new Subnet and moved the VMs over to the new subnet (which caused the VMs to reboot, but that was expected).

It turns on that BDC in SQL Server 2019 doesn’t like having the IPs changed for the aks nodes.  The problem stems from the fact that BDC is generating its certificates off of the IP address of the node, so if the IP address of the node changes (even if you are using DHCP for on-prem nodes and DHCP gives you a new IP address) your BDC won’t respond.

Read on for your three possible solutions.

Comments closed

Creating a New Database in Azure Data Studio

Published 2020-02-03 by Kevin Feasel

Dave Bland shows how you can create a new database using Azure Data Studio:

Regardless of what tool we are using, SQL Server Management Studio or Azure Data Studio, the need to create new databases is always present. Using Transact SQL is an option in both tools.  What is not an option in both tools is to right click and to go “New Database”.  This has been in SSMS for many years, however it is NOT present in Azure Data Studio.

If you really do want to create a new database using a GUI in Azure Data Studio, Dave shows you the extension you need. It’s not as fully-featured as the wizard in Management Studio, so it would make sense to understand what that wizard is doing and learn the T-SQL yourself.

Comments closed

Queueing Event Notifications with Service Broker

Published 2020-02-03 by Kevin Feasel

Max Vernon ties event notifications to Service Broker:

My previous post shows how to configure an Event Notification to fire whenever a login event occurs. The post uses Service Broker to receive those Event Notifications into a queue, which is then processed by a stored procedure and saved into a standard SQL Server database. This post provides a quick+dirty VB.Net command line monitor that shows how full a Service Broker queue is.

The following code should be pasted into a blank Visual Studio VB.Net console project. It is trivially easy to translate this into C#, but I like VB – what can I say.

Click through for the code. No F# translation from me, however, as I am lazy.

Comments closed

Tracing a Session with Extended Events

Published 2020-01-31 by Kevin Feasel

Jason Brimhall shows how you can trace a specific session using Extended Events:

The ability to quickly and easily trace a query is important to database professionals. This script provides one useful alternative to trace a specific spid similar to the method of using the context menu to create the trace within SSMS and Profiler.

This is yet another tool in the ever popular and constantly growing library of Extended Events. Are you still stuck on Profiler? Try one of these articles to help remedy that problem (here and here)

Read on to see how.

Comments closed

Chaos Engineering with SQL Server

Published 2020-01-30 by Kevin Feasel

Andrew Pruski is excited about Chaos Engineering:

Chaos Engineering is a practice in which we run experiments against a system to see if it reacts the way we expect it to when it encounters a failure.

We’re not trying to break things here…Chaos Engineering is not breaking things in production.

If I said to my boss that we’re now going to be experiencing an increased amount of outages because, “I AM AN ENGINEER OF CHAOS”, I’d be marched out the front door pretty quickly.

On the plus side, we will know Andrew’s supervillain origin story.

2 Comments

Moving tempdb on a SQL Server Instance

Published 2020-01-29 by Kevin Feasel

Drew Skwiers-Koballa gives us the step-by-step process for moving tempdb from one folder to another on a machine:

Not only can the size of TempDB files be unpredictable (unless the workload is completely predictable or a size limit is placed), but it is full of old Tupperware. That is, if TempDB is destroyed, your SQL Server will create a new one as soon as the service restarts. The whole migration is summarized in these 4 steps:

1. Create a new location for TempDB
2. Use TSQL to change the TempDB file location(s)
3. Restart the SQL Server service during a maintenance window
4. Verify and clean up

This is one of the easier things to move, but it does require server downtime.

Comments closed

Interpretability Issues in Monitoring Tools

Published 2020-01-29 by Kevin Feasel

Brent Ozar explains how it’s hard to explain things without context:

However, outside of that window, you may not have any wait time on CPU at all. If queries are simple enough, and there isn’t enough concurrency, then as we discuss in Mastering Server Tuning, you can end up with SOS_SCHEDULER_YIELD waits with no time attached to them. You wouldn’t notice a CPU problem at all outside of that 8AM window because you don’t have a CPU bottleneck – yet. And to be fair, you probably wouldn’t tune a server in this kind of shape, either.

But when you DO need to tune a server that isn’t running at 100% capacity, picking the right query to tune isn’t just about wait stats: it’s also about which queries you need to be more responsive, and how you need to plan for future load. If your company tries to run a flash sale, and a lot of folks try to check out at the same time, Query T is going to knock your server over. But wait-stats based tools won’t see that coming: they’ll still be focused on Query R, the only one that spends a lot of time waiting on CPU.

Good food for thought.

Comments closed