When I am writing my own code using a .NET (or other) language, I have a great deal of control of how the connection string that my application uses to connect to SQL Server is configured.
In particular, I might need to add another parameter or two.
As a simple example, you might have a multi-subnet Availability Group, spread across a production site and a disaster recovery site. It's common to then have an Availability Group Listener in both subnets.
If you add the parameter MultiSubnetFailover=true to your connection string, when SQL Server attempts to connect to the listener, it will send a request to each IP address concurrently, not just to one at a time. It will then connect to whichever server responds first.
This is great, but how do we do that with SQL Server Management Studio (SSMS) connections?
The answer is that in the database server connection dialog, we can choose Options:
In the dialog that appears, there is a tab for Additional Connection Parameters:
On that tab, I can enter the required details:
Note also that if you enter a value here that is also on the graphical connection pages, the value that you enter overrides those values.
The PRINT statement in SQL Server's T-SQL language is useful but one of the biggest restrictions with it is the size of the strings that it can print. Where this becomes a big issue is if you are needing to create dynamic SQL statements (which you obviously need to be careful of in the first place) or scripting database objects, and the statements need to be either executed or printed.
In our free SDU Tools for developers and DBAs, we added a procedure ExecuteOrPrint to help with this. You can pass it a large string (nvarchar(max) typically) and tell it to either execute the value, or if you are using it for scripting or debugging, to print the value.
The default action is to print the value.
We designed it to help with both scenarios in the same code. For example, in scripting, you might want batch separators (ie: GO) but when executing, you don't want to send those to the server, you want to carve up the script and send it in batches, based upon the separator. It can also add carriage returns and line feeds as required.
The parameters are as follows:
@StringToExecuteOrPrint nvarchar(max) -> String containing SQL commands
@PrintOnly bit = 1 -> If set to 1 commands are printed only not executed
@NumberOfCrLfBeforeGO int = 0 -> Number of carriage return linefeeds added before the batch separator (normally GO)
@IncludeGO bit = 0 -> If 1 the batch separator (normally GO) will be added
@NumberOfCrLfAfterGO int = 0 -> Number of carriage return linefeeds added after the batch separator (normally GO)
@BatchSeparator nvarchar(20) = N'GO' -> Batch separator to use (defaults to GO)
You can also see it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
Choosing the right database permission can be hard. I've lost count of the number of times I've heard a discussion like this:
I need to let Mary restore truncate one of the tables but I don't want to give her permission to do it, in case she stuffs it up.
or
I need to let Paul restore this database but I don't want him to be able to restore other databases, and I'm worried if I give him the permission, he might accidentally do something bad and I'll be blamed for it.
Whenever you have this type of discussion, the problem is that you're looking to give the user a permission, but only in a very limited situation and the DCL (data control language) statements (ie: GRANT, DENY, REVOKE) are too coarse for what you're trying to do.
Instead, what you need to do is to create a stored procedure, to give the stored procedure the permission to do what's needed, and then just give the user permission to execute the stored procedure.
There are two basic ways to do this.
The first is to create the stored procedure with a WITH EXECUTE AS clause. For example, I if write this:
CREATE PROCEDURE Utility.DoSomethingPotentiallyScary
WITH EXECUTE AS OWNER
AS
…
then whatever the procedure does is executed as the owner, not as the user. And this includes any dynamic SQL code. It's documented here. For stored procedures, instead of OWNER, you can also have CALLER (that's the default anyway), SELF (ie: the person creating the procedure), or a specific user.
To create or alter a procedure to execute as someone else, you need to have IMPERSONATE permission on that user. (That's already there if you're an admin).
That's a pretty simple solution but it has a few limitations.
For trickier scenarios (such as some cross-database scenarios), you can do this instead:
Create a certificate
Create a user from that certificate
Add the required permissions to that special user
Digitally sign the stored procedure with the certificate
Now when the stored procedure runs, it will acquire the permissions associated with that certificate, but only while it runs. An added bonus is that if the stored procedure is changed in any way, the digital signature is removed, along with the permissions.
Hi Folks, we're looking forward to doing a number of presentations across New Zealand starting around the end of this month.
Aug 27th (Mon): SQL Server User Group – Wellington (Things I wish Developers knew about SQL Server)
Details here
Aug 28th (Tue): Data Management and Analytics Meetup – Wellington (A Comprehensive Look at What's New in SQL Server 2017 – and ongoing product directions)
Details here
Aug 31st (Fri): Pre-conference Day for SQL Saturday – Auckland (Developing SQL Server Applications that Perform)
Register here
Sep 1st (Sat): SQL Saturday – Auckland (Keynote)
Register here
Sep 1st (Sat): SQL Saturday – Auckland (Database on a diet – taming a large database)
Register here
Sep 3rd (Mon): SQL SERVER & Data Management User Group – Christchurch (Things I wish Developers knew about SQL Server)
Details here
Sept 6th (Thu): Venue TBA – Dunedin (Database on a diet – taming a large database)
Would really love to see you at any of them. Please come and say hi.
Last week, I discussed the meaning of simplified vs traditional Chinese characters. I had discussed the differences in them, and pointed out that in most sentences, there are only a few characters that are different between the character sets.
So, it would seem that the obvious question is why we can't then just simply translate between the two character sets.
Ironically, it is the simplification process itself that has made this difficult.
It is quite easy to have a computer translate traditional Chinese characters to simplified ones. The problem is the reverse.
The first reason that this is a problem is that in some cases, more than one traditional character was mapped to the same simplified character. Let's see an example:
Each of these four characters:
Traditional characters
was translated to this character:
Simplified character
as you can see in the main image above this post.
So when you need to translate back the other way, which character do you translate it to?
The answer is that you need context, and that's where over time, computers will get better and better than humans at doing this, but not quite yet. Here's another example:
Translate to traditional
This one is easy for the system as it knows that Táifēng (a typhoon) is a specific thing and knows which character to use.
A second part of the challenge though is also shown in the example above. Note that the name Táifēng is somewhat similar to the English word typhoon. That's no accident. It's what's called a 通假 (or Tōngjiǎ) which is called a loan word, based on phonetics, not on the meaning of the characters directly.
Loan words are very difficult to translate back to traditional characters because the only context is the loan word itself. These groups of characters often have little meaning by themselves.
For example, my name Greg is often written like this:
Greg translated
But now look at the meaning of the individual characters:
Components of Greg's name
Note that "grid, mine, grid" isn't particularly meaningful on its own. It's only when the entire name is present, that Google Translate has any clue about what it means, and then it's only an "educated" guess.
As an interesting side note, it's also why a lot of westerners spend ages trying to find a suitable Chinese name, much the same way that I have Chinese friends who have chosen western names.
The most notable of these is probably Mark Rowswell (大山 or Dàshān) whose name means Big Mountain. That's more exciting than grid mine grid. If you'd like to see him telling an old Taiwanese joke (with subtitles), check this out:
I was especially interested to hear this one on Audible, given the author was also the narrator. I really wanted to hear him explain his view on the situation.
Generally, I'd avoid a book like this because I was assuming it would just be a self-apologetic or self-aggrandizing account of recent events. What I found though was very, very different.
I was genuinely surprised by how compelling I found the book to be.
Most of the book wasn't about the current Trump situation, etc. It was about his background, the New York mafia, and a lot of information about earlier investigations in the FBI.
I found the discussion around Martha Stewart particularly interesting. It seems that many times when people are brought in for questioning, that if they had just told the truth, the outcome for them might have been quite minor, but lying in the interviews is where things start to go very, very wrong for them.
I've heard a lot of people who support the current US president maligning Comey but I was left wondering that if they'd read this book (or listened to it), if they'd have either moderated or changed views.
US politics seems irredeemably polarized but regardless of your political leaning, given the background coverage in this book, and the timeliness of its later content, I'd suggest that it's worth your while reading or listening to it, before forming any further opinion on it or him.
What can I say? I found it fascinating and I wasn't expecting to.
Greg's rating: 9 out of 10
Note: as an Amazon Associate I earn from qualifying purchases but whether or not I recommend a book is unrelated to this. One day it might just help cover some of my site costs. (But given the rate, that's not really likely anyway 🙂
In previous posts, I've been talking about how to use snippets in SQL Server Management Studio (SSMS) and how to create your own. There are several types of snippets and one of the special types of snippets that I want to mention are the "surround with" snippets.
If you look at the following block of code:
Imagine that you want to execute the four highlighted lines only when a condition is true. If I hit Ctrl-K and Ctrl-S while they are highlighted, I'm prompted with this:
Note that I get an option to surround them with a BEGIN/END, or an IF or WHILE. Let's choose an IF. I just double-click the If option and this happens:
We can then just type the condition. Notice that because I was using multiple lines, it put them all in a BEGIN/END block for me.
This is all quite good but I still might want to create my own instead, and I can do that. An example of why I might want to do that, is that I might want a statement terminator after the END.
If we go into the Code Snippets Manager (from the Tools menu), and expand the Function category, we can see this:
I can't say that I think of IF as a function but none-the-less, note that the type is a SurroundsWith snippet. Let's see how it's defined. The Location is here:
You can execute SQL Server Agent jobs from within T-SQL code. The procedure sp_start_job can do that.
That's all well and good but notice that it doesn't say "execute job"; it just says "start job". The command starts the execution of a job but has no interest in when it completes.
Sometimes, you need to be able to start a job and wait for it to complete before taking a following action.
In our free SDU Tools for developers and DBAs, we added a procedure ExecuteJobAndWaitForCompletion to help with this.
In the main image above, you can see its default action ie: execute a job and wait for it to complete.
We have some other optional parameters though. In the screenshot below, you can see a parameter for determining how long to wait for the job to start, how long to then wait for it to complete, and whether or not to output debugging information:
You can also see it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
Just a reminder that as well as the pre-conference day and the keynote, I'll be presenting my Database on a Diet session at SQL Saturday in Auckland. I regularly work with some pretty sizable databases at tier-one financial (and other) organisations and they all struggle with the size of their databases. It's easy for the size of a large database to get out of control.
In the example in this session, I'll describe the techniques I've used to reduce an operating 3.6TB database to just over 500GB, while increasing its performance. The session will include a deep dive into compression and other related techniques within SQL Server.
Anyone who's ever traced activity against a SQL Server will have no doubt seen a large number of commands where the procedure sp_reset_connection has been executed. Yet, this command won't appear anywhere in the source code of the applications that are running.
As an example of why this occurs, one of the most common data access technologies that is used to connect applications to SQL Server is ADO.NET. It has a SqlConnection object that represents a connection that can be opened to a SQL Server instance. In the design of the SqlConnection class, the architects of it were grappling with two big issues:
They knew that opening and closing connections to SQL Server was a relatively expensive process.
They also knew that on a busy website, they didn't want to use enough resources (or might not even have had them), to open up a connection for each concurrent session on the website.
So they decided to make the connections to SQL Server able to be shared. By default, when you execute the Close() method of the SqlConnection, the underlying connection doesn't get closed, it just gets added back into a shared set of connections known as the connection pool. And when the Open() method is executed, it first checks if there is an available connection in the pool and provides it, before going to create an actual connection.
By default, the pool allows for 100 shared connections.
(It's actually a little more complex than this because it depends upon whether the connections are the same ie: shareable in the first place. As a simple example, they'd all need to use the same credentials or a separate pool is needed).
One of the challenges though, is that SQL Server has settings that can be configured at the session level. You don't want a new Open() picking up session settings from the previous user of the connection. Sessions can also have state. You don't want a new Open() picking up an uncommitted transaction from the previous user of the connection either.
SQL Server provides a procedure sp_reset_connection that is designed to reset the settings and the state. So when you use connection pooling (the default), every time you open a connection, ADO.NET executes sp_reset_connection to make sure you get a "clean" connection before it hands it over to you.
Is it perfect?
No but it's pretty good. There have been versions where things like transaction isolation levels were not reset, when everything else was. (That's fixed in current versions).
Does it incur an overhead?
Yes. A whole bunch of code gets executed internally when this is run and obviously that's an overhead. But nowadays, the bigger concern is the latency for the round trip over the network. Back when your DB server might have been on the same system, this wasn't so much of an issue, but when your DB server might be somewhere in the cloud, over a potentially higher latency connection, this is more of an issue.
Can I avoid it?
Sure. You can set Pooling=False as an option in your connection string. It's also possible to change the pool size there too.
Should I avoid it?
That's a tough question. In general, for most web apps, the simple answer is no. There's a good reason why it was added. Opening and closing actual connections is still a relatively expensive pair of operations. But there are other types of applications where there really isn't a need for a connection pool and its associated overhead.
If you are performing tracing on SQL Server workloads, chances are high that you won't have control over that, and you'll probably want to just ignore executions of sp_reset_connection.
In the LoadPerformanceTuningTrace procedure in our free SDU Tools for DBAs and Developers, we have a parameter @IgnoreSPReset that does just that.
