IMPORTANT: The majority of the settings you’re about to see are actually taken from the system database “model”. The model database is, as its name implies, the “model” by which all others are created. Therefore if you change settings, initial sizes, growth rates, and recovery options in the model database, then those are the defaults you will be presented with when creating a new database from scratch following the method below. If you have some settings that you wish to apply generically to all databases, then simply change them on the model database.
Open SQL Server Management Studio and wait for the login screen to appear. Log in to your SQL Server instance using whatever credentials you have. In my case I’m logging into a local SQL Server using the sa account.
(Note: I wouldn’t normally use the sa account, but this is a brand new install at time of writing and I have not created any accounts yet)
Once inside, right click “Databases” in the Object Explorer panel to the left of your screen…
From here, click “New Database…” as shown. This will present you with a Windows Form…
For the most basic of database creations, simply type your database name and click OK. That’s literally it.
However, read on if you want to see the options in a little more detail and therefore make some better decisions in regard to your database setup…
This is pretty self-explanatory really. It’s simply the name by which your database will appear and be referenced within SQL Server. In this case I am calling my database “boredDBA”…
You will see that SQL Server automatically uses the database name to fill in the logical names of your database data and log files. You can overtype these if you wish with any name of your choice, but to be honest the defaults are pretty good and don’t really need changing.
This is pre-entered as "default" and will therefore simply take on the username of whomever is creating the database. Therefore if I was logged in as “myDomain\myUser” then the Owner of the database would be listed as “myDomain\myUser”.
This can, however, have some undesirable consequences. What happens if myUser leaves the company and his account is deleted etc? Deploying CLR code to systems can also be tricky depending on the Owner of the database. Due to this, I tend to make sure all my database owners are the sysadmin account of “sa”…
Initial Size & Autogrowth
At this point in time we’re not going to deal with Filegroups as that will be covered later in this article, therefore we’ll move straight on to the Initial Size of the database.
This is an area you want to aim to get right at the start rather than deal with later as getting it wrong can be costly in terms of database performance due to both internal and external fragmentation of the database.
Internal fragmentation is an advanced topic to be discussed elsewhere, but external fragmentation is a less complex issue that I will cover with a quick example:
Let’s say you create a database, as above, with an initial size of 3MB and a growth rate of 1MB. The operating system will allocate you a 3MB chunk of hard drive and write your data file to it.
You then slowly start to load data to a total of 10MB. What happens is that once the database reaches 3MB, SQL Server requests another 1MB slot, so the operating system will allocate a 1MB piece of hard disk space. This happens a further 6 times. Therefore your database is now 10MB and you have 10MB of space on the hard disk.
However, the 10MB on the hard disk is in 8 separate files which, if the operating system is busy, could be considerably spread around the drive, therefore proving costly in terms of disk IO when trying to read and write data.
Planning ahead and starting with an initial size of 10MB would have solved this problem. Also, having a sensible autogrowth is crucial as if you’re loading 100MB of data at a time then growing by 1MB would be nonsensical.
Note: This type of logic is applicable to both the data and log files.
I would also recommend using fixed number growth instead of a percentage as this will help maintain internal fragmentation in the long run.
In my example, I’m going to be using my database for all manner of demonstrations and examples, I also have no shortage of space on my disk (another consideration, especially with regard to “unlimited growth”), so I am going to set my database as follows:
Lastly on this tab is the option as to where you wish your database files to be located and what you want the physical file to be called. If this is blank then the logical names (mentioned earlier) will be used.
You can alter the database and log file locations as you see fit, but in my case I’m using the default locations that I set up during installation (which is why I set them so that it’s easier when creating new databases).
Selecting the Options tab will present you with a screen similar to the following:
Here you can alter numerous settings to take effect within your new database. The majority of these are fine as defaults, but you may find that, over time, you find some work better for you if changed. Personally I like the defaults. The only options of note are Auto Shrink (NEVER, EVER, EVER turn this on)… and Database Read-Only which you may require when setting up such a database.
This is the main thing you would need to check on this tab when setting up a new database. This defines how SQL Server will utilise the database log file.
I will do an article solely on Recovery Models at a later date (and place a link here).
For my purposes I do not need point in time recovery or complex backup strategies, therefore I’m going to change mine to SIMPLE…
Collation is fine left as server default unless you have a specific reason to change it, and compatibility on a new database should generally be left alone unless you’re planning on restoring an older database over it, or running code that requires an earlier version of SQL Server.
Clicking on the Filegroups tab will show you the following:
In a basic setup you won’t have to worry about Filegroups and can happily just hit OK and you’re done. However, if you want to investigate then this is the place to do it.
Let’s add a filegroup to see what happens…
We now have an additional filegroup inventively named “FILEGROUP1”. You can see it has no files and is neither Read-only or Default.
Going back to the General tab, we could now choose to add another data file which we could then allocate to FILEGROUP1…
What this would now allow us to do, would be to allocate this data file to a completely different drive on our server, therefore splitting the database over multiple drives. This provides better disk IO for the database.
Also when creating tables and indexes we can specify to create them on PRIMARY or on FILEGROUP1. This allows us the potential to keep tables and indexes apart on separate disks (large performance increase) or have stale historical data on a large, slower drive whilst frequently updated and accessed data on smaller faster drives.
There are numerous possibilities that this presents if used correctly.
At this point in time I’m not going to use this and therefore will remove this filegroup and data file, but I will write an article dedicated to this as a follow up.
Filestream will be covered in a completely separate article.
So… click “OK” and your database will now appear. You’re now free to start adding users, tables, and data…