SQL Server with Mr. Denny


May 20, 2015  4:00 PM

India Here I Come

Denny Cherry Denny Cherry Profile: Denny Cherry
IT conferences, SQL, SQL Server

I’m thrilled to report that I’ll be presenting at the first SQL Server conference in India later this year. The conference organizer Amit Bansal made the official announcement last week. A new conference is a huge undertaking for the organizers, and bringing in speakers from outside the US is a huge commitment as international airfare isn’t cheap.

The conference is this August 27th-29th in Bangalore, India and includes a day of precons, which I’ll be delivering one of. The event team is currently putting together a survey for the local community to see what precon session they’d like to see the most, so once the results of that survey are in I’ll know what session I’m presenting.

I look forward to seeing India for the first time and I look forward to meeting new people at the conference.

Once I’ve presented my first session I’ll have officially presented on four continents (North and South America, Europe and now Asia). Only three more to go (granted one of them is a lot harder than the other two.

Denny

May 15, 2015  8:45 PM

Recommended reading from mrdenny for May 15, 2015

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: MilosSQL also known as HP

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


May 13, 2015  4:00 PM

Know Your Monitoring Scripts

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

If you are running monitoring scripts against your SQL Server database you need to know what those scripts do. As even a pretty basic monitoring script can cause some pretty big problems. Recently I was called to look at a large SQL Server who’s CPU had run up to almost 100% and users were getting huge numbers of timeouts.

Looking at the server using sp_whoisactive I could see massive waits in tempdb all on PAGELATCH wait types. Looking at sys.dm_exec_requests I could see that everything that was waiting was waiting on page 2:1:128. This was a pretty strange page ID for everything to be waiting on. Do I busted out DBCC PAGE and looked at the headers for the page. This page belongs to the system object sysobjvalues which stores meta-data about tables. The only reason that one of these system objects like this should be locked is if there’s an open transaction which created an object and never rolled back.

So I switched to the tempdb database and ran DBCC OPENTRAN. This showed a user transaction had been hanging around for about 90 minutes (it took a while for me to get the call and get VPNed in) and this session belonged to a user not the application. They checked with the user and he didn’t have any sessions running in SSMS.

So I ran DBCC INPUTBUFFER for the session with the open transaction and saw a query which was querying against various DMVs. So at some point this query window was used to begin a transaction, then create a temp table then do stuff (either with the temp table or without it). The transaction was never rolled back or committed so SQL hung onto it’s locks on those system objects, effectively preventing the application from creating temp tables or other temporary objects.

As soon as I killed this session, which forced it to roll back the application processes were able to begin flowing through the system again and everything got back to normal within about a minute or so.

So it just goes to show that you don’t have to be running some complex query against the SQL Server for all hell to break loose. One open transaction with a few small locks taken in just the right place can cause some serious problems pretty fast.

Denny


May 8, 2015  11:22 PM

Recommended reading from mrdenny for May 08, 2015

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server

Microsoft Ignite was great, but I still found the time this week to find some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: murilocmiranda also known as Murilo Miranda

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


May 6, 2015  6:00 PM

Should I use Backup Compression or NTFS Compression for Backups?

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL

There’s a few different ways to compress your database backups when backing them up to disk. The two easiest ones are database backup compression (using the COMPRESSION parameter in the BACKUP DATABASE command) or turning on NTFS compression on the folder. Recently I was working with a client doing a database migration and their old scheme used NTFS compression while I was doing SQL backup compression on the server that they were migrated to.

So this sounded like a perfect time to do a little testing while other backups and restores were running.

The backup files that I’m playing with are from a SQL 2014 database. The database was backed up to a database backup file using SQL compression, then to a database backup file without using compression. Then the uncompressed backup file was copied to the old Windows 2008 R2 server to see how well NTFS handled the compression.

The database it self is about 4768.63 MB in size with 1180.09 MB free space within the database.

The SQL compressed database backup was about 38 Megs give or take. The SQL uncompressed database backup was about 3.5 Gigs which is basically what you’d expect as that’s how much data is in the database.

sql_backups

So the SQL Backup Compression here is pretty damn good. So taking those same files and copying them to a folder which has NTFS compression we see that uncompressed backup has been compressed to 1.28 Gigs while the compressed backup has been compressed to 38 Megs (NTFS compression saves all of 512 bytes according to the screenshot below).

test

test_compressed

So if you are looking for a way to store your backups in a compressed format, SQL Backup Compression is going to be the way to go. The good news is that SQL Backup Compression was first introduced in SQL Server 2008 but as an Enterprise Edition feature. It was moved into the standard edition in SQL Server 2008 R2 so almost every production SQL Server out there should be able to use it (or at least most of them).

Denny

(As always when looking at compression metrics from someone, your mileage may vary depending on the kind of data in the database, the alignment of the moon, the color of the sky, etc.)


April 29, 2015  4:00 PM

Stop Touching the Public Database Role

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server, SQL Server security

Odds are you don’t need to make permissions changes to the public database role. Back in the SQL 7 days making changes to it was pretty common as the database engine wasn’t all that well secured. Today in the SQL 2005 and newer times there’s no reason to change any of the default permissions that Microsoft has setup.

I’ve seen auditor requirements to remove access to things like xp_instance_regread as this could potentially access things in the registry that an attacker could use. I suppose this is true, if you haven’t setup the startup accounts correctly. If SQL is running under an account which is a member of the local Administrators group then yes someone could read registry keys which they shouldn’t have access to. But there also shouldn’t be anything all that sensitive written to the registry on a production SQL Server. SQL puts basically nothing in there other than a few startup parameters and there shouldn’t be really anything else installed on the system. Anything sensitive that Windows writes to the registry is going to be encrypted.

Now what’ll break by changing these permissions could be just about anything. I know that if users don’t have access to run xp_instance_regread for example they won’t be able to use SSMS as they’ll get an error when they connect to the instance. So to make the error go away you now have to grant the EXECUTE right to xp_instance_regread for every user that connects to the database engine with SSMS (which is probably most of them). This means that removing it from public was meaningless as it’s still granted to everyone that connects, just via their login.

Now I know that it’s a lot easier for me to write about this than it is for you to defend leaving it to an auditor or to management, but hopefully you can get logic to prevail. So unless you really want to do a lot of security related troubleshooting that you shouldn’t need to do, don’t be messing with the public security permissions.

Denny


April 22, 2015  4:00 PM

Take Your Time With Azure Site to Site VPN Configurations

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server, VPN, Windows Azure

Setting up Azure Site to Site VPN Endpoints can be a real treat sometimes. Recently I was setting up a site to site VPN between two Azure sites. One in the US West data center and the other was in the West Europe data center.

Now the annoyance when setting up a site to site VPN between two Azure sites is that you have to do some stuff in the Azure Portal (manage.windowsazure.com) and some stuff through PowerShell. You have to do this because (as of when I’m writing this) you have to create the dynamic routing gateway in the portal because PowerShell only supports creating a static routing gateway and you have to change the pre-shared key in PowerShell because the “Manage Key” button in PowerShell only supports showing you the key but not changing it.

Well when I was trying to get this setup I was trying to get it done as quickly as possible as I had other stuff to focus on that day. And apparently I wasn’t waiting long enough for the commands which the UI was running to actually finish before trying to run the PowerShell cmdlet Set-AzureVNetGatewayKey to change the pre-shared key. This then ended up causing problems for the commands which the UI had kicked off and the Gateway would only be half created and wouldn’t show up correctly, so I’d drop the gateway and try again.

Long story short I ended up starting the gateway creation then went to dinner. When I came back everything was done, and I could change the pre-shared key and get everything back up and running again without issue.

When creating site to site VPN links between Azure take your time. You’ll spend less time if you slow down than you would otherwise.

Denny


April 17, 2015  6:00 PM

Recommended reading from mrdenny for April 17, 2015

Denny Cherry Denny Cherry Profile: Denny Cherry
Apple, SQL, SQL Azure, SQL Server, SQL Server Integration Services

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: PASSAppDev also known as PASS AppDev VC

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


April 15, 2015  4:00 PM

Where to find the Azure PowerShell SDK ToolKit

Denny Cherry Denny Cherry Profile: Denny Cherry
download, downloading, Powershell, SQL, SQL Azure, SQL Server, Windows Azure

If you do basically any work with Azure you’ll need to get the Azure PowerShell ToolKit at some point. Now finding the Azure PowerShell SDK Download page is a royal pain. So to make life easier I’ve setup a quick redirection that will get you to the correct place. Simply browse to www.getazureposh.com and you’ll be redirected to the gitHub download page for the Azure PowerShell SDK.

The most recent build is on the top. Use that one.

You are welcome.

Denny


April 10, 2015  4:00 PM

Recommended reading from mrdenny for April 10, 2015

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL, SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: _dbassassin also known as Tim M. Hidalgo

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: