There must be regular database backup routines in place. This should also be documented in the design document.
There must be regular database maintenance routines in place. This should also be documented in the design document.
Considerations for clustering (high availability) of the Database Server should be made and documented.
In general, Micro Focus does not recommend virtualizing the ZENworks database server. However, it is possible to run Microsoft SQL Server on a virtual host. See the following documentation for more information:
Place data and log files on separate drives for the database [zenworks_database] on a server [server_name].
Check the database integrity at least every 14 days for the database [zenworks_database] on a server [server_name].
Keep the TempDB database and log files in a separate drive.
Keep backups in a separate drive. Network shared drive can also be used to take daily backup.
Ensure that you have the skills in-house, or readily available (contractor, consultant, or partner) to manage and maintain the Microsoft SQL Server, based on the best practices that Microsoft outlines for regular database management.
Microsoft also offers the SQL Server Best Practices Analyzer Tool. This tool addresses a wide variety of best practices as outlined by Microsoft. This tool can be found on the Microsoft website.
SQL Server supports the following types of storage for data files:
Local Disk
Shared Storage
SMB/CIFS File Share
SQL Server failover cluster installation supports Local Disk only for installing the tempdb files. Ensure that the path specified for the tempdb data and log files is valid on all the cluster nodes. During failover, if the tempdb directories are not available on the failover target node, the SQL Server resource will fail to come online.
SQL Server supports Direct Attached Storage (DAS), Storage Area Network (SAN), and Network Attached Storage (NAS) storage architectures.
Direct Attached Storage (DAS): DAS is a digital storage system that is directly attached to a server or workstation, without a storage network in between. DAS physical disk types include Serial Attached SCSI (SAS) and Serial Attached ATA (SATA).
Network Attached Storage (NAS): A NAS unit is a self-contained computer that is connected to a network.
Storage Area Network (SAN): SAN is an architecture to attach remote computer storage devices (such as disk arrays and tape libraries) to servers in such a way that the devices appear as locally attached to the operating system (for example, block storage).
In general, Micro Focus recommends a SAN when the benefits of shared storage are important to your organization. The benefits of shared storage include the following:
Easier to reallocate disk storage between servers
Can serve multiple servers
No limitations on the number of disks that can be accessed
For more information on storage architecture, see: SQL Server and Network Attached Storage.
The disk types that you use in the system can affect reliability and performance. When everything else is equal, larger drives increase the mean seek time. SQL Server supports the following types of drives:
Small Computer System Interface (SCSI)
Serial Advanced Technology Attachment (SATA)
Serial-attached SCSI (SAS)
Fibre Channel (FC)
Integrated Device Electronics (IDE)
Solid State Drive (SSD) or Flash Disk
For optimal performance, store the different files in different drives, preferably on different I/O controllers.
For Example:
For OS use C:\
For SQL Binaries use D:\
For TempDB (1 file per processor, equi-sized) use E:\
F:\ Data
G:\ Log
H:\ Backup
Micro Focus recommends using SAN Storage or Solid State Drives for ZENworks databases to obtain optimal performance.
If you are using a SAN in conjunction with MS SQL, consider the following best practices:
Consider the bandwidth of the data channel that depends on the I/O demands for the SQL Server.
Consider the way the SAN abstracts the physical devices it presents to the system to take maximum advantage of parallelism.
Choose an appropriate RAID level for the SAN. For ZENworks database, RAID 10 is recommended. When you configure a RAID array, ensure that you align the file system to the offset that is supplied by the vendor.
For more information on RAID, see:
MS TechNet Article on SQL RAID
Hard Drive Configurations for SQL Server
For more information on storage in general, see:
Throughput of any database server is greatly influenced by the resources allocated for the server. In most cases, it is best to keep them to the default values for better results.
Set the maximum worker threads to 0.
This ensures that the database server manages the needed threads that give the best throughput.
Set the max server memory to the default value (2147483647 MB).
Update the database server with the latest service packs.
Other factors that might influence the memory that is required, include the following:
The use of SQL Server mirroring
The frequent use of files larger than 15 MB
Read Committed Snapshot allows transactions to share locks to databases so that you can allow two distinct processes to update the same table at the same time. If this setting is not enabled, large amounts of blocking can occur that decreases ZENworks performance.
To enable Read Committed Snapshot, run the following command from an SQL editor:
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON; GO ALTER DATABASE <database name> SET MULTI_USER; GO
To verify that the Read Committed Snapshot has been successfully enabled, run the following:
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<database name>'
For more detailed information on snapshot isolation and transaction isolation, see the following links:
Because ZENworks uses a shared database for all Primaries, it is important that the database server be available at all times. The MS SQL Server provides several options to create high availability for a server or database:
For additional information on the MS SQL Server high availability, see the SQL Server High Availability guide.
This provides local high availability through redundancy at the server-instance level.
Benefits:
Protection at the instance level through redundancy.
Automatic failover in the event of a failure (hardware failures, operating system failures, and application or service failures).
Support for a broad array of storage solutions, including WSFC cluster disks (iSCSI, Fiber Channel, and so on) and server message block (SMB) file shares.
Disaster recovery solution using a multi-subnet FCI or running an FCI-hosted database inside an AlwaysOn availability group. With the new multi-subnet support in Microsoft SQL Server 2012, a multi-subnet FCI no longer requires a virtual LAN, increasing the manageability and security of a multi-subnet FCI.
Zero re-configuration of applications and clients during failovers.
Flexible failover policy for granular Trigger events for automatic failovers.
Reliable failovers through periodic and detailed health detection using dedicated and persisted connections.
Configurability and predictability in failover time through indirect background checkpoints.
Throttled resource usage during failovers.
For more information on AlwaysOn Failover Cluster Instances, see AlwaysOn Failover Cluster Instances.
Introduced in SQL Server 2012, AlwaysOn Availability Groups maximize the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases.
For more information on AlwaysOn Availability Groups, see AlwaysOn Availability Groups.
SQL Server Log Shipping allows you to automatically send transaction log backups from a primary database on a Primary Server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases, individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations, and optionally raises alerts if these operations fail to occur as scheduled.
Benefits:
Provides a disaster-recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL Server.
Supports limited read-only access to secondary databases (during the interval between restore jobs).
Allows a user-specified delay between when the Primary Server backs up the log of the primary database and when the secondary servers must restore (apply) the log backup.
For more information on Log Shipping, see Log Shipping.
Database Mirroring is a solution for increasing the availability of an SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.
Benefits:
Increases the availability of a database.
Increases data protection.
Improves the availability of the production database during upgrades.
This feature will be removed in a future version of Microsoft SQL Server. Use Always-On Availability Groups instead.
For More information on mirroring, see Database Mirroring.
The MS SQL Best Practices Page provides additional best practices for SQL Server design, planning, and maintenance.