Recently I needed to use temporal tables. And not just for the job history table in a managed instance. In the vein of try it before you need it, (well, maybe a bit late for me I guess) let’s play with them a bit this month.

• Create a new table with a temporal table. (15pts)
• Run some inserts, updates, and deletes on the table (0 points, this is way too easy )
• Review the data in the temporal table (5pts)
• Create a table on a high-activity table in a test environment (or a regular table and use a tool or some scripts to create high activity) and monitor the temporal table during the activity (20pts)
• This is going to take some time, but over the course of a week or so add/modify/remove data from your table (5pts)
• Write a few queries to view the data at specific points in time. (15pts)
• Turn versioning off and back on again (5pts)
• Modify the name of the table (5pts)
• Modify the name of the temporal table (5pts)
• Add then modify the retention period (5pts)
• Read up on other ways to handle retention (10pts)
• Remove a temporal table from a table (10pts)

It’s been a bit since I did one of these so let’s go back to the basics. Best practices. We all know they exist and probably know at least a few. A while back I talked about the fact that best practices change over time. Turns out they also change rather dramatically depending on your environment. Kendra Little (blog|twitter) recently posted a discussion of index maintenance on Azure SQL DBs and Managed Instances and how the best practices should be somewhat different than on-premises.

Obviously, cloud environments have their own unique requirements but even when on-premises things are changing. Storage is getting faster and bigger, and memory and CPUs are more plentiful than ever before. Is it any wonder that best practices are going to change?

So this month take a look at your best practices. Start with some research. What are considered the current best practices? I’ll give you a hint too. Not all of it is going to be what Microsoft says (blasphemy!). Look at what other DBAs out there are doing and what they are finding to work the best. Kendra’s post above for example. Then take a look for any anti-patterns in your code, review your maintenance plans (FYI I mean your plans for maintenance, not actual maintenance plans. Don’t use those.), your backup plans and schedule, and last but certainly not least, check your instance and database settings. Make sure all of it makes sense given your current setup. Far too often things get put in place and forgotten about for years. And of course last but not least make plans to update what you are doing with the current best practices. This should include testing and figuring out if the best practice is what’s best for you.

SQL Server 2022 has been released! Long live SQL Server! No really, I’ve still got a while before retirement. I need SQL Server to stay popular for at least another decade or two.

One of the things that I’ve found the most useful over the years is that I’m at least familiar with the majority of what’s available within the areas of SQL that I work in. Can I use them without having to look anything up? No, not in a lot of cases. Do I at least know they exist? Yes. One thing that’s helped me (aside from following a bunch of SQL experts and reading their blogs) is reading over the list of things that are new in the latest version of SQL. And the version before that, and the version before that, etc. In fact, your homework for this month is some light reading. Pick a version you are particularly familiar with, in my case 2016, and read all of the “what’s new pages” from that point on.

The What’s New pages for 2016 and up are pretty easy to find.

• 2016
• 2017
• 2019
• 2022

The older versions are a bit harder and require you to go into the SQL Server Previous Versions documentation.

This is not going to qualify you to use any of the stuff mentioned, but at the very least you’ll have a general idea of what’s going on. And who knows, you might find something that excites you and you can do some reading/practicing on that particular feature. Batch mode anyone?!

Back in July I suggested that you get your resume up to date. It’s something I’d wish I’d kept up with and still something I recommend. Here’s what I did right. About 10 years ago I had a thought. I like working (I do truly like what I do), but in particular I like getting paid. Getting paid requires that I have some form of job. Whether that’s working for myself or working for someone else. Now, I know myself. I know I enjoy being a DBA and I prefer to work for someone else. All that said, if I lose my job I need to find another one. So what can I do now to help me find a job later.

Here’s what I’d like you to do this month. Have that conversation with yourself. What can you do today that will help you find your dream job in the future. Again, that might be working for yourself, working for someone else, working in another industry, etc. That’s going to be very personal to you.

