Best Practices for a Database Creation in SQL Server

Creating a new database in SQL server is a very common and general task for professionals dealing with SQL in an environment. There could be different ways of creating a new database. You can literally run a basic create DB statement, which will follow the default settings of your instance, or you can pinpoint every single thing related to the creation of the database (like number of data and log files, their locations, growth rate, max growth size, filegroup etc.) After creating a new database, it is very important to have it configured in such a way that it meets the need of the user with performance, security, availability, and recoverability. I have described the points below that definitely need to be considered while creating a new database.

• Create the needed database with default data and log file location as per database settings

Use Master;
Go
CREATE DATABASE databasename;
Go

Or Create the needed database with specifying the location of data and log files with specific file growth

USE master;
GO
CREATE DATABASE databasename
ON
( NAME = databasename_data,  
    FILENAME = 'Drive:\folder\databasename_data.mdf',
    FILEGROWTH = 64MB) 
LOG ON
( NAME = databasename_log,  
    FILENAME = 'Drive:\folder\databasename_log.ldf',
    FILEGROWTH = 64MB);
GO

Note: This is always a best practice creating a database with data and log files in a different drive for better performance in the long run.

• By default, the creator of the database becomes the owner of the database. You don’t want an individual account to be an owner of a database in case they leave the company and their account gets disabled. It is a best practice to make either ‘sa’ account the owner of the database, if sql authentication is enabled, or assign a service account to the ownership.

Change the owner of the database to ‘sa’/service account

Use Databasename;
GO
EXEC sp_changedbowner 'sa/service account'
GO

• If the newly created database is for a production environment and requires a point-in-time recovery, you should make sure the database is in full recovery mode. If that is not the case, it can be put into simple recovery mode. By default, newly created database options are gathered from the model database. So the model database can be set to either full or simple recovery model based on the need for that instance.

USE master ;  
ALTER DATABASE databasename SET RECOVERY FULL 
GO

Or

USE master ;  
ALTER DATABASE databasename SET RECOVERY Simple
GO

• There is ‘guest’ user account in every SQL database which is also disabled by default. If it is enabled unplanned in the model database, it will have the same thing in every newly created database as well. This should be double checked and disabled if enabled unplanned.

Use Databasename;
GO
REVOKE CONNECT from guest
GO

• You should also check if there are any specific needs to change the compatibility level and collation of the newly created database based on your application need. If yes, you can right click on the database, go to properties, and click on options tab to change it per your need.



• The next thing to consider is who are the users of the database? There are multiple default membership roles you can choose to assign to the user based on your need. You can right click on to the user tab inside the database and click the new user, then you can choose the type of membership you want or assign specific object level permission based on your need.

• For better performance and Recoverability you also need to make sure to add the databases in your maintenance plans. This should include database backup, index rebuild/reorg, update stats, and database consistency check.
• Depending on the criticality of the data in the newly created database, you must consider if you need to take advantage of Transparent Data Encryption(TDE), which can protect your data, log and backup files from being stolen and restored without the key.

Note: Special care should be given while configuring TDE. If you lose your encryption key and you don’t have a backup of the key you won’t be able to restore the database in the result of a disaster.

• You also need to check if this database needs to be a part of any high availability solution. Check for AlwaysOn, mirroring, log shipping, or replication being used in your environment and if this database needs to be included on them.
• A lot of organizations use third-party tools these days to monitor the availability and performance of the databases. If that is the case for your environment, make sure to include it in your monitoring tool and configure the different settings for how you want it to be monitored.