A Fine Slice Of SQL Server
Stuart Moore has started on an ambitious project:
So I’m doing another 31 days of posts, hopefully consecutive days but given current pandemics and sporadic working hours it may not be.
This is the index of all the posts, I’ll update it as each one is posted.
Click through for the index page, which will contain links to all of the post. So far, Stuart has 4 up.
Comments closedKevin Hill takes us through a nice use of dbatools:
My client asked me for a way to automate test restores and CheckDB either ad hoc or on a schedule, but not for certain databases that are close to or over 1TB in size.
Read on to see how Kevin pulled it off, all in under 4 hours.
Comments closedDave Bland talks about a few useful tables in msdb:
How long a database takes to backup is something that over the years I have been asked to get. These requests come for different reasons, sometimes it could be to find out how much it has increased over time, sometimes it could be to see if the backup job is interfering with other jobs and sometime it isn’t about duration at all, it is more about showing the backups were completed. Over the years I have had a number of auditors ask of backup history.
In order to get this information we need to pull data from two tables in the MSDB database, backupset and backupmediafamily.
Read on to learn about these two tables and to get a sample query. On systems with a large number of databases and a DBA who loves frequent transaction log backups (like I do), this table can get pretty big, so don’t forget to prune that data over time.
Comments closedEduardo Pivaral walks us through what it takes to get point-in-time recovery of data in SQL Server:
Nowadays, data is a precious asset for companies today. If you are a database administrator (by decision or by mistake) or simply you are the “IT guy,” you have the mission of guarantee all the data is backed up and accessible for recovery.
Trust me, even when you could think you have the more reliable hardware on the planet, or you have multiple database replicas around the globe, anything can happen (a user deleting an entire schema by mistake, an application updating the wrong records, some process crashing, a lot of things can happen).
So trust me and don’t question me, just backup all your databases regularly.
During my time as a DBA, I think the most frequent reason for needing point-in-time backups was “We goofed up at 2:20 PM and need to get the database back to that state,” where goof-ups typically involved mass updates or deletes of data.
Comments closedNiko Neugebauer has some advice on how to clean up backups which live in Azure Blob Storage:
Continuing the topic of the Backups to Azure Blob Storage that I have kind of kicked off with the post Striping Backups to Azure Blob Storage, I want to touch on the important aspect of “keeping it clean” – thus deleting the old backups.
On the regular Windows Server this is a rather easy task, and if you are using a standard maintenance solution, such as Ola Hallengren’s Maintenance Solution or any other one. You can also use the regular SSMS maintenance (*cough* for whatever reason that is unknown to me, that you might wish to *cough*), or you can easily set up a regular Windows Scheduler with Command Line Batch or Powershell or whatever tool/script/language you like.The situation is quite different with the Backup To URL functionality, the one that is available since more than 6 years (and the good old SQL Server 2012 has even got a support for it in a certain Cumulative Update – SQL Server 2012 Service Pack 1 CU 2, to be more prices)
Niko goes through five different methods you can use, so check it out.
Comments closedJohn McCormack explains why and how to stripe database backups:
First of all a definition of what we mean when we say stripe SQL Server database backups. When we talk about striping a backup, it simply means the overall backup is distributed across several files, often on different drives. Microsoft’s description.
The biggest reason for me is the time savings. I’ve done a few performance analyses and in my various circumstances, the optimal number of files to create (for minimizing full backup time) has consistently been greater than 1, even when I’m writing all of the files to the same underlying drive. The only downside I see is having to manage more files.
Comments closedGuy Shilo shows how you can back up an Elasticsearch cluster:
Elasticsearch is facing the same challenge and it’s built-in backup method is snapshots. Unlike classic storage snapshots, Elasticsearch snapshot can be stored remotely on external storage systems, and that is supposed to enable them deal with large amounts of data.
Snapshots can be stored on a shared file system (mounted on all cluster nodes), on all major cloud storage providers (Amazon S3, Azure and GCS) and on HDFS.
The documentation can be found here.
Read on to see the demo. Even if your Elasticsearch data is not the home of record and you could rebuild the cluster doesn’t mean ignoring backups is wise.
Comments closedJosh Smith shows us how to back up a Cosmos DB collection:
Now that ADF is a trusted service I wanted to document the state of my current solution since I’ve been able to dump the hack-y PowerShell script I put together. I haven’t been able to get the level of abstraction I’d really like to see but overall I think I’m pretty happy with the solution (and I still get to include a hack-y PowerShell script). My solution consists of
– a control pipeline,
– a notification pipeline and
– 1 pipeline for every Cosmos DB service I want to to work with. (This is because I wasn’t able to figure out a way to abstract the data source connection for the Copy Data task.)
Read on for the solution.
Comments closedArun Sirpal takes us through restoration of a database from a URL:
I think many have covered how you should backup your SQL Server database to Azure storage (also known as backup to URL) but what about restoring? Lets assume you have setup backups and they are working, this is what I usually do.
It’s very similar to restoration of databases from disk, so nothing too crazy.
Comments closedAndy Mallon has some tests for us:
If you restore a database, what does that do to the plan cache? Well, let’s start by looking at the documentation for RESTORE. (Emphasis mine)
Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: ” SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations”. This message is logged every five minutes as long as the cache is flushed within that time interval.
Yikes. That first sentence sounds like it is going to clear the cache for the entire instance.
Read on as Andy tests this and (spoiler alert) changes the documentation.
Comments closed