Here we are again. The beginning of another month. At the beginning of each month I put out a SQL Homework post with the intent of getting junior DBAs/database developers to try new things, mid-level DBAs/database developers to practice things they should already know, and Sr DBAs/database developers a boost to their ego (Ha! That’s easy!) or a pointed reminder that they may not know everything (oops, missed that). Last month I asked you to create a database with a rather specific set of requirements. This month

It’s a year later, new things to make you go hmmm.

• How close was our 1-year growth estimate? Do we need to adjust our 5-year estimate?
• Do the current file locations have sufficient space or should we get more? Do we need to move some of this database (or all of it) to a new set of drives to free up space for other databases?
• How is the space for our backups? Are we able to maintain the number of backups we want on disk? What is our total retention of backups? I.e. are we moving them from disk to tape or some other storage? If so how long do they stay on that other storage?
• Security changes over time, should we do a quick review and clean up any permissions that are no longer needed?
• What is our current database version? Is a new one out with features we want? Should we start thinking about an upgrade?

Changed requirements for your database.

(10 points for each task.)

• Turns out they want a new version of this database. Make a duplicate copy of the database named [dbname]_v1.
• Rename the existing database [dbname]_v2.
• Rename the logical and physical file names as well.
• We are running low on space. Move the file for the primary filegroup to a new location.
• Add a table to the read_only filegroup. Put 10 rows of data in it.
• We need a new filegroup. It should have two files. 100mb each, 10mb auto growth, max size 300mb.
• Make the new filegroup the default filegroup.
• Change the database to FULL recovery.
• We’ve added some new CPUs, change MAXDOP to 4 for this database only.
• The new cardinality estimator isn’t working out too well for us. Turn it off for this database only.
• Bonus points: Set this database up to allow for memory optimized tables.

And as always, yes, I realize that if you are a Sr DBA all of this is easy stuff. This isn’t really targeted at you. It’s meant for Jr and Mid-level DBAs who may not be quite as comfortable with some of these tasks. That said, if you are a Sr DBA, I challenge you to change up this database through one or more scripts, without using BOL. And if even that’s easy, do it in a different scripting language. Powershell maybe?