Category: Administration

Once you have created the prerequisites, you are ready to create your first Managed Instance. As of now, Managed Instance is only available in the following subscription types:
Pay-As-You-Go
Enterprise Agreement
Cloud Service Provider
Information about subscription and resource limitations can be found here. I will update this with any changes.

Frank has a series of screenshots to show you the way.

Comments closed

“String Or Binary Data Would Be Truncated” Update In SQL Server 2017

Published 2018-11-01 by Kevin Feasel

Randolph West shows us how, in SQL Server 2017 CU 12, we can remove the scourge of “String or binary data would be truncated”:

This is how the error message looks now:
1
2
3
Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber
String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’.
Truncated value: ‘%.*ls’.
Notice how the table, column and value are all mentioned in the error message now, which makes debugging and troubleshooting much easier. Thank you Microsoft!
As of 24 October 2018, we can now get the full picture in SQL Server 2017 as well, provided we install Cumulative Update 12. I’d say this is worth the update in and of itself!

There is a trace flag involved, so check it out.

Comments closed

Scary Scenarios For Database Developers

Published 2018-10-31 by Kevin Feasel

Bert Wagner gets into the Halloween spirit:

You’ve spent all morning loading millions of records into your tables. Some of that data was painstakingly loaded by hand from flat files, and some lookup values you even authored manually because “you’ll only have to do this once.”
You are jumping around between SSMS windows, running this query here and that query there, pressing F5 like there’s no tomorrow. And then right before you press to F5 to finish all of your work…
…you realize that you ran the whole tab worth of code instead of the one statement you meant to highlight. “Commands completed successfully” flashes in your Message window, and in horror you discover you accidentally ran a truncate table statement!
Ok so this is pretty bad.

Not that I’ve done that before…nope…

Comments closed

Automating E-mail Of Windows Event Log Alerts Via Powershell

Published 2018-10-29 by Kevin Feasel

Dave Bermingham shows us how to automatically fire off e-mails for specific Windows Event Log event IDs:

The first thing that you need to do is write a Powershell script that when run can send an email. While researching this I discovered many ways to accomplish this task, so what I’m about to show you is just one way, but feel free to experiment and use what is right for your environment.
In my lab I do not run my own SMTP server, so I had to write a script that could leverage my Gmail account. You will see in my Powershell script the password to the email account that authenticates to the SMTP server is in plain text. If you are concerned that someone may have access to your script and discover your password then you will want to encrypt your credentials. Gmail requires and SSL connection so your password should be safe on the wire, just like any other email client.
Here is an example of a Powershell script that when used in conjunction with Task Scheduler which will send an email alert automatically when any specified Event is logged in the Windows Event Log.

Read on for the script as well as some bonus troubleshooting.

Comments closed

Azure SQL Database Hyperscale Tier

Published 2018-10-26 by Kevin Feasel

Chris Seferlis looks at a new service tier offering for Azure SQL Database:

The Hyperscale service tier provides the following capabilities:

Support for up to 100 terabytes of database size (and this will grow over time)
Faster large database backups which are based on file snapshots
Faster database restores (also based on file snapshots)
Higher overall performance due to higher log throughput and faster transaction commit time regardless of the data volumes
The ability to rapidly scale out. You can provision one or more read only nodes for offloading your read workload for use as hot standbys.
You can rapidly scale up your compute resources (in constant time) to accommodate heavy workloads, so you can scale compute up and down as needed just like Azure Data Warehouse

At what cost? I like Chris’s “not inexpensive” understatement here.

Comments closed

Slipstream Installation Of SQL Server

Published 2018-10-26 by Kevin Feasel

Randolph West shows how to install a pre-patched version of SQL Server:

For this example, we will be using the SQL Server 2017 Developer Edition RTM (called en_sql_server_2017_developer_x64_dvd_11296168.iso), and Cumulative Update 11 (called SQLServer2017-KB4462262-x64.exe), which was the latest CU available at the time of this writing.
Place the Cumulative Update in a folder that will contain the patch files. On older versions of SQL Server, this could comprise the latest Service Pack, Cumulative Updates, as well as additional hotfixes you may wish to apply. For instance, as of this writing, SQL Server 2016 requires Service Pack 2, Cumulative Update 3 for Service Pack 2, and two more hotfixes to bring it up to date. We would have to have all four files in this folder.
The actual path does not matter as long as we keep track of where they are. For the purposes of this post, we will assume they are stored in C:\Temp.

Definitely a good idea if you’re installing SQL Server regularly.

Comments closed

Whither Running Kafka On Kubernetes

Published 2018-10-25 by Kevin Feasel

Gwen Shapira walks through some of the costs and benefits of using Kubernetes to host your Apache Kafka brokers:

First, if you are running most of your other applications and microservices on Kubernetes, it becomes the organizational path of least resistance. This is just like how organizations who standardized on VMs have found it very difficult to allocate physical machines with local disks for Kafka.
I see situations with larger organizations where deploying Kafka outside of Kubernetes causes significant organizational headache that involves many approvals. When this is the case, I usually say that this isn’t a good hill to die on. It is possible to run Kafka on Kubernetes, so just do it. You’ll get your environment allocated faster and will be able to use your time to do productive work rather than fight an organizational battle.
And if things go wrong, you’ll get much better service from your internal infrastructure teams, because you’ll be running in an environment that is familiar to them.

Read on for more benefits as well as a few drawbacks.

Comments closed

Azure SQL Managed Instance Prerequisites

Published 2018-10-25 by Kevin Feasel

Frank Gill has started a series on Azure SQL Managed Instances and has two posts up already. First, an introduction:

The drawbacks of Azure SQL Database make it difficult to migrate existing applications, because of the number of application changes required. Azure SQL Database is designed to be used for new development in Azure and for multi-tenant environments, where each tenant requires their own copy of a database.
The benefits of SQL Server on an Azure VM make it much easier to migrate an existing application to Azure. However, the VMs underlying the application still have to be managed by the client. This fails to take advantage of the management of resources in Azure, and uses Azure as a VM host.
A third option, Azure SQL Managed Instance, was released at the beginning of October 2018. Managed Instance combines the best of the previous options. With Managed Instance, the infrastructure is fully managed and the majority of the SQL Server feature set is available. The full list of differences between a traditional install of SQL Server and Managed Instance can be found here. A number of the most dramatic differences are listed below.

Then a post covering pre-requisites:

Before creating an Azure SQL Managed Instance, a number of prerequisite resources must be provisioned. These are:

An Azure Virtual Network
A dedicated subnet for Managed Instances
A route table

It looks like this is part of a longer series Frank is building out, so stay tuned.

Comments closed

Reading Error Logs Outside Of SQL Server

Published 2018-10-24 by Kevin Feasel

Kenneth Fisher shows us where error logs are located and how to read them outside of SQL Server:

Quick and easy post today. Hopefully you’ve opened the error log on a SQL instance. However, what happens if you don’t have the log viewer in SSMS? In fact, the instance won’t come up at all so you really need to see what went wrong.
Fortunately, the error logs in SQL are just text files, even though they don’t actually have that extension. The trick is knowing where they are.

Read on for the answer.

Comments closed

Beautiful Deadlock Graphs And Tying RIDs Back To Object Names

Published 2018-10-24 by Kevin Feasel

Josh Simar shares a deadlock graph which I have entitled The Pit Of Despair:

I can’t make heads or tails of that but I can tell you that seems like a really bad brawl for resources. It’s like a Jerry Springer show with a few extras thrown in. Since I knew that my graph wasn’t going to be helpful in this instance I went to the actual xml and tried to figure out how I could tune this to make it better in the future. I needed to know exactly where the issue was so the waitresource pointer is a good place to start.
You will see many blog articles on how to find SQL wait resources when the resource type is a key, a page, or an object (I suggest Kendra Little’s blog post) There is however a noticeable glut on articles explaining RID (a RID is a key on a table with no clustered index). I finally found how to tie a RID to an actual resource name but it was used for corruption so the details were a bit hazy at first.

Click through for this work of database art as well as a script which links RIDs back to specific object names.

Comments closed