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.
Change the owner of the database to ‘sa’/service account
Use Databasename;
GO
EXEC sp_changedbowner 'sa/service account'
GO
USE master ;
ALTER DATABASE databasename SET RECOVERY FULL
GO
Or
USE master ;
ALTER DATABASE databasename SET RECOVERY Simple
GO
Use Databasename;
GO
REVOKE CONNECT from guest
GO
- 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.
- 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.
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.