I’ve really got to make a plan here at some point. So far I’ve been just putting down things as I think of them. In September I had you set up a home lab, and in October I had you download and attach some sample databases. What would have been the next logical step? Creating a database of your own of course. So here we are 7 months later and it’s time to create your own database!

Things to make you go hmmm.

• What are the default locations for the data and log files.
• How big is our database going to be?
• Do the default locations have sufficient space or should we get more? Or maybe put this database on it’s own set of drives?
• Do we have an estimate for the growth over the next month (during setup)? Year? 5 years?
• Are we going to need in-memory tables? Filestream?
• What type of recovery do we need? Full (point in time recovery) or Simple (recover only to the times full and differential backups ended)?
• Who is going to need access to this database? (This is a test database so just us obviously.)
• Do we need any database level configuration changes to be different from the default? (compatability level, MAXDOP, collation, etc)
• Depending on your needs you might even be asking: Should this database be in the cloud?

Requirements for your new database.

(10 points for each task.)

• The database should have 3 filegroups. One of them read only.
• The non-PRIMARY read/write filegroup should have two files.
• The non-PRIMARY read/write filegroup is the default filegroup.
• Make each new data file 100mb (leave PRIMARY as the default) and the log file 50mb.
• The size for the file in the read only filegroup should have a max size of 100mb.
• The autogrowth on the log should be 10mb with a max of 1gb.
• The autogrowth on the PRIMARY filegroup should be 50mb with a max of 500mb.
• The autogrowth on the non-PRIMARY filegroup should be 100mb with a max of 10gb.
• The database should be in SIMPLE recovery.
• Make the collation SQL_Latin1_General_CP1_CS_AS.
• MAXDOP should be 2.

Now if you want to get ahead, next month I will have you take this database, and change literally each of these settings.

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 create this database through a script, without using BOL.