Monitor SQL Server availability

We recommend that you monitor the SQL Server service. It doesn’t matter if it's located on the same server as the rest of the Windows services, or if you use an external server or cluster to host the service.

One of the most common issues that can cause your Zylinc solution to become unavailable to the users in your organization is if a database runs out of available space and becomes read-only.

If you want to proactively prevent this, we recommend that you keep an eye on the following:

• Monitor that all disks in your SQL server always have a reasonable amount of free space available, for example at least 5 GB of available space per disk.

• Check recovery model and backup schedule for all your databases, as described in the following:

Manually check database recovery models and backup schedules

If your backup schedule doesn't include a regular transaction log backup, and Recovery model for a database is set to Full or Bulk-logged, the database transaction log files (LDF files) will expand until the disk is full. The database will become read-only and your Zylinc solution will become unavailable. We don’t want that!

You can use SQL Server Management Studio to go through all your databases and manually check the Recovery model for each of them. If you find a database that has its Recovery model set to Full or Bulk-logged, you should then check if the Last Database Log Backup is not too old, for example, older than 24 hours.

The advantage of frequent log backups is that they increase the frequency of log truncation, which results in smaller log files. That’s why, if the Last Database Log Backup is not too old, you can assume, with a high degree of certainty, that a backup schedule includes a regular transaction log backup for that database, which in turn indicates that your configuration is OK.

However, there's an easier way: Automatically check your configuration with the SQL script that we describe in the following.

SQL script to check database recovery models and backup schedules

1. Start SQL Server Management Studio and connect to the SQL Server that hosts ZyDB.

2. Click New Query.

3. Copy the SQL code from this file SQL_script_to_test_recovery_model_and_backup_schedule.txt to the SQL Query window.

4. Optionally, edit the first line of the script. The default value for the oldest accepted age of a backup is 24 hours (24*60 minutes). If you want, you can change that value to something else.

   For example, to extend the value to 7 days, change the line to:

   DECLARE @max_allowed_backup_age_in_minutes_before_warning INT = 7*24*60

5. Press F5 or click Execute to run the script.

6. If the script doesn't return any warnings in the TestCase or TestResult columns, your recovery model and backup schedule configuration is likely to be OK.

If there's a potential problem, the script can return any of these four warnings for each of the user-databases on your SQL server:

TestCase	TestResult
490 No backup for "<database name>": According to MSDB backup history, this database has never been backed up.	Info/Warning
500 Backup too old for "<database name>": According to MSDB backup history, the most recent backup for this database is too old (older than <minutes> minutes).	Info/Warning
510 No transaction log backup for "<database name>". Risk of disk full in the future: There is a risk that the database transaction log files (LDF files) for this database will expand until the disk is full. The recovery model for this database is set to "FULL" or "BULK_LOGGED" but according to MSDB backup history, the transaction log has never been backed up.	Info/Warning
520 Transaction log backup too old for "<database name>". Risk of disk full in the future: There is a risk that the database transaction log files (LDF files) for this database will expand until the disk is full. The recovery model for this database is set to "FULL" or "BULK_LOGGED" but according to MSDB backup history, the most recent transaction log is too old (older than <minutes> minutes).	Info/Warning

Make script include overview of most recent backups

If you want to view an overview of the most recent backups, for each backup type, for each database, do the following:

1. Delete comment start tag /* from the 6th last line.
2. Delete the comment end tag */ from the last line.
3. Press F5 or click Execute to run the script again.

If you see NULL in the most_recent_backup_finish_date column, it means that a backup for that database has never been made.

If you see more than one row of information for a single database, it means that multiple types of backups have been made for that database, and each row contains information about the date of the most recent backup of this type.

Databases contain indexes that can get fragmented. Because of that, we recommend that you Check if you need to defragment your indexes on a regular basis, and if your database indexes have become too fragmented, see How to defragment your indexes.

Alternatively, a database administrator (dba) can set up maintenance plans on the SQL server to automatically schedule the tasks mentioned in the previous.

If you are a database administrator (dba), and you want to use SQL Server Profiler to set up a Trace, you may find the following Column Filters useful:

• Duration with the condition greater than or equal to, for example, 2000ms will warn you if queries on your SQL server takes too long to execute.
• Reads with the condition greater than or equal to, for example, 65000 pages will warn you if queries on your SQL server have become ineffective.

If you identify issues that require changes to, for example, indexes or stored procedures, you need to contact Zylinc support to get assistance to make such changes. This is because the license agreement with Zylinc doesn't permit you to change indexes, stored procedures, etc. in databases provided by Zylinc.

We recommend that you monitor the default network port for the SQL Server service, which is typically port 1433/tcp.

The help topic Install a Zylinc solution from scratch describes how to create a new mixed mode security user, with the name ZyUser and db_owner database role memberships for the two Zylinc databases, named ZyDB and ZyStatDB.

The Zylinc solution uses ZyUser to access the two databases.

We recommend that you monitor that ZyUser can always access the two databases. Both databases contain a table with the name database_info that you can query. This query should always return at least one row.

You can set up two monitoring tasks that uses ZyUser to log in to the two databases ZyDB and ZyStatDB and executes the following query:

select * from database_info

As mentioned before, the query should return minimum one row.

Databases can be online and readable, but if the disk that contains the transaction log becomes corrupt or full, or if the database size exceeds the maximum allowed size (which on a SQL Server Express Edition can typically be as little as 10 GB), the database becomes read-only, and the Zylinc solution will no longer work. Again, we don’t want that!

That's why we recommend that you set up two monitoring tasks that check the state of the updateability database property for both ZyDB and ZyStatDB:

SELECT DATABASEPROPERTYEX('zydb', 'Updateability');

and

SELECT DATABASEPROPERTYEX('zystatdb', 'Updateability');

Each query should return a row that contains the value READ_WRITE.

For more information, refer to the documentation for Microsoft SQL Server and your monitoring tool.