Encryption has always been intriguing to me but seemed like it could be a very complex process to set up. However, SQL Server has made it very simple when they introduced Always Encrypted (AE) into SQL Server 2016 and Azure SQL Database. Unlike Transparent Data Encryption (TDE) which only encrypts data files and backups at rest, AE is configured on a column level and not database level. Additionally, Always Encrypted is available in Standard (and Express) Edition, starting with SQL Server 2016 SP1. You can easily encrypt a social security number (SSN) which is considered very sensitive within the United States or Salary column in a table with just a…
-
-
This week is Thanksgiving in the United States, so I thought it fitting to write a quick blog on what I am thankful for as a DBA. These are in no particular order and feel free to respond with something you are thankful for. I’d love to hear it. Glenn Berry’s Diagnostic Scripts- (B|T) Used these for years. Really a great set of scripts and explanations that we all should be grateful for. Ola Hallengren’s (B) Maintenance scripts. Index Optimization, Backup, and Integrity Checks for all! They have become an industry standard and continue to get better and better. RCSI (Read Committed Snapshot Isolation) –My Readers can stop blocking Writers! Thanks…
-
What is the GO statement and why is it so important to use? When do I have to use it? When do I not use it? These are questions that have passed through my head from time to time while writing T-SQL within SQL Server. First What Is It and When Should I Use It? The GO statement lets SSMS (the interface) know when it’s the end of the batch. It basically defines the scope of what you are trying to send to the Database Engine. The below example sends two separate statements. The first statement changes the database context to run the next statement under, followed by the execution of…
-
I ran across a client the other day that had these Backup and Recovery options set like the picture below because it is defaulted this way. The Database Administrator didn’t know what they should configure them as so he left them alone. I find this is the case with a lot of options. For the most part leaving the defaults can be just fine, but other leaving others cause leave you missing out on some good features. Let’s start from top to bottom. Default backup media retention in days. Now the first things that comes to my mind is that “hey this is a cleanup job” SCORE! Thinking that maybe…
-
Are you using your Model Database to its full potential? I am finding more and more that Database Admins are not using the Model database to its fullest potential and some not at all. What is that Model Database for? The model database is basically the default setup (template) for all other databases created on a SQL Server instance. All databases created after install will inherit the properties of this database. Why Configure It? Using the model can insure consistency within your environment and is a quick way to automate your database setups. Below is a list of things I’ve used in my environments and others. Top (in no particular…
-
One of the things I’ve been able to implement to help with performance is changing from Update Statistics Synchronous to Auto Update Statistics Asynchronously. It’s a simple change that can have a big impact when implemented in highly transactional OLTP environments. Notice I said OLTP not OLAP, since data in an OLAP environment tends to not be as dynamic, so it’s rare to enable this in a data warehouse. So, what’s the difference between the two and why does it help? Synchronous (defaulted as AUTO_UPDATE_STATISTICS =TRUE) By default, when Auto Update Statistics is set to True, the SQL Server Query Optimizer will automatically update statistics when data has met a threshold…
-
Okay, here is a pet peeve of mine, I think every stored procedure, function, view etc. should all contain a block of code I refer to as a preamble. If yours doesn’t I strongly recommend you start adding it. It drives me crazy when I see code with no documentation of any kind telling me what it is for and when it was written or changed. Why? A preamble documents the use, need, and changes for the code. It also leaves bread crumbs as to how why and what you did. I don’t know about you but I may code something and not have to change it for two years.…
-
This year will be my 6th PASS Summit that I will have attended. Some people have asked me why I still go, and what I get out of Summit that I don’t get from attending and speaking at SQL Saturdays. That’s an easy one for me to answer, but a long answer at that. Networking First and foremost, it’s for the networking. Getting to meetup with so many other like minded people is gratifying. This networking allows you to exchange ideas, war stories, and downright geek out with others that know what you are talking about and don’t have their eyes glaze over when discussing optimizer internals. On top of…
-
Let’s take a look at what a Sequence is in relation to an Identity Column in SQL Server. Did you know Sequence even existed? I didn’t until I was asked about them. It’s amazing how much you can skip over and never notice in SSMS. See this little folder, ever notice it under Programmability in Management Studio. Yep it’s there, SQL Server has this very handy thing called Sequences. Sequences are a relatively new feature that have only existed since SQL Server 2012, but have long existed in Oracle (where there a no identity columns). What is a Sequence? Per MSDN, A sequence is a user-defined schema bound object that generates…
-
Today I ran into something on a client server I unfortunately see too often. The DBA goes through the trouble of configuring and setting up alerts\operators but doesn’t really understand what the options in the configurations mean. So unfortunately, that means they take the CYA (cover your ass) approach and they check all of them. Now, not only have I seen this with alerts but also with things like security configurations as well. My advice is to always in to take a second and research what each option is before you check the little boxes, especially when it comes to security. Always follow the rule of less is more. In…
