Procure SQL welcomes Jay Falck!

Announcements

Procure SQL is proud to announce that Jay Falck has joined our team! Jay brings a wealth of knowledge, wisdom, experience, and levity to our group.

How it all started.

Jay’s love of all things computer started his sophomore year in high school when a friend of his chemistry teacher loaned them a computer for a week. Jay was so taken, he ended up walking home from school around 7:00 PM that night! Over the next two years, a teacher, using his own money, bought a computer kit and did training before and after school as the school didn’t have a computer science class. For those two years, Jay’s team won first place in a programming contest at Southwest Texas State for schools from Waco to San Antonio. He decided then he was destined to do something computer related with his life.

A very impressive list of experience!

While in high school, Jay worked as a night shift operator at the textile mill in New Braunfels, Texas, where he got to dabble in FORTRAN and COBOL troubleshooting. As a freshman at UT, he worked for an Animal Science researcher maintaining and running a huge FORTRAN program that performed radioimmunoassay analysis. At San Antonio College, Jay worked in the research lab at the Audie Murphy VA Hospital. Here he did everything from computer operator to DG BASIC and FORTRAN programming for the research doctors. After graduation, he moved to Austin and went to work for a small firm selling account software and hardware. (This was his first exposure to rudimentary database systems.)

Eventually he moved to state government as an application developer working with VSAM and Model204 database systems. After 2 years he got the opportunity to do what he really wanted to do, Operating Systems programming! He spent the next 14 years of state employment as a mainframe system programmer. This lead to his two biggest passions in IT: performance and security.

Jay’s passion for healthcare IT.

In 1996, when the Clinton Administration was moving forward with HIPAA, Jay was invited to join a company that was moving into healthcare. At this time he didn’t know anything about healthcare IT but neither did they, so he went along for the ride. At this point Jay added healthcare IT to his passion list. He spent the next 20 years doing programming, system administration, and database administration for Unix/Linux and Windows based systems. This included Informix, Universe, Oracle and SQL Server databases. During this time he was VP of the CSC Healthcare User Group and the CIO of a small health insurance company in Lexington, KY.

SQL Server experience.

Jay’s two passions in IT are making things go faster and security. His experience with SQL Server includes years of improving query performance, indexing, data extraction and transfer, and security. Through the years, he has had the opportunity to see first-hand how important security is to the data world. He is looking forward to bringing his understanding of security policy to the database admin role and becoming proficient in applying policy to procedure.

Jay holds many certifications.

Fast forward to today and Jay maintains an impressive list of certifications related to his passion areas: CISSP (Certified Information Systems Security Professional) and HCISPP (Healthcare Information Security and Privacy Practitioner) from ISC2, and CCSA (Certified Cyber Security Architect, a HIPAA centric certification that replaced the CHSS (Certified HIPAA Security Specialist) and CSCS (Certified Security Compliance Specialist). He is currently studying to add Microsoft Certification for SQL Server and ISC2 CCSP (Certified Cloud Security Professional).

What he brings to the table.

Not only does Jay bring his vast experience and knowledge, he brings a love of movies and the Texas Longhorns. He’s always armed with a zinger and great story as well! Jay is always ready to learn new things as well as teach what he knows. We are very thankful to have Jay and cannot wait to share his knowledge and humor with our clients!

March 26, 2018/0 Comments/by Angela Tidwell

SQL Saturday Chicago

General, Performance Tuning

Last weekend I had a blast speaking at the SQL Saturday in Chicago. It was awesome to share my knowledge and also catch up with some good friends. My talk was on Automating the Pain Away with Query Store and Automated Tuning. I hope this session helped people understand Query Store and Automated Tuning as these two new features can help resolve parameter sniffing problems.

John Sterrett Teaching Performance Tuning

John Sterrett teaching Performance Tuning at SQL Saturday Chicago

SQL Saturday Chicago training class

The post SQL Saturday Chicago appeared first on SQL Server Consulting & Remote DBA Service.

March 21, 2018/0 Comments/by JohnSterrett

Finding Top Offenders with Extended Events

General, Performance Tuning

This week at MVP Summit I got to talk with a friend who loves profiler. We were talking about capturing workloads and doing analysis on them. T-SQL or other 3rd party tools like ClearTrace, ReadTrace would be used to aggregate the data to get insight into the top offenders. I mentioned that workload analysis could be done with extended events without writing a single line of T-SQL. This was a lightbulb moment for him. Quickly, I learned that he is not alone and that there are a lot of people in the community who didn’t know this either.

I am including a quick video below to show you why Extended Events is a great solution for finding top offenders in a workload.

The post Finding Top Offenders with Extended Events appeared first on SQL Server Consulting & Remote DBA Service.

March 6, 2018/0 Comments/by JohnSterrett

SQL Server Recovery Models

DBA 101, Disaster Recovery or High Availability, Education

In my last post (A Beginner’s Guide to SQL Server Backups) we discussed the basics of SQL Server Backups. As Backups are the foundation for Recovery, the next logical discussion should be Recovery Models. For reference, I have included this Sample Backup Plan from the earlier post:

Sample Backup Plan

There are Three SQL Server Recovery Models

Simple Recovery Model

This is the most basic of the Recovery models. It gives you the ability to quickly recover your entire database in the event of a failure; (or if you have the need to restore your database to another server) however, this only recovers data to the end of the last backup.

Thankfully, Differential Backups can be utilized with this recovery model. NOTE: Any changes made after the last Full or Differential Backup will be lost. Transaction Log Backups cannot be used in Simple Recovery Model. There is no Point-In-Time recovery in this model (recovering to a specific transaction or point in time). Looking at the sample backup plan above… if you have an issue occur Saturday afternoon, you will lose any changes in data since your last Differential Backup that ran Friday night. If you do not opt for Differential Backups and only perform Full Backups once a week, your changes and data will be lost for the full week. (GASP!)

Why would you ever consider using such a basic, Simple Recovery Model? Actually, there are a few really good (and perfectly safe) reasons why you choose to use Simple Recovery Model:

Your data is not critical and can easily be recreated
The database is only used for test or development
Data is static and does not change
Losing any or all transactions since the last backup is not a problem

Full Recovery Model

This is the most inclusive of the Recovery models; you can recover (depending on your valid backups) data up to the last transaction that was run before the failure. Log backups are a must for this recovery model, including Tail Log Backups (which will be discussed at length in a later post). Data can be restored to a specific point in time (once again, depending on your backup plan)!

- - - Let’s explore that for a moment using the sample backup plan above. Say you take Full Backups on Sunday night, Differential Backups every week night, and Transaction Log Backups every half hour. If you have a failure and must restore data, by using all the backups to the specific time you need to restore, you can recover data at any moment in time! Using this recovery model, you have a potential of minimal to no data loss (once again…depending on your backup plan and whether your backups are restorable). You want to be the DBA who is able to restore quickly and recover data to the very last moment before the failure or crash occurred. YES, we all want to be THAT person!

Bulk Logged Recovery Model

Once again, this Recovery model requires log backups in order to prevent the log file from continually growing. This is a special purpose recovery model that is not widely used, and Microsoft states that it should only be used “intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data.” It allows high performance bulk-copy operations and is only available when in Full Recovery. Keep in mind that with the Bulk Logged model you can recover to the end of any backup; however, if a bulk transaction has occurred during the last log backup, those transactions will have to be redone. From the bulk operation on, you can no longer utilize point in time restore.

OOOOOOOOOO, I dropped the ENTIRE database!

While studying Backups and Recovery, I have been working in SQL Server 2016 AdventureWorks Database (Download Here). To see if I REALLY understood the process of Backup and Recovery, I made a Copy Only Backup of my original database, made some changes to my database, took Differential Backups, made another Full Backup, made more changes, and made more backups….and then dropped EVERYTHING. POOF….gone. While I did do this intentionally, my heart was racing. Sparkly spots darted in and out of my vision, and my heart pounded in my ears. Would I be able to get it back? Would I be able to restore it? WHAT HAVE I DONE??????

I Dropped the DB! Photo Credit @Lance_LT

How do I know that my backups were good?

I firmly believe you learn more from one of your failures than you do from one hundred of your successes. TEST TEST TEST TEST those backups! I am afforded some extra wiggle room while I am working on my virtual machine and not actually working on a client’s production database.

So, I did it, I dropped that DB like a hot rock. I attempted to restore my database in Simple Recovery Model. My Differentials would not restore. Somewhere along the way in the backup or restore process, I messed up. (I will give you a hint at my mistake, I did not restore the Full Backup with NORECOVERY before I tried to restore the Differential Backup with RECOVERY – we will go into Recovery processes in a later post).

Knowing that I had made a Copy Only Backup before I started monkeying around with the database, I set out to restore faith in myself. And boom, I restored the Copy Only Backup! The day was saved! Well, mostly. I lost some data changes, but still had the original database, so that was good-ish.

Lessons Learned

At a later date I repeated the steps above and was able to fully recover a database that was previously in Simple Recovery Model complete to the last Differential Backup (noting my mistake and learning from it). I additionally created another database to test the Full Recovery Model with Full Database Backup, Differential, Transaction Log Backups, also utilizing Tail of the Log backup to get to the very last transaction that was run after the last backup and before my data files AND database were deleted. (This is pretty in-depth and is covered in Recovery Using Tail Log Backup.)

Please come back next time when we will explore ways to validate those backups.

Thank you for reading!

February 13, 2018/5 Comments/by Angela Tidwell

A Beginner’s Guide to SQL Server Backups

DBA 101, Disaster Recovery or High Availability, Education

As was discussed in a previous blog, (The Most Important Role of a SQL Server DBA) Backups and Recovery are the cornerstone of all successful SQL Server DBA careers. Exactly how much attention is paid to backups until something drastic happens? NOT AS MUCH AS SHOULD BE!

What is a SQL Server Backup?

A backup is the process of putting data into backup devices that the system creates and maintains. A backup device can be a disk, file, a tape, or even the cloud. Easy enough!

Backups are your Keys to Success

There are four different methods of backup in SQL Server:

Full Backup – A Full Backup is a copy of the entire database as it exists at the time the backup was completed. It is all-encompassing; every detail of your database (data, stored procedures, tables, functions, views, indexes, and so forth) will be backed up. This is why the Full Backup is the foundation of every restore sequence. These Full backups can be quite large and put a strain on your storage capacity. Despite the name, a full backup will only backup the data files; the transaction log must be backed up separately. To ensure you can recover to the point of failure quickly, you will want to also utilize Differential and Transaction Log Backups (we will cover these next). Without a Full Backup, your Differential and Transaction Log Backups are useless as you cannot restore a database without a Full Backup!
Differential Backup – Differential Backups capture only changed data from the last Full Backup. Simple terms: this will backup only the changes since the last Full backup, not backup the entire database. In the event of an outage, the Differential Backups can greatly reduce the time to recover. Using both Differential Backups and Full Backups will dramatically reduce required storage space, as the Full Backups can be very large and the Differentials are remarkably smaller.
- How does a Differential Backup know what data has changed? When data is changed on a page, it sets a flag. This flag indicates which extent (collection of 8 database pages) the Differential Backup will need to backup. If your Differential Backups are set to run daily, each day’s data changes are recorded and the flag will not be cleared until a Full Backup has been executed. Say on Monday you have 3 flags set, and Thursday you have 4 more set, your differential backup on Thursday night will contain data changes represented by all 7 flags, so the flags from the Monday Differential are not cleared by the subsequent Differential Backups. The flags would only be cleared by a Full Backup.
Transaction Log Backup – A Transaction Log is a physical file that stores a record of all the transactions that have occurred on a specific database; much like a recorder, maintaining a record of all the modifications. The Transaction Log Backup will capture the information and then releases the space in the log file so it is able to be used again. In doing this, the Transaction Log Backup truncates the information in the Transaction Log, but the data is not deleted, it is stored in the backup! Without Transaction Log Backups, the Transaction Logs will run and grow nonstop, chewing through valuable storage space.
- Not only does backing up and truncating the Transaction Log manage the filesize, it also decreases the potential for catastrophic data loss. Having all the Transaction Log Backups since your last Full Backup will allow you to perform point in time restores. Transaction Log Backups are ideally set up to execute every few minutes to every hour, depending on your company’s threshold for data loss.
Copy Only Backup – A Copy Only Backup doesn’t change the Differential. It can be a full backup in that it is a record of the database as it exists when the backup is taken; however, it does not reset flags. It is ideal for doing a full backup in the middle of the week without disrupting any other backups. A Copy Only Backup can be used in a Dev space for troubleshooting, or preparing to move to a new environment.

Here is an example of a solid weekly backup plan which uses Full, Differential, and Transaction Log Backups:

Sample Backup Plan

Okay, great…backed up, now what?

Okay, so now you know what SQL Server backups are, a description of each backup type, an idea of how and what they back up, and have an idea of a good plan of action to create a solid backup plan. So, why are backups so important? Do you know how easy it is to accidentally update or delete data? It is just one T-SQL statement away with the wrong filter, or no filter at all. Having good up to date backups and being able to restore them is the difference between looking like a hero and being forced to find a new job. Backups are your foundation for Recovery.

Please come back, this is the 2nd in a series of blog posts regarding SQL Server Backups and Recovery. See you next time when we begin to discuss SQL Server Recovery Models. Thank you for reading!

February 8, 2018/2 Comments/by Angela Tidwell

Where is my Availability Group Primary Replica?

Disaster Recovery or High Availability

In SQL Server 2012 we got this new high availability feature called availability groups. With readable secondaries under the covers, it can be harder to figure out the answer to the following two questions. When did the availability group failover? Where did the availability group go when the failover occurred? The goal of this blog post is to help simplify answering these questions.

Availability Group Extended Event

One of the things we really like about Availability Groups is that there is a built-in extended event named “ALwaysOn_health” that runs and captures troubleshooting information. We took a look at the extended event and noticed that there are several error numbers that were included in the filter for this extended event. This is shown below as we scripted out the default extended event for a quick review.

CREATE EVENT SESSION [AlwaysOn_health] ON SERVER ADD EVENT sqlserver.alwayson_ddl_executed, ADD EVENT sqlserver.availability_group_lease_expired, ADD EVENT sqlserver.availability_replica_automatic_failover_validation, ADD EVENT sqlserver.availability_replica_manager_state_change, ADD EVENT sqlserver.availability_replica_state_change, ADD EVENT sqlserver.error_reported( WHERE ([error_number]=(9691) OR [error_number]=(35204) OR [error_number]=(9693) OR [error_number]=(26024) OR [error_number]=(28047) OR [error_number]=(26023) OR [error_number]=(9692) OR [error_number]=(28034) OR [error_number]=(28036) OR [error_number]=(28048) OR [error_number]=(28080) OR [error_number]=(28091) OR [error_number]=(26022) OR [error_number]=(9642) OR [error_number]=(35201) OR [error_number]=(35202) OR [error_number]=(35206) OR [error_number]=(35207) OR [error_number]=(26069) OR [error_number]=(26070) OR [error_number]>(41047) AND [error_number]<(41056) OR [error_number]=(41142) OR [error_number]=(41144) OR [error_number]=(1480) OR [error_number]=(823) OR [error_number]=(824) OR [error_number]=(829) OR [error_number]=(35264) OR [error_number]=(35265))), ADD EVENT sqlserver.lock_redo_blocked ADD TARGET package0.event_file(SET filename=N'AlwaysOn_health.xel',max_file_size=(5),max_rollover_files=(4)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) GO

SQL Server Availability Groups got us interested in learning why these specific errors are included in the extended event session explicitly created for managing Availability Groups. Knowing that the descriptions for messages are in the sys.messages table, we did a little digging.

System Messages

Taking the error numbers from the AlwaysON_health extended event we were able to build the following query to get the description of the errors included in the extended event.

SELECT * FROM sys.messages m where language_id = 1033 -- English --AND m.message_id =1480 AND ([message_id]=(9691) OR [message_id]=(35204) OR [message_id]=(9693) OR [message_id]=(26024) OR [message_id]=(28047) OR [message_id]=(26023) OR [message_id]=(9692) OR [message_id]=(28034) OR [message_id]=(28036) OR [message_id]=(28048) OR [message_id]=(28080) OR [message_id]=(28091) OR [message_id]=(26022) OR [message_id]=(9642) OR [message_id]=(35201) OR [message_id]=(35202) OR [message_id]=(35206) OR [message_id]=(35207) OR [message_id]=(26069) OR [message_id]=(26070) OR [message_id]>(41047) AND [message_id]<(41056) OR [message_id]=(41142) OR [message_id]=(41144) OR [message_id]=(1480) OR [message_id]=(823) OR [message_id]=(824) OR [message_id]=(829) OR [message_id]=(35264) OR [message_id]=(35265)) ORDER BY Message_id

Now we will focus on one particular error message. The error message 1480. Looking at the description below you will see that every time a database included in an availability group or database mirroring changes its role this error occurs.

The %S_MSG database “%.*ls” is changing roles from “%ls” to “%ls” because the mirroring session or availability group failed over due to %S_MSG. This is an informational message only. No user action is required.

When did my AlwaysOn Availability Group Failover?

By now it should not be a big surprise to see how you can figure out when your availability group failed over. To answer this question, we are going to filter the “AwaysOn_health” extended event for error_number 1480.

The “AlwaysOn_health” extended event target is to a text file, and by default, it will utilize the default log folder for SQL Server. Keep in mind, that by default the target does a rollover four times when a file reaches 5 MB for a total of 20 MB. If you continually have failovers occur data will get purged.

The server used for this blog post my path is “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\” if this is not your path you will need to modify line 2 in the script below.

;WITH cte_HADR AS (SELECT object_name, CONVERT(XML, event_data) AS data FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\AlwaysOn*.xel', null, null, null) WHERE object_name = 'error_reported' ) SELECT data.value('(/event/@timestamp)[1]','datetime') AS [timestamp], data.value('(/event/data[@name=''error_number''])[1]','int') AS [error_number], data.value('(/event/data[@name=''message''])[1]','varchar(max)') AS [message] FROM cte_HADR WHERE data.value('(/event/data[@name=''error_number''])[1]','int') = 1480

Below you will see an example of the result set which shows my last failover.

You could also utilize the Extended Event GUI to watch data. We will skip that today as we would recommend using T-SQL so you can find failovers in multiple Availability Groups on different servers. We will go into more detail about this process a little later in the blog post.

How Do We Become Proactive?

If you want an action to occur when a database inside an availability group changes roles to be proactive, you can configure an SQL Agent Alert. An SQL Agent alert can perform actions like sending an email to your DBA team or running another SQL Agent job to complete your required task.

The following shows you how to configure this alert via the SSMS user interface.

How Do We Report failovers across the Enterprise?

Central Management Server (CMS) is your best friend for building reports to show Availability Group failovers across the enterprise. You can create a CMS group for your SQL instances in your Availability Group and copy and paste the query above to detect Availability Group failovers.

NOTE: This assumes you have an standard install process that keeps the default log path the same across your SQL Server instances. We strongly encourage that you have an automated SQL Install process that keeps using the same path for all your installs but we will keep that blog post for another day.

Need more info on Building Your First Availability Group?

Join us for pre-conference training brought to us by SQL Saturday Nashville! John Sterrett will be presenting a half day precon in Nashville, TN on Thursday, January 11, 2018, from 1:00 pm to 4:30 pm!

In this half-day session, you will learn how to build your first availability group while also learning how availability groups work with other components like active directory, storage, and DNS. You will walk away with a checklist to help your future deployments while also learning how to implement, monitor, troubleshoot and use availability groups.

Space is limited so act fast and Register here now!

Nashville Availability Group Training

January 8, 2018/0 Comments/by JohnSterrett

Building Your First Always On Availability Group

Disaster Recovery or High Availability, Education

Have you wanted to build an availability group but didn’t know where to start?
If so, we have discounted SQL Server Availability Group training for you!

Nashville, TN learn how to implement and monitor Always On Availability Group Solutions!

Join us for pre-conference training brought to us by SQL Saturday Nashville! John Sterrett will be presenting a half day precon in Mufreesboro, TN on Thursday, January 11, 2018, from 1:00 pm to 4:30 pm!

SQL Server Availability Group Training in Nashville, TN