SQL Server: Advanced protection

Protecting your SQL database means saving thousands of enterprises in terms of productivity, compliance with regulatory requirements, and prevention of downtime and data loss. Here are some tips and tricks to help you deploy your SQL database correctly and make your data available when you need it most.

In addition to the data itself, SQL performance contains transaction logs and a system database. Both must be carefully protected for a smooth application recovery.

Monitor workloads around the backup window

SQL backups can be performed when the user is actively querying the database and transactions are being processed. SQL backups utilize a lot of system resources, especially I / O, so it is recommended to perform a centralized full backup when the system load is light.

Shorten data backup

If the overall performance is slow due to the long backup window, you can take some steps to reduce the time it takes for the system to perform the backup. One way to save it is to back it up to disk. If you back up to disk before offloading to another backup system, be careful not to back up to the same disk that is used to store the database or transactions.

Copying the database to another array not only prevents I / O overload, but also ensures that the database is available in the event of a primary system failure.

Use different backup methods

SQL Server offers a variety of backup methods, including full backups, differential backups, and transactional backups. It is built into SQL Server. The choice of backup method depends greatly on your environment. Specifically, it depends on the size of the database and how important it is to your business.

Full backups can overwhelm your servers and storage systems, so plan carefully how often you need to perform full backups and differential or transactional backups. Small databases that are not too large and do not change frequently can only be backed up daily or weekly. Mission-critical transaction databases should be backed up as often as possible.

Frequently backup transaction logs

Next to the database, the transaction log is the most important data in a SQL server database. The logs cover the activity and can be used to perform a PiT (point in time) restore.

Note: Since transactional backups only back up to the last transactional backup, a full restore can mean performing a series of transactional backups to fully reuse the database.

Transaction logs should be run at least several times a day, every 10 minutes for highly active databases.

SQL system database backup

The system database is another important component of the SQL Server application, including both msdb and master. It contains important data such as system configuration and is required for a complete restore. However, system databases change infrequently and should be backed up at least weekly, or especially if the database is active, daily

One of the biggest factors affecting the performance and protection of Microsoft SQL Server is the I / O of the disk subsystem.