Filegroups are an interesting and useful (if not actually all that fun, despite the title) concept within SQL Server. They provide not just a way to group objects like tables together, but also provide a mechanism to help us to speed up the backup, restore and recovery of the databases. And sometimes they can even give us a little help with the performance or to migrate data to disks that have more space available.
In this post, focus will be in archiving a table that we no longer need by making a read-only copy of it. In a real world scenario we’d also consider putting that to a slower disk, if it’s not frequently used, but my demo environment only has a single data disk.
Over the last two years I have been working lot on improving the tooling and processes related to database development at where I work. One concept born out of this work is what we have started calling a “Database-as-a-Code” model. Originally the idea was to introduce some of the good practices, such as version control, build automation and unit testing to database development. Over the time it has evolved to include even more, in an effort to break down traditional silos between software developers, database developers and the operations people.
In this post I’ll describe some of the decisions we made and the steps we have taken in our Database DevOps journey.
I was recently involved in a query tuning work where we used synthetic, rather than production data, to validate the results of our query and index tuning work. We faced some issues with the generated data that had quite a severe impact on our testing, and that prompted me into writing this blog post. Lets start by first defining what is synthetic data. In my view synthetic data is data that resembles actual production data, but is artificial/generated. I have seen similar (and also more detailed) definitions elsewhere and I think it is a good one.
I also like to point out that there are plenty of good reasons for using synthetic data in testing, as production data is often strictly regulated and not easily available for testing purposes. However, you need to be certain that the synthetic data you are using is similar to what you have in production.
SQL Server has had the Data Compression feature for a while, ever since the version 2008, so it is hardly a new thing. However as it has been Enterprise Edition feature until SQL Server 2016 Service Pack 1, it is not something you see employed very often. Technically speaking, you could also compress data before 2008 by using NTFS file level compression on a read only data. However with the implementation of SQL Server Data Compression you could now do it inside the database on a page or a row level.
I was reading this blog post by Thomas LaRock (@SQLRockstar) about a database design mistakes and I’ll warmly recommend that anyone who is involved with database design should read it as well. It also got me into thinking about one database design issue which, in my opinion, is not taken into consideration often enough; Database Administration.