SQL Server with Mr. Denny


January 11, 2017  6:00 PM

Should I back up system databases on an Azure VM?

Denny Cherry Denny Cherry Profile: Denny Cherry
Graphic with a back up button. Because we can't restore with out backups.

https://www.flickr.com/photos/sonofgroucho/3344527949/

Well that really depends. When your database VM fails for some reason (either someone does something bad like delete the VM by accident, or a Windows patch doesn’t install correctly and the VM won’t start correctly, etc.) do you care about having your logins on the server, your jobs, any SSIS packages deployed to MSDB, any operators, etc. restored to the system. Or do you want to have to redeploy all those things after you rebuild the server and get SQL set back up? If you aren’t OK with redeploying those (or the risk of those deployments is to high) and all the risks that go with that, then doing a restore of those system databases is going to be the way to go.  (Hint, a restore is always going to be more reliable than a redeployment.)

Now I know with AlwaysOn Availability Groups you can just fail over to another node, but at some point you need to rebuild that failed node.

What if you don’t have Availability Groups in place. Maybe it’s a tier 2 server that doesn’t have HA beyond what Azure offers, or maybe it’s a failover cluster so you don’t have multiple copies of the system databases.

Now I get that restoring master isn’t as easy as a user database, but it’s actually pretty easy to restore. It just requires restarting the SQL software in single user mode with a couple of switches (SQLServer.exe -c -f -m) from the command line, then you have to login using another command line window to actually restore the database using sqlcmd and the RESTORE DATABASE command to actually restore master. Restoring msdb is easier as all you have to do is stop the SQL Server Agent (any anything else using msdb) then restore the msdb database like normal.

If I can easily describe how to restore the two databases in a single paragraph then it shouldn’t be all that hard for someone to do.

Now the cost. Your system databases should be small. If you’ve got pruning setup correctly in your msdb database then the backups (which you should be writing to RA-GRS, Readable Geo-Redundant Storage using the Backup to URL feature of SQL Server) should be a few megs per day. Assuming you keep them for 10 days (which is probably more than most people do) and we’ll assume 1 Gig of space needed for backups to make the math easier you’re talking about $1.20 per month to store the backups. If you kept the backups for 31 days then the cost is $3.72 per month for the system backups. (Assuming you don’t have an EA, and you are paying the full retail price, which basically no one should be.)

I’ve shown that system databases are easy to restore.

I’ve shown that they don’t cost much to backup (if you aren’t paying attention, the cost to store these backups is a rounding error in the cost of the SQL VM).

So what’s your excuse now for not backing them the system databases?

Denny

January 4, 2017  6:00 PM

SQL Server for the New or Non-dba – Nashville Edition Reminder

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

I wanted to throw out a reminder that I’ll be giving a pre-con at Nashville SQL Saturday 2017. I know that the announcement for it was right before the holidays and things can get lost around the holidays pretty easily. So with that…

I’ll be presenting a pre-con named SQL Server for the New or Non-dba on January 13th, 2017 at 8am CST.sqlsat581_header

In this all day session on Microsoft SQL Server we will be learning about how Microsoft SQL Server works and what needs to be done to keep it up and running smoothly when you don’t have a full time database administrator on staff to help you keep it running.

In this session we will cover a variety of topics including backups, upgrade paths, indexing, database maintenance, database corruption, patching, virtualization, disk configurations, high availability, database security, database mail, anti-viruses, scheduled jobs, and much, much more.

After taking this full day session on SQL Server you’ll be prepared to take the information that we go over and get back to the office, get the SQL Server’s patched and properly configured so that they run without giving you problems for years to come.

Be sure to go register for the pre-con, as registration is required.

See you at SQL Saturday Nashville.

Denny


December 28, 2016  12:34 AM

2016 By The Numbers

Denny Cherry Denny Cherry Profile: Denny Cherry
https://www.flickr.com/photos/oskay/8668965783

https://www.flickr.com/photos/oskay/8668965783

Well 2016 was a hell of a year for blogging. I recorded just under 285k page views (284,553 as of writing this the afternoon of December 27th), which is up about 40k or so from 2015.

This years top 10 posts look a bit different from last years.  There’s actually 7 repeats but the top two were written in SQL Server 2016.

  1. Fixing a storage pool that doesn’t have an read-write server
  2. SQL Server on Linux?!?!?!
  3. What exactly is MSDTC, any when do I need it? (Number 2 in 2014, Number 2 in 2015)
  4. SQL Server 2014 Licensing (Number 1 in 2015)
  5. Big Announcements in SQL Server 2016
  6. Difference Between An Index and a Primary Key (Number 3 in 2015)
  7. What Does WITH (NOLOCK) Actually Mean (Number 5 in 2015)
  8. How To Configure MSDTC On Windows 2008 (Number 1 in 2014, Number 4 in 2015)
  9. The Process Could Not Execute sp_replcmds on x (Number 7 in 2015)
  10. Calculating Fibre Latency Correctly Is a Must When Trying To Determine Storage Performance Numbers (Number 6 in 2015)

This year was another first for my blogging. I had a single post this year hit not just 20k page views, but 30k page views.  That’d be #1 on this list which topped out at 30,749 page views (plus a few more in the last 3 working days of the year).  Number 2 up there had 14,337 while the MSDTC post had 11,643.

I review these numbers every year (I actually look at them through out the year as well) because it gives me some insight into what people are looking for. New posts obviously get some traffic (especially when I’m able to get blog posts written and posted within minutes of the announcement) but I find it interesting that a lot of these older posts continue to get a lot of traffic, like the MSDTC post up there which has been a top three post by page views for 3 years in a row.  Not bad for 15+ year old technology or a 3 year old blog post.

So based on this multi-year trending of data, I need to keep writing about random errors that I get when working on clients systems, and keep blogging about Microsoft announcements 5 seconds after they are announced.  That was pretty much my plan for 2017 anyway. 🙂

Don’t forget to check out DCAC’s Ascending to Azure webcast on January 5th, 2017 where we’ll talk about some of the successful migrations we’ve done from on-premises to Microsoft Azure.

Denny


December 21, 2016  4:00 PM

SQL Server for the New or Non-dba – Nashville Edition

Denny Cherry Denny Cherry Profile: Denny Cherry

I’m thrilled to be able to say that I’ll be presenting a pre-con at the Nashville 2017 SQL Saturday. I’ll be presenting a pre-con named SQL Server for the New or Non-dba on January 13th, 2017 at 8am CST.sqlsat581_header

In this all day session on Microsoft SQL Server we will be learning about how Microsoft SQL Server works and what needs to be done to keep it up and running smoothly when you don’t have a full time database administrator on staff to help you keep it running.

In this session we will cover a variety of topics including backups, upgrade paths, indexing, database maintenance, database corruption, patching, virtualization, disk configurations, high availability, database security, database mail, anti-viruses, scheduled jobs, and much, much more.

After taking this full day session on SQL Server you’ll be prepared to take the information that we go over and get back to the office, get the SQL Server’s patched and properly configured so that they run without giving you problems for years to come.

Be sure to go register for the pre-con, as registration is required.

See you at SQL Saturday Nashville.

Denny


December 14, 2016  2:00 PM

Ascending to Azure

Denny Cherry Denny Cherry Profile: Denny Cherry
Azure

I’m thrilled to announce that we’ll be presenting a free webinar called Ascending to Azure on January 5th, 2017 at 10am PCloudST / 1pm EST. During this webcast the Denny Cherry & Associates Consulting team will review some of the challenges that we’ve seen with moving customer workloads to Azure and how we’ve been able to work through those challenges.

We’ll also talk about one of the migrations that DCAC did where we moved the customers entire on-prem environment to Azure. SQL, web servers, domain controllers, everything. Leaving basically no servers on-prem giving the client the maximum flexibility for scale.

Want to attend? No problem. The webcast is free, you just need to register, save the calendar entry to your computer and show up on January 5th. Pretty simple. So go register now.

Denny

“>


December 9, 2016  10:16 PM

Recommended reading from mrdenny for December 9, 2016

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

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.

Hopefully you find these articles as useful as I did. Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


December 7, 2016  4:00 PM

Settings of the Query Store GUI

Denny Cherry Denny Cherry Profile: Denny Cherry

The SQL Server Query Store which was introduced in Azure about a year ago or so, and came to the on-premises version of SQL Server in SQL Server 2016. This is a fantastic tool which can help you find performance problems. However when looking at the query store it may not be showing you the data that you are looking for. This is because the Query Store GUI doesn’t know what time window you want it to look at by default, so it’s using the settings which Microsoft programmed in as the defaults. Changing this to get a better view of the data that you have stored within the Query Store is just a matter of changing the settings within the Query Store GUI.

https://www.flickr.com/photos/mezuni/475143348/in/

https://www.flickr.com/photos/mezuni/475143348/in/

With whatever Query Store report you are looking at within Management Studio in the upper right of the window there’s a “Configure” button.  If you click that it’ll give you the various options for the report that you are looking at.  What those options are will depend on the Query Store report that you are looking at.  The settings I end up changing the most usually fall into the “Time Interval” category so that I can change the time windows that the Query Store is querystoreconfiguredisplaying data from.

For example on the Regressed Queries report Management Studio will default to showing queries from the last hour that have regressed from what they were over the last month. However if I’m looking at a customers system I may want to see the queries that have changed in the last week compared to the last few months.  This is where I’d set those settings.

Once done click OK, and you’ll get updated reports with the data that you are looking for.

Nice and easy.

Denny


December 4, 2016  11:03 PM

Recommended reading from mrdenny for December 2, 2016

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

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.

Hopefully you find these articles as useful as I did. Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


November 30, 2016  4:00 PM

My VMware AirWatch Transaction Log File Keeps Growing

Denny Cherry Denny Cherry Profile: Denny Cherry

Anything the uses a database needs to have the database settings configured correctly, this includes all the VMware tools.  I’ve had a couple of customers contact me recently about VMware’s AirWatch system because the transaction log on the database keeps growiairwatchng.  The reason in both cases so far is because the database is using the default settings and the transaction log was never backed up.  I talked about this at Microsoft’s TechEd conference a couple of years ago during my talk titled “Introduction to SQL Server Management for the Non-DBA”.  If you prefer a VMware themed slide deck, I’ve given basically the same presentation at VMworld as well (registration required).

Fixing this is actually a really simple fix.  You need to do one of two things.

  1. Change the recovery model from FULL to SIMPLE.
  2. Start taking transaction log backups of the database

Now, how do you select the one of these that you want to do?  That’s pretty easy.  Do you need point in time recover-ability of the database?  If the answer to that question is yes, then select option 2.  If the answer to that question is no, then select option 1.

With most AirWatch systems you don’t really care if the database is restored to what it looked like yesterday or 5 minutes ago, so you usually don’t need point in time recovery.  So just change the database from FULL to SIMPLE and you’re good to go.  Doing this is actually really easy.  Connect to the SQL server in SQL Server Management Studio.  Right click on the database and select properties.  Change the recovery model from FULL to SIMPLE, then click OK. That’s it the log won’t grow any more.

If you need to reclaim the space because the disk is full, then you need to shrink the log file.  To do this, click the “New Query” button at the top of management studio and run the following command.

DBCC SHRINKFILE (2, 1024)

That’ll shrink the file down to 1 Gig and it “shouldn’t” ever grow any larger than that. There’s no outage to doing this, and no risk to the system.  Because of the way that shrinkfile works you may need to run it a couple of times over a couple of days in order to actually get all the space back.

If you don’t have SQL Server Management Studio, or you can’t find the properties, here’s a script that’ll do everything that I’ve described.  Just run this in the AirWatch database (it could be named anything so you have to change the database name in the USE statement from YourDatabaseName to whatever the name of your AirWatch database is).

USE YourDatabaseName
GO
declare @dbname sysname
set @dbname = db_name()

declare @sql nvarchar(1000)
set @sql = 'ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE'

exec (@sql)
GO
CHECKPOINT
GO
CHECKPOINT
GO
DBCC SHRINKFILE (2, 1024)
GO

Denny


November 23, 2016  4:00 PM

Why Are Nested Views Horrible?

Denny Cherry Denny Cherry Profile: Denny Cherry
https://www.flickr.com/photos/1024/3407840726/

https://www.flickr.com/photos/1024/3407840726/

Nesting views in SQL Server by itself probably won’t introduce performance issues. However when it will do is make it MUCH harder to find performance problems. When you have the top view calling 3 views, which each call 3 views you suddenly have a nightmare of views to unwind to resolve the performance issues.

There’s another dirty little secret of views. Even if you aren’t accessing columns from all the tables in the views which are being referenced, SQL Server still needs to query those columns. This increases IO for your queries, for basically no reason. Because of this reason alone it often makes sense to not use views, and specifically nested views.

Now there are plenty of reasons to use views in applications, however views shouldn’t be the default way of building applications because they do have this potential problems.

While working with a client the other week we had to unwind some massive nest views. Several of these views were nested 5 and 6 levels deep with multiple views being referenced by each view. When queries would run they would take minutes to execute instead of the milliseconds that they should be running in. The problems that needed to be fixed were all indexed based, but because of the massive number of views that needed to be reviewed it took almost a day to tune the single query.

Eventually the query was tuned, and the query was able to be run in about 200ms, but the cost to the customer was quite high.

Use views when they make sense, but don’t use them every time. Like everything else in SQL Server, there is no one right answer for every problem. They are one solution for a subset of problems, not the end all solution.

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: