SQL Server with Mr. Denny


January 24, 2017  4:00 PM

Porn Mode is really handy for Azure

Denny Cherry Denny Cherry Profile: Denny Cherry
Azure

Working in Azure means mastering Private Browsing (aka. Porn Mode). This is especially true if you have multiple Azure Active Directory accounts (or at least several Microsoft Live accounts) that you need to log into accounts. On any standard day I usually have 2-3 different browsers open each running in both normal and porn mode so that I can be signed into several clients at once.

After working in Azure for a while you’ll find that you are a master of ++P (Internet Explorer and Firefox) and ++N (Chrome and Safari).

I wrote this blog post because this phrase came up during a client meeting, and the client bet me that I couldn’t write a blog post about it. So there. :p

Denny

P.S. Never bet me I can’t write a blog post about something stupid.

January 18, 2017  2:00 AM

Database Administration for the Non Database Administrator at SQL Bits

Denny Cherry Denny Cherry Profile: Denny Cherry
Database administration, DBA

SQL BitsAt SQL Bits this year I’ll be presenting my all day session Database Administration for the Non-DBA. This all day training day is a great session for those who work in shops where they have to function as the DBA, but their job isn’t to be the DBA (or someone who is brand new to being a DBA).  We’re going to talk about all the great things that you have to worry about as the DBA including backups, restores, corruption, performance tuning, indexing, virtualization, High Availability, Disaster Recovery, and much more.

If you are an accidental DBA, or are just getting into the DBA field then this is the training day event that you want to sign up for.  So stop waiting, and get registered (you select the training day during the normal registration process for SQL Bits).

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


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: