May 2, 2018 by Kenneth Fisher
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.