This is likely the last in a series of three posts about backups and restores. My aim with this series has been to give you an idea of the options available and a basic start on using those options. I’m not trying to go in-depth on the subject of backups and restores.
Today will be about getting you going on the concept of restoring databases in SQL Server. I’m using a local install of SQL Server 2017 on my desktop PC to demonstrate these techniques.
Last time we discussed 5 backup types for SQL Server. Now I want to introduce you to five ways to make backups in SQL Server. I can’t possibly explore all the options available with each of these six methods. There is a lot to cover for even a “simple” topic like backups.
For those readers who like the SSMS GUI, there is good news. SQL Server Management Studio offers a fairly straightforward method for backing up a database. Once you connect to the SQL Server instance containing the database you want to back up, left click on the “+” sign next to the Databases folder. Then right click the name of the database you want to backup, choose Tasks, then choose the Backup option in the fly out menu.
One of the foundational responsibilities of a DBA is making sure that backups are available. SQL Server has several foundational types of backups that can be made. Before I dive into that though, I want to ask a question that seems to have a fairly obvious answer.
What does a full backup accomplish? A full backup, creates a backup of the entire database. It is all-inclusive, containing the entire database structure and all the data. When a full backup starts, the Log Sequence Number is recorded and when the full backup completes, the Log Sequence Number is recorded. This Log Sequence Number is the mechanism used by SQL Server to know what order INSERTS, UPDATES or DELETES occurred in. As such, having the beginning and ending Log Sequence number recorded as part of the full backup allows for a transactionally consistent backup because the full backup is aware of the changes that took place while the backup was being made. This allows for recovery of those transactions during the restore process. This full backup serves as the base backup, or starting point for all differential and log backups after it. Each time a new full backup is taken, a new base backup point is created.
In late 2019, a project that had been in progress for well over a year finally came to a conclusion. I had collaborated with a number of people to migrate a stand alone SQL Server 2008 and two stand alone SQL Server 2008 R2 instances. Each stand alone SQL Server was migrated to a three node Availability Group. Here are a few things learned along the way.
First, consider using automatic-seeding if you are migrating to AlwaysOn Availability Groups. I hadn’t previously worked with anything newer than SQL Server 2014 so I was unaware of this feature. It is new as of SQL Server 2016. In this case, the old SQL Servers were migrating up to SQL Server 2017, so this option was available to us. For a couple of months leading up to the migration event there had been various tests involving the set up of the AGs. One of those tests involved restoring a full backup followed by one or more log backups over on the secondary nodes. As the migration date approached, we started experimenting with auto seeding.
For those who have taken an interest in the latest SQL Server release, SQL Server 2019, you may have noticed a new feature called Data Virtualization. Perhaps you’ve seen a presentation about it, or maybe you’ve read about it in Bob Ward’s book entitled “SQL Server 2019 Revealed.”
I myself was curious about this feature and so I set out to see if I could use it to connect to an AS400 iSeries machine. Tom Willwerth, my friend at StraightPathSQL, and I worked on this process together. This post is primarily about walking you through the steps we recently took to get this configured.
I’m in part 3 of a series on installing SQL Server. In the previous post I began discussing steps to take after the install is complete. Because there are quite a number of things to consider post-install, this part 3 will continue discussing the post-install configuration items.
One of the things I always do after installing SQL Server is set up SQL Server Alerts. This is a free, easy way to find out about problems on the SQL instance, like read retry errors or other potential nasty issues. There are probably a number of places to find scripts useful for this task. The one I recommend is from Glenn Berry at SQLSkills.com. You can find his script at this link. One of my favorite things about Glenn’s script is that it has very well defined alert names that allow the DBA to easily know what server is involved when an alert is sent.
In part 1 of this series, I discussed pre-installation steps. There are many guides available online for the actual installation process so I’m not going to give a step by step installation guide here. Rather, I’m going to focus on what to think about after the install is complete.
Recent versions of SQL Server will now go out and download additional content for you before the install actually happens so you’re not left on the base, RTM release of the product. However, one thing to consider post installation is still, are you on the latest patch? How do you know? Where can you go to compare the version number of the SQL Server you installed to what is currently available? Well, you can look back at this post to answer that question.
In part 1 of a series on installing SQL Server, I’m going to discuss things to do prior to running the installation program for SQL Server. There are a number of install guides for SQL Server on the internet and plenty of recommendations around installation best practices and how-to’s. This will be what I’ve arrived at over the course of time.
This first step I’m going to introduce makes the assumption that this install is going to be for one or more databases that you’re planning to migrate from an existing instance. My first step is to run the Database Migration Assistant from Microsoft. This product is free and can be installed away from your SQL Servers. I run my install from my laptop. After opening the UI and starting an assessment, this tool will scan the source databases selected and determine if there are any breaking changes, behavior changes, or deprecated features between the source and target for your migration.
Have you ever looked at sys.dm_os_virtual_file_stats and saw horrible latency for your database files? Generally speaking, you want to see latency below 20 ms on your database files. much above that and there are some problems somewhere that need some tuning.
But, how do you know what queries are causing your issue? What’s causing those IO waits?
I responded to a question on SQLServerCentral.com about performance that is related to these types of scenarios. The poster wanted to know if there was a way to correlate pending io requests with the queries that were experiencing the waits using the DMV sys.dm_io_pending_requests.
Suppose you’ve been working on cleaning up SQL Agent jobs and their email operators and find that you have more than one operator with the same email address. How would you tidy this up to ease administration?
Let’s do a little setup to demonstrate the scenario. The T-SQL below will create three operators with different names but the same email address. Then it updates two of my existing test jobs to use those email operators when the job fails. I am also logging failures to the Windows Event Log.