Simplicity can be deceiving. Microsoft SQL Server is a relational database engine that allows you to create your first database as soon as the installation is complete. This apparent simplicity of the product, just a few mouse clicks, makes you believe that your installation is correct and flawless... The reality is quite different.
Appearances are deceiving
Unfortunately, appearances are deceiving. An installation requires consideration of Best Practices based on elements such as:
- Your current needs while anticipating the future
- Your physical or virtual configuration
- Your type of storage
- Your network configuration
This approach will guarantee longevity and be less costly than a complete reorganization, which in most cases requires a production shutdown.
Security
An MS SQL Server installation starts with its foundations represented by the Windows operating system. If possible, the server will be part of an Active Directory where a dedicated service account for MS SQL will be created to better manage security. Why not delegate security to the System Administrator by using AD Groups within your SQL instance?
Edition choice: one of its consequences
The choice between Standard and Enterprise editions is important. There is no point in installing a single SQL Standard instance on a server with 32 cores because only 4 of them will be used due to the limit set by the Standard edition.
Disk resource choices
Lors de l'installation, les ressources disques sont importantes tant pour la sécurité de vos bases de données que pour les performances.
During installation, disk resources are important for both the security of your databases and performance.
For security:
- On disk C: reserved for the operating system and shared MS SQL programs
- On disk D: SQL engine + system databases master, model, tempdb, msdb, distribution
- On disk E: user databases, .mdf and .ndf files
- On disk F: transaction logs, .ldf file and tempdb database
- On disk G: local backups
This is a typical example. It can be further improved by adding a resource for indexes and others for storing partitions. But as mentioned earlier, this is organized.
For performance:
- Use file groups for data, others for indexes, partitions, consisting of several secondary files not exceeding 32 GB in size. By doing so, the engine will write information in stripes.
- For large databases, it will be possible to back up a database by file groups rather than backing up the entire database.
Be cautious about information you may find on the Internet about creating a database with a single .mdf file of 800 GB or even over 1 TB. Yes, it is possible, but it is better to be reasonable, especially when mixing tables, views, indexes, system objects, etc., in a single file!
Sealing
When your instance is created, there are important post-installation actions to make it "sealed." Publishers, not knowing the configuration of their clients' servers, will rely on the system model database to create theirs. If this model database is not configured according to your requirements, the result will not meet expectations. Many clients use only the 2 main files that constitute a minimal database: the .mdf and .ldf files. It should be noted that the Master Data File (mdf) contains the system information of its own database, such as objects (tables, views, indexes, etc.), users, and their associated rights. Creating your own objects in this file is the same as creating tables directly in the system master database of the instance. It is recommended to create file groups for user objects.
Type of database usage
Depending on the use of the instance in OLTP mode (short transactions such as insert, update, delete, merge, select, etc.) or OLAP for decision-making, the parameters will not be the same and will need to be adapted from your specifications.
Backup and Recovery
There are 2 main methods for recovering a database:
- SIMPLE mode, which only guarantees data recovery from the last backup. In this case, you allow the loss of data entered since the last backup in case of a crash.
- FULL mode, which guarantees complete database recovery in case of a crash.
Depending on the recovery mode, the backup procedures will not be the same. Many think that a Full Backup backs up the database as well as the transaction log, but this is false. It only backs up the database, i.e., the contents of the .mdf and .ndf files. This is why it is common to find a 1 GB log for a 300 MB database. To ensure the maintenance of information in case of a crash, the transactions contained in the .ldf file can only be purged after a BACKUP LOG and not a BACKUP DATABASE. If you find in the backup procedures the switch from FULL to SIMPLE recovery mode to empty it and then from SIMPLE to FULL to secure the database again, it means the person did not understand the principle and risks data loss in case of a crash!
Disaster Recovery Plan (DRP)
For DRP solutions, backup procedures deserve even more attention. For example, do not limit yourself to implementing backup procedures only on the active server of an SQL mirror. Indeed, in case of a switch to the backup server, these backup procedures will not be present on this server. However, if these backup procedures are present on both servers, configure them so that they do not generate alerts from the passive server due to the status of the databases on this server: in RECOVERY mode and therefore inaccessible.
Best Practices
It is never too late to get back on track with "Best Practices." Often, these have avoided replacing the hardware configuration, which would have only postponed performance problems instead of correcting them. DEEP has extensive experience in the implementation and administration of Microsoft SQL Server. Want to know more about this topic? Contact us for more information.
