Spring 2017 SQLintersection

The Spring SQLintersection conference will be May 21st – May 24th. I attended my first SQLintersection conference Spring of 2015 and was blown away at how awesome a conference can be. There was an incredible lineup of speakers and sessions. What I found to be one of the best features of this conference is the attendee to speaker ratio. I have been to conferences where you have hundreds of attendees in each session, while the content of that session can be good, if you have any additional follow up questions, you couldn’t gain access to the speaker. At SQLintersection, the speakers were readily available and eager to share follow up answers to questions after their session.

Another great thing about SQLintersection is the ability to attend workshops. For the Spring conference there are two days of pre-conference workshops and one day of post conference workshops. I am fortunate to be presenting a full day pre-conference workshop on Azure as well as three general sessions.

If you are planning to attend the Spring 2017 SQLintersection conference, you can use the discount code RADNEY to save $50 off of registration.

The Spring 2017 conference has a stellar line-up of speakers to include (in alphabetical order)
Aaron BertrandSQL_Spr17_300x250_Radney
Tim Chapman
Jonathan Kehayias
Ben Miller
Brent Ozar
David Pless
Tim Radney
Paul Randal
Joe Sack
Shep Sheppard
Erin Stellato
Kimberly L. Tripp
Bob Ward
Buck Woody

Using the Azure SQL Database DTU Calculator

Over the past year I’ve been teaching and helping clients with Azure SQL Database. One of the most common questions I’m asked is, “What size tier do I need for my database?” I instinctively refer them to the DTU Calculator. This is a free tool that you can use to capture local instance metrics and upload to a website for an estimation of the service tier needed. The DTU calculator captures data on processor, disk reads and writes, and log bytes flushed.

Something to take into consideration about the DTU Calculator is that it collects the metrics for the entire instance. If you are looking to migrate a single database from an instance, then those metrics will be skewed. Depending on your situation, this may be fine, you’d know that the database in question would fit within the tier recommended by the DTU Calculator. If this is not acceptable, another option would be to isolate the database to it’s own instance, and then run the DTU Calculator against that instance.

Justin Henriksen, the creator of the DTU Calculator suggests that you could use SQL Server DMVs to isolate a database, but I personally have not tried this.

The DTU Calculator is offered in an executable with a configuration file and a PowerShell script. The two items I typically change are the location and name of my output file and the duration the script runs. The duration defaults to 3600 seconds, but when I’m conducting demos, I usually reduce this to 60 seconds. In the real world, I run the process for 1 hour increments, and continuously throughout the day so I get a full picture of the workload. I was excited to see the addition of the executable command line tool rather than just the PowerShell option.

If you’re looking to migrate multiple databases to Azure SQL Database, then an Elastic Database Pool is most likely a better option for you instead of managing multiple individual Azure SQL Database tiers. With an Elastic Database Pool, you’ll have a pool of eDTUs that each database can pull from (individual database limitation differences between Standard and Premium pools).

If you are considering Azure SQL Database, check out the DTU Calculator and if you need assistance migrating from On Premises to Azure or Azure SQL Database, let us know.

How to run Windows Server 2012 Hyper-V on VMware Workstation

I recently needed to create a new virtual machine, however with this virtual machine I needed to demo Windows Server 2012 with Hyper-V enabled. Since I run VMware Workstation as my hypervisor on my laptop, I needed to run a hypervisor within a hypervisor.

Installing Windows Server 2012 was no problem, however when I tried to add the Hyper-V server role I received an error

AddRoles1
Check Hyper-V and click next

AddRoles2
I then click Add Feature and…..

AddRoles3
This is where things failed. Hyper-V cannot be installed: A hypervisor is already running.

By default, you cannot install a hypervisor under another hypervisor. However there is a work around. First you to power down your VM and edit the .vmx file for that VM. You will need to add a two lines of code, save the file and then startup the VM.

hypervisor.cpuid.v0 = “FALSE”
mce.enable = “TRUE”

Edit_vmx_file

Once I edited the VM and restarted and tried to add the Hyper-V role, I received another error regarding my CPU type. Hyper-V cannot be installed: The processor does not have required virtualization capabilities.

Error

To correct this I needed to again power down the VM and edit the virtual machine settings, specifically, the processor values.

vmware_changes

Once I change the preferred mode to “Intel VT-x/EPT or AMD-V/RVI” and checked the box for “Virtualize Intel VT-x/EPT or AMD-V/RVI” I was able to successfully add the Hyper-V role to my VM and start provisioning additional VM’s under Hyper-V.

hyperv

If you experience any of these issues then I hope this post solves your issues like it did mine.

Configuring SIOS DataKeeper for multiple volume snapshots

Recently I had the opportunity to work with a client that was utilizing SIOS DataKeeper in a Windows Server Failover Cluster to create a readable secondary in order to offload reporting. SIOS DataKeeper is an application that synchronizes data between two volumes, allows you to present local storage to a WSFC as shared storage, and gives you the ability to take snapshots of the mirrored volumes.

Taking a snapshot will make the mirrored volume available to the OS on the secondary. You can then attach those databases to an instance of SQL Server and have your users to connect for reporting. This is all doable with Standard Edition of SQL Server which can save thousands of dollars on SQL Server licenses.

Installing and configuring DataKeeper to mirror volumes and adding them to the WSFC was very simple and well documented. To keep the snapshot current with the previous day’s data, I needed to schedule a process that would drop any existing snapshots and take a new snapshot. I did this by using the built in command line tool EMCmd. To take a snapshot of the E and G drive on my secondary server named AGNODE6, I needed to run

EMCmd AGNODE6 TAKESNAPSHOT E G

To delete the snapshot I would run

EMCmd AGNODE6 DROPSNAPSHOT E G

I put together a batch file that would navigate to the proper folder and issue a drop snapshot to clear any existing snapshots and then take a new snapshot. This was scheduled daily on the secondary server.

The problem I encountered was dropping the snapshots during a failover event on the cluster. In order for the SQL Server clustered resource to failover to the secondary, all snapshots had to be dropped first.

Refreshing the snapshot was straightforward, but how do I drop the snapshots in a timely fashion when the SQL Server clustered resource needs to failover from the primary server, in my case AGNODE5 to the AGNODE6.

SIOS provides a sample VBScript named DKSnapshotCleanup that can be copied and modify to drop the snapshots on the secondary. For this to work I needed a folder on each server and a copy of the modified DKSnapshotCleanup script. Next, I had to add a resource to the cluster and point to the VB Script and make my DataKeeper volume dependent on the new resource. The instructions in the VB script are detailed and easy to follow, however I had an issue when it stated to create a “Generic Application” and point to the script, I had to choose “Generic Script” instead.

clip_image001

In my environment, I had a drive E for data files and drive G for log files. In my DKSnapshotCleanup script, I needed to drop the snapshot for both volumes. To do this, I added two lines, one for each volume toward the bottom of the script.

clip_image002

I assigned the DKSnapshotCleanup Script resource as a dependency on drive E.

clip_image003

When my cluster failed over to the secondary node and back, things didn’t go as planned. Occasionally drive G would fail to come online which failed the cluster.

clip_image004

I was able to quickly bring drive G: back online however that isn’t an acceptable course of action in production. This has to work consistently each time the cluster fails over.

I did some more testing and validated with SIOS support that the best course of action was to create a dependency for drive G on drive E.

clip_image005

This makes drive G have to wait for drive E to come online before it starts. Once I configured the second dependency, I was able to consistently fail the cluster over with both snapshots being dropped and both volumes coming online.

My overall experience with SIOS DataKeeper was positive. The support team was very quick to respond and the product was easy to install. Although I had a couple of configuration issues, I was able to resolve them quickly. Another cool thing about SIOS DataKeeper is that it works on both Windows and Linux, it is a great way to build in HA for storage without having to have a high dollar SAN replication solution.

Issue publishing to SSRS 2012 with SSDT 2015

I recently was helping a client who was trying to use SQL Server Data Tools 2015 to update and generate reports that would be deployed to SQL Server Reporting Services 2012. When trying to deploy the report, the client would get the following error:

“[rsInvalidReportDefinition] The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas.”

I knew that SSDT 2015 should be backwards compatible and able to work with multiple versions of SQL Server products, so I had to do a little research to figure out what exactly needed to be changed in order to publish a report to SSRS 2012.

In my lab, I created a new project named GetDatabases. This project was a basic report that would get query a system table. The first thing I tried to change in the project was the TargetServerVersion located under the project properties.

clip_image001

clip_image003

I updated the TargetServerVersion to “SQL Server 2008 R2, 2012, or 2014”, the default in was “SQL Server 2016”. I also updated the TargetServerURL to my development server running SSRS 2012. I made these changes to both the release and debug configuration options.

After making these changes, I was still unable to deploy the report. After a bit more research, I found a location to change the default deployment server version of the Business Intelligence Designer. This is located under ‘Tools and then Options’. The default was set to version 13.0, once I changed the version to 11.0 to match the server I was deploying to, I was able to successfully deploy my report to SSRS 2012.

clip_image004

It seems odd to me that I would have to change the overall behavior of SSDT instead of just the properties of my project, however I confirmed with my client that they couldn’t deploy until they modified the default deployment server version.

I hope this helps if you are having similar issues.

Calling all user group leaders! I want to present for you!

Late last year SQlskills made an offer to remotely present for user groups anywhere in the world. The response was incredible. The SQLskills team has been presenting like crazy so far this year. Since I didn’t join SQLskills until January I was not part of the initial listing of potential sessions that the chapter leaders could pick from but that hasn’t stopped me from getting in on some of the fun. Over the past couple of months I have been able to remotely present for user groups in Alaska, Indiana, and Puerto Rico.

Getting first-hand experience with how rewarding it is to remotely present to areas that I otherwise wouldn’t be able to share with has made me want to have my own call for user group leaders.

If you would like me to present remotely for you during the remainder of 2015, send me an email with:

  • Details of which user group you represent
  • The usual day of the month, time, and timezone of the user group
  • Which months you have available, starting in June 2015

What’s the catch? There is no catch. We’re just stepping up our community involvement this year and I am trying to contribute like the rest of my team, plus I love presenting :-)

I’m serious about anywhere in the world – it doesn’t matter what the timezone difference is – I’ll try to make it work.

Currently I have two sessions you can pick from.

Common SQL Server Mistakes and How to Avoid Them

Making mistakes is human nature; avoiding them comes from experience and shared wisdom from others. In this session, Tim Radney will share experiences he has encountered supporting multiple Fortune-level companies. Tim will share real-world experiences of environments he has worked on and the impact he has seen from administrators not knowing how to properly configure and administer SQL Server. In this session, you will learn many accepted best practices for installing, configuring, and supporting SQL Server to help your environment run as smooth as possible. When you leave this session, you will have a checklist of items to take back to your shop to make sure your environment is configured properly.

Understanding SQL Backups and Restores
Having backups is important for organizations, but – are you sure it will restore? Too often, Tim Radney has been brought in by organizations to help recover failed systems where administrators simply didn’t know how to properly restore SQL backups. Or, where their backups didn’t restore? In this session, Tim will go through the various types of backups that can be performed as well as demonstrate the proper process for restoring each of them. This isn’t your basic session as Tim will not only be covering the common full, differential and log backups, he will also be covering partial, file group, COPY_ONLY, a URL backup and restore, encryption as well as performing a demo of a “hack attach” for the ultimate tail of the log restore.

I hope to hear from you,

Be careful when playing with features

I recently performed a health audit for a client and found several transaction logs that had grown exponentially larger than the data files. This is a fairly common thing to find when databases are in full recovery model with no log backups, however that was not this client’s issue. The databases were in full recovery and log backups were being taken every 15 minutes, however the log was not truncating and reusing the existing log.

I looked for open transactions assuming that was the issue but that was a dead end.  I decided to do some digging and looked at the database properties in sys.databases and found that the log_reuse_wait_desc was set to ‘REPLICATION’. This was odd since the client stated they do not use replication. I reported what I found to my client and was told that a long time ago they toyed with replication trying to build a reporting server and ended up killing the project.

What didn’t happen was cleaning up and removing replication from the databases once the project was cancelled. I am not a big replication guy so this was a new area for me. With a quick search I was able to find sp_removedbreplication which removes all replication objects from the database. I was able to remove replication from each of the database by running the following:

sp_removedbreplication N’db_name’

Once replication was removed the log_reuse_wait_desc was updated to ‘nothing’ and the log was able to truncate. This didn’t solve all the problems with these logs though. An adverse effect of the logs growing out of control were extremely large log files with a lot of virtual log files. I followed Kimberly’s advice in her post (8 steps to better transaction log throughput) and was able to shrink the log file, resetting the number of virtual log files, and then properly grow the log files to efficient sizes with more appropriate auto growth sizes.

BTW, for an interesting twist on the REPLICATION value for log_reuse_wait_desc, see Paul’s post: REPLICATION preventing log reuse but no replication configured.

 

Becoming a mentor

I was recently asked if I would take on being the mentor for one of our fellow #sqlfamily members. I was extremely humbled at this request and immediately said yes. I’ve mentored a few others in I.T. over the past few years, but this is what I would call my first big mentoring project. Mentoring someone starting in I.T .feels like it wouldn’t be that hard, but considering how to help an established data professional get to the next level feels much more challenging.

I’m always up for the challenge and I’m lucky enough to have several mentors I can call on for help with this if I get stuck. Being at the stage of my career where I get to pay it forward like this is truly amazing. I have had my fair share of challenges and successes that I can draw on to help give guidance to others.

What I’ve found talking with my new mentee is that my experience in corporate America has been much like his. There are a great deal of parallels from what I went through and what he is currently going through. I’m really looking forward to being a mentor to my friend and to us both growing through this process.

I encourage everyone to have a mentor that you can call on to help you with whichever area you feel you need to progress and grow in. I also encourage those of you who have knowledge to share with others to consider becoming a mentor to others.

My boss recently took on a huge endeavor to help mentor more than 50 individuals. He has been doing this awhile and can handle that type of load but for me, I am looking forward to working with my one mentee and if others will have me, take on a few more down the road.

SQL Server 2005 end of extended support

Microsoft has recently confirmed that the end of extended support for Microsoft SQL Server 2005 will be April 12, 2016. At the time of this blog post that is just under 1 year from now. One year is not that very long if you have several mission critical applications that rely on SQL Server 2005. To migrate there is a great deal of testing and validation that must be done. If you are currently having to support SQL Server 2005 then now is the time to engage others to start the planning and migration of these databases. According to a blog post by T.K. Rengarajan, SQL Server 2014 has been bench-marked to be 13 times faster than SQL Server 2005. In addition to SQL Server 2014 being much faster, there are many new features you could benefit from such as Always On, backup encryption, backup compression, and more. If your application does not support SQL Server 2014 you still should consider upgrading to a supported platform such as SQL Server 2008, SQL Server 2008R2 or SQL Server 2012. Each have a number of new features and performance improvements over SQL Server 2005.

 

Common Things Administrators Miss

A big part of any DBA consultant’s job is helping clients’ workloads run faster. When I get called to help a client, the first thing I do is perform an audit of their instance to get a good baseline. I pull a lot of history from DMVs, log files and system configuration information.

A lot of effort has gone into making this information gathering as light weight as possible because the last thing I want to do is add stress to an already stressed system. Collecting all this data enables me to be able to check the client’s configuration against commonly accepted best practices as well as performance bottlenecks.

Over the past few years I have found that many clients make the same mistakes – missing changing the same settings or missing implementing the same maintenance. Here are a few that I’ve picked from my long list that I think are the most critical and easy to implement:

  • Backups
  • DBCC CHECKDB
  • Memory settings
  • Statistics
  • Index maintenance
  • MAXDOP and cost threshold for parallelism
  • SQL Server Agent alerts

Backups

I always check to see what recovery model each database has and the history of recent backups of each database. Typical findings here include:

  • No backup at all – no record of backups for the database
  • Missing backups – database in FULL recovery model with no log backups
  • No recent backups – last backup was weeks/months/years old

Issues with backups always make the top of my priority list as it’s critical that data recovery be possible. That means not only that the client has proper backups to meet SLAs but also that copies of these backups are stored in a secondary location offsite.

DBCC CHECKDB

I also always look to see when the last DBCC CHECKDB was ran against each database. What I find is very similar to backups:

  • No DBCC CHECKDBs being performed at all
  • Only some databases with DBCC CHECKDBs being performed
  • No recent DBCC CHECKDB – last was performed months or years ago
  • The worst, failed DBCC CHECKDBs – job scheduled, corruption found and nothing is being done

Finding corruption in a client’s database is always frustrating when the corruption is in a portion of the database that is going to cause data loss that will have a detrimental effect on the business.

I have worked with clients from numerous lines of business, most recently including Relativity customers, healthcare, financial and retail. Each had a similar story where some I/O-subsystem related issue caused corruption. In some of the cases the client was performing consistency checks and when the corruption was detected they reached out for help. In those cases I was able to restore from backups with no data loss.

In some other cases the client was not so lucky and lost data. Corruption is nasty. Sometimes you get lucky and the corruption is in a non-clustered index and rebuilding the index is the fix, other times the corruption could be the heap or clustered index, which is the physical table data, or schema corruption. For those a restore is nearly always your best option.

I have had situations where the only fix was to script out the database and copy all the usable data out of the corrupt database into the newly-created database. This was due to not having backups and having unrepairable schema corruption. That is not fun. You can avoid these costly issues by running DBCC CHECKDB regularly against all your databases and reacting quickly when failures occur. You will want to have a backup retention policy that provides enough coverage for your consistency checks. You don’t want to be running consistency checks weekly and only retain three days of backups.

Memory Settings

A default install of Microsoft SQL Server will have your minimum memory value set at 0 and your maximum server memory value set at 2147483647 MB. If these values go unchanged you can find yourself in a situation where SQL Server can be starving the operating system or if your SQL Server is shared with another application including SSIS, SSAS, or SSRS, that SQL Server itself could be starved for resources.

It is highly advisable to set a minimum and maximum value for your SQL Server instance. My colleague Jonathan Kehayias has written an excellent blog post How much memory does my SQL Server actually need, with the formula that many consultants are using to establish the baseline for the maximum memory value. I recommend my clients to set the minimum value to 30% of the memory on the server, to avoid SQL Server being pressured too low by other executables.

Statistics

Finding out-of-date statistics is very common when performing audits on SQL Server instances. There is a misconception that having “auto update statistics” enabled keeps your statistics up to date. The issue with that setting is that statistics are not automatically updated until at least 20% of the rows plus 500 have been modified. For large tables this could take a very long time!

Having up-to-date statistics is important because the Query Optimizer uses statistics to formulate execution plans. With out-of-date or invalid statistics, your system is very likely going to have inefficient plans.

To prevent having out-of-date statistics, I encourage my clients to utilize a third-party script to update statistics. Ola Hallengren has published a widely-used and highly-praised Maintenance Solution for SQL Server. Part of that process is the Index Optimize procedure that includes updating statistics. This process can update all statistics or just those that have had row modifications since the last statistics update. By updating the statistics that have had row modifications you decrease the overhead of having to update all statistics, which is really cool.

Index Maintenance

Performing index maintenance by removing fragmentation from your indexes is very important. According to a Microsoft study, index fragmentation can have a negative impact from 13-460% depending on the size of the environment and the level of fragmentation. See Microsoft SQL Server Index Defragmentation Best Practices for more details.

Fragmentation occurs through normal operations such as inserts, updates and deletes, which you can consider as normal wear and tear on your databases. To remediate this, proper index maintenance of rebuilding or reorganizing your indexes is needed. For this I again turn to Ola Hallengren, for his Index Optimize script. What makes me recommend Ola’s scripts over database maintenance plans is the ability to specify to rebuild or reorganize based on the level of fragmentation as well as only doing so for indexes that have more than X number of pages.

Rebuilding and reorganizing indexes comes with an I/O and logging cost so being able to use logic to determine which indexes to perform maintenance on helps decrease that cost.

MAXDOP and Cost Threshold for Parallelism

Max degree of parallelism and cost threshold for parallelism are usually left at the default values on the client servers I see. For MAXDOP the default value is zero which means an ‘unlimited’ number of CPUs could be used to execute a parallel region of a query. Many years ago when servers had lower processor core counts, this value was acceptable. In current times with high core density and multi-socket servers, an unlimited number of CPUs for parallelism isn’t so good. Microsoft has recently given guidance that if you have more than 8 CPUs on the server, a MAXDOP value of 8 should be used as a starting point, if you have less than 8 then use a value from 0 – N, with N being the number of CPUs.

With the popularity of virtualizing SQL Server, many of the virtual servers I have worked on have less than 8 virtual processors and a MAXDOP of 0. In this case the default value would be ok, however I still tell my clients that if they later decide to scale the server to more than 8 processors to consider setting the MAXDOP value. You can read about the Microsoft recommendations here: Recommendations for guidelines for the “max degree of parallelism” configuration in SQL Server.

Cost threshold for parallelism has a default value of 5. The history of this number goes way back to the early days of SQL Server and the workstation that testing was performed on. Fast forward to modern times and the advancements in processors, memory, disk and everything else related to computing, the cost estimation of 5 is really outdated. Plenty of testing has shown that increasing the number from 5 to a higher value will keep shorter-running queries from having a parallel plan. SQLskills recommends that our clients start with a value between 25 and 50, monitor the plan cache, and adjust from there. See this great blog post from Jonathan for more details: Tuning ‘cost threshold for parallelism’ from the Plan Cache.

SQL Server Agent Alerts

Everyone should be using SQL Agent alerts unless they have a third-party application monitoring for the same error conditions. Configuring alerts is easy and free, and having them configured will give you critical information when your servers are having problems. Who doesn’t want to be proactive, right? Unfortunately this is another common thing to find clients not doing.

I recently wrote an article titled SQL Server Agent Alerts on SQL Sentry’s cool sqlperformance.com website, providing step-by-step instructions on how to create alerts for severity 19-25 errors and error 825. Enabling these alerts is easy: enable database mail, create a mail operator and then create the alerts. This can be accomplished using the GUI or with T-SQL. I encourage my clients to script out this process using T-SQL and make it part of their standard server build. Who doesn’t like free and easy?

Summary

As you can see, there are lots of settings that should be modified from the defaults for an installation of SQL Server. This list is not comprehensive and there are many more things I plan to blog about when it comes to not using defaults, so stay tuned for more posts!