Here are some suggestions. Obviously these aren’t inclusive but here are some things that helped me, or at least that I think would have helped. And also obviously, if I say SQL Server and you’re thinking I’d really like to get into woodworking instead, then replace SQL Server with woodworking.

• Start a blog <- This is what I did obviously.
• Get on twitter and network with the SQL Server people there. <- Also something I did.
• Join your local SQL Server group <- Did this but not extensively
  • Groups like this always need help. Help out how you can. Maybe join leadership eventually.
  • Speak. You absolutely do not have to be an expert to speak. Just come up with a presentation that shares your experience. If you need help doing so there are a lot of people out there who will help. See the second suggestion.
• Study. Learn a new skill, work on one you already have, whatever. Pick something you are interested in so it’s easier but spend some time, and hopefully it can be work time but check with your boss about that, improving yourself. Note: Again, this could be something like performance tuning, or if you want to go the woodworking route it could be the correct way to build a table.
• Get/keep up with certification(s). This is pretty similar to the study entry but it’s more codified. You’ll have a group of things that need to be studied, and when you go to interview it can help you get in the door. Note: Certificates are not proof of knowledge. You’ll still have to do any tech tests they have and demonstrate that you know what you are talking about. If you have a certificate but not the skills related to it this will almost certainly tank any attempt to get a job at that place. That said, if it’s a new skill, Azure for example, it can be really helpful in demonstrating your intent to learn even if you don’t have any professional skill.

Really the important part of this months homework is that conversation. Decide what you want and how you want to get there. Future you will thank you.

In August we started taking a look at replication. We learned some of the terms used, and set up a (probably) pretty basic example of replication. Now it’s time to work with it a bit more.

• Open the Replication Monitor.
  • Right click on different entries. See what options you have.
  • Left click on them and see what shows up.
  • Insert a tracer. What does the tracer do? What does the output mean?
  • Configure an alert. There are a fair number of options here but hopefully you’ve created an alert before so this should be pretty familiar.
  • If you have the option connect to a listener for an instance that has replication and open the replication monitor. What was different?
• Go to the subscriber of an publication. Insert a row into one of the replicated tables. To make this easier make sure that table has a primary key. Then go back to the publisher and insert the same row into the same table. Give it a few minutes and check the replication monitor again. You should see that this is a particularly bad idea. How do you fix it? How do you prevent it?
• Add a new table to the subscriber database. Insert some data into that table. Did that cause a problem with replication? What are some reasons you might do this? What are some reasons why you shouldn’t? How do you avoid problems?
• Modify a replicated stored procedure. Modify a replicated table. Add a column, or change a column’s datatype. Take a look at the subscriber. Did the changes make it there? How about if you add an index?
• Add a second subscriber to a publication. Insert some rows into a replicated table (to be clear add it on the publisher). Take a look at both subscribers. Did the data make it? Why might you want multiple subscribers?

I had every intention of doing Replication Part 2 this month but had a thought I liked better. I will get back to replication but let’s do something else this month.

I’m a firm believer that the GUI’s Microsoft has made for us are amazing. Azure Data Tools and SQL Server Management Studio are both awesome tools. That said, this month I have a challenge for you.

Scripting only.

Pretend like you are going to have to use sqlcmd, or the DAC (dedicated admin connection). You have no choice, you have to script rather than using a GUI to do the work for you. That means that if you need to take a backup you write a script. Want to see the list of databases? sys.databases. etc. Virtually anything can be done via scripting, and frequently you can do more, faster by scripting things out. Obviously don’t let this stop you from getting your job done. There are a few things that you are better off using the GUI. (Unless you decide to use PowerShell?) But can you force yourself to script? Spend a few extra minutes looking up that command you need?

I promise you it’s worth it. After some practice I can start a backup or restore many, many, many times faster with a script than I can with the GUI. (I mean assuming I’m not going to be restoring a couple hundred log files. Although to be fair, I can do that pretty quickly too.)

So. Challenge given! Do you accept?

You’ll hear a lot of people complain about replication. Including me. That said, replication is an amazing tool. There really isn’t anything quite like it. Availability Groups, Mirroring, and tools like that will let you create a copy of a database and keep it up to date. But what if you have a terrabyte+ sized database and only want to copy a couple of small(ish) tables? That’s where replication comes into play.

There are things that replication can do that HA (high availability) tools just can’t. Copy just specific tables or code (stored procedures, functions, etc), copy a portion of a table (only the rows where the last names are between F and H), create multiple updatable copies of a database that all update a central repository, or even update each other so that all copies contain the changes from all of the other copies. That’s some really useful stuff.

So let’s get started with replication. We’ll be working on it for a few months so fair warning.

• Learn the terms of replication. For example subscriber, distributor, article, and any others that you can find.
• Set up replication on at least one of your lab instances.
  • Set up a distributor. Make a note of some of the options here.
  • Set up a publication.
    • Select multiple tables, but not all.
    • For at least one table do not include all of the indexes in the replication.
      • You are going to want to know what this looks like when one of your indexes doesn’t show up in your replicated copy.
    • Select multiple stored procedures, views, and functions, but again not all.
  • Set up a subscription. It can be on the same instance, or a different one.
  • In your new database (the subscriber) set up the permissions so that users can only read from the replicated tables.
  • In your new database create a new table and give at least one user access to both read and write to it.

Resumes have been on my mind a lot recently. I’ve been thinking that I wish I’d kept mine up to date over time. So I’d like you to do better than I did.

This is just a recommendation, but the time to get ready to find your next job is now. Not when you are in shock over losing the last one. Do everything you can to prepare ahead of time. Education, networking, an online resume (blog anyone?), and yes, keeping your resume up to date. You would be amazed at how much you can get done early so that when the time comes, as it does for almost all of us, finding your next job will be far easier.

A few years ago I gave you homework to create a SQL Agent Job and play with it a little bit. This month I want you to either take that job (or another) and work with it a bit more. We are also going to play with a few other parts of the SQL Agent.

• What is the SQL Agent running under? (5 pts)
• Do some reading on why we care. (5 pts)
• How far back does your job history go? For this job and/or all of your jobs combined? (5 pts)
• Extend that history out some. (5 pts)
• Open the Job Activity Monitor and look at what it shows you. (5 pts)
  • Right click on a job and view the history or open the properties. (5 pts)
• Create a proxy. (20 pts)
• Make at least one step in your job run off of the above proxy. (10 pts)
• Look all of the proxies that can run a CmdExec job step. (5 pts)
• Create an Operator. (5 pts)
• Create 2 different types of Alerts.
  • One should notify the Operator you just created. (5 pts)
  • The other should start your job. (5 pts)
  • Test the alerts to make sure they work. (10 pts)
• Go back to the Operator and make sure they are going to be emailed (or paged) for at least one of your alerts. (5 pts)
• Open the SQL Server Agent log. Skim through at least some of the data. (5 pts)

No one knows everything. Ask any senior level person in IT and they’ll tell you that they spend plenty of time looking things up. Frequently I’ll just do a web search, but, at least in the case of MS SQL Server, Books on Line is one of my top choices. It’s not always perfect, but it’s certainly a good place to start.

So here’s what I’d like you to do. Look up at least 10 of the following in BOL. Try to pick ones you don’t know, or at least don’t know well.

• What are the permissions required for OpenQuery?
• What command should you be using in new development instead of RAISERROR?
• How many expressions can be included in the SELECT list?
• Look at the example for the query hint MAXRECURSION.
• Review the syntax for creating a login.
• Is the text data type supported by the OUTPUT clause? Are there any restrictions if it is?
• What is a generic term to return the identity column in the SELECT list?
• What permissions are required to alter an ASYMMETRIC KEY?
• What does DBCC OPENTRAN do?
• Look at the example for using a CTE with an UPDATE.
• What type of lock is taken on the table when running sp_estimate_data_compression_savings?
• What is a DATABASE SCOPED CONFIGURATION?
• What are the data types for the different outputs of sp_help?
• When using COVERT to convert to a datetime data type what does the style 126 return?
• When you run sp_updatestats what happens with disabled indexes?