Differences

This shows you the differences between two versions of the page.

--- public:module:microsoft_sql_database:requirements [2019/09/12 15:16]
ronnie.chan
+++ public:module:microsoft_sql_database:requirements [2021/04/19 11:41] (current)
ronnie.chan
@@ Line 1: / Line 1: @@
 ====== Requirements ======
 You are strongly recommended to configure or check all the requirements below before you proceed with the MS SQL server backup and restoration.
-CloudBacko Pro supports 2 backup modes when creating a backup set for MS SQL server, VSS mode and ODBC mode.
+===== Hardware Requirement =====
-----
+Refer to the here for the list of hardware requirements: [[public:edition:cloudbacko_pro:hardware_requirement]]
+===== Software Requirement =====
+Refer to the [[public:edition:cloudbacko_pro:supported_platforms|list of compatible operating systems]] and [[public:edition:cloudbacko_pro:supported_applications|application versions]].
+===== CloudBacko Pro Installation =====
+Make sure the latest version of CloudBacko Pro is installed directly on the machine where the MSSQL Server database(s) are hosted.
-===== VSS Backup Mode =====
-The VSS-based backup utilizing the Microsoft SQL Server VSS Writer to obtain a consistent snapshot of the MS SQL databases, no spooling / staging of database file(s) is required during the backup process.
 <WRAP indent>
-=== Hardware Requirement ===
+Backup and restore of MS SQL Server database(s) running on a remote machine is not supported.
-Refer to the following article for the list of hardware requirements for AhsayOBM: FAQ: Ahsay Hardware Requirement List (HRL) for version 8.1 or above
+</WRAP>
-=== Software Requirement ===
+===== Java Heap Size =====
-Refer to the following article for the list of compatible operating systems and application versions: FAQ: Ahsay Software Compatibility List (SCL) for version 8.1 or above
-=== AhsayOBM Installation ===
+The default Java heap size setting on CloudBacko Pro is 2048MB. For MS SQL Server backup it is highly recommended to increase the Java heap size setting to be at least 4096MB to improve backup and restore performance. The actual heap size is dependent on the amount of free memory available on your MS SQL server. See: [[public:edition:cloudbacko_pro:faq:how_to_increase_java_heap_size]]
-Make sure the latest version of AhsayOBM has been installed on the MS SQL server.
-=== AhsayOBM Add-On Module Configuration ===
+===== MS SQL Server Registry =====
-Make sure the Microsoft SQL Server feature has been enabled as an add-on module in your AhsayOBM user account. Contact your backup service provider for more details.
-=== Backup Quota Requirement ===
+Make sure the MS SQL entry is present in the registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL".
-Make sure that your AhsayOBM user account has sufficient storage quota assigned to accommodate the storage of MS SQL Server backup set and retention policy.
+To access this path, type “regedit” in the command prompt to launch the Registry Editor.
-=== Continuous Backup Module ===
+{{public:module:microsoft_sql_database:cloudbacko_module_sql_005.png?800}}
-The continuous backup add-on module is required if you would like to enable the continuous backup feature.
+<WRAP info>
-=== Java Heap Size ===
+Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation.
-The default Java heap size setting on AhsayOBM is 2048MB. For MS SQL Server backup it is highly recommended to increase the Java heap size setting to be at least 4096MB to improve backup and restore performance. The actual heap size is dependent on amount of free memory available on your MS SQL server.
+</WRAP>
+===== SQL Server Services =====
+Ensure that the following SQL Server Services have been enabled in the Windows Services menu. Launch Services in Windows by clicking Start then typing “Services” in the search box. All MS SQL server related services should be started by default. If in case it is not, turn it on by right clicking the item then selecting Start.
+{{public:module:microsoft_sql_database:cloudbacko_module_sql_003.png?800}}
+===== Transport Layer Security(TLS) =====
+For MS SQL Server 2005, 2008, 2012, and 2014 VSS and ODBC backup modes, TLS version 1.0 must be enabled as only TLS version 1.0 is supported.
+To check if TLS 1.0 is enabled on the MS SQL machine, launch the registry editor and locate the following path:
+“HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client”
+The value of registry key should be “1” to indicate that TLS 1.0 is enabled.
+| {{public:module:microsoft_sql_database:image1019.png?800}} |
+Meanwhile, for MS SQL Server 2016, 2017 and 2019 VSS and ODBC backup modes, TLS version 1.2 must be enabled as only TLS version 1.2 is supported.
+To check if TLS 1.2 is enabled on the MS SQL machine, launch the registry editor and locate the following path:
+“HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client”
+The value of registry key should be “1” to indicate that TLS 1.2 is enabled.
+| {{public:module:microsoft_sql_database:image1023.png?800}} |
+===== Upgrade VMware Tools Requirement =====
+To avoid unexpected java crash, if the Windows machine is a guest VM hosted on a VMware Host then it is highly recommended that the VMware tools version installed on the guest VM must be 10.0.5 or above. Below is the warning message that will be displayed if the version of the VMware Tools is less than 10.0.5.
+| {{public:module:microsoft_sql_database:image1025.png?800}} |
+===== VSS Backup Mode =====
+The VSS-based backup utilizing the Microsoft SQL Server VSS Writer to obtain a consistent snapshot of the MS SQL databases, no spooling / staging of database file(s) is required during the backup process.
+<WRAP indent>
 === User Account Privileges ===
 Make sure the operating system account that performs the backup and restore has sufficient permission to access both SQL server and VSS.
 === Temporary Directory Folder ===
-  - The temporary directory folder is used by AhsayOBM for storing backup set index files and incremental/differential delta files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder to be set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows or database.
+  - The temporary directory folder is used by CloudBacko Pro for storing backup set index files and incremental/differential delta files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder to be set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows or database.
   - It is recommended that the temporary directory folder should have at least free disk space of 50% of the total database size because the default Delta ratio is 50%. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
   - The SQL Windows service must have read and write permission to the temporary directory.
 === SQL Server VSS Writer ===
 Make sure the SqlServerWriter has been installed and running on the SQL server, and the writer state is Stable. This can be verified by running the “vssadmin list writers” command in the Windows Command Prompt.
 If you do not find the SqlServerWriter in the result, make sure the SQL Server VSS Writer has been started by following the instructions in Windows Services section below.
-Example:
+**Example:**
 <code>
 C:\Users\Administrator>vssadmin list writers
@@ Line 118: / Line 164: @@
 === MS SQL Server Volumes ===
 MS SQL Server volumes must use a file system which supports the use of VSS snapshot, for example NTFS.
 === Windows Services ===
 Ensure that the following services have been enabled in the Windows Services menu.
@@ Line 126: / Line 174: @@
 <WRAP indent>
-.SQL Server VSS Writer
-{{public:ahsay_wiki_module_microsoft_sql_requirement_2.png?800}}
+**SQL Server VSS Writer**
-. SQL Server Services
-{{public:ahsay_wiki_module_microsoft_sql_requirement_3.png?800}}
+{{public:module:microsoft_sql_database:cloudbacko_module_sql_002.png?800}}
-. Volume Shadow Copy
-{{public:ahsay_wiki_module_microsoft_sql_requirement_4.png?800}}
+**Volume Shadow Copy**
-</WRAP>
-=== MS SQL Server Registry ===
+{{public:module:microsoft_sql_database:cloudbacko_module_sql_004.png?800}}
-Make sure the MS SQL entry is present in the registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL".
-To access this path, type “regedit” in the command prompt to launch the Registry Editor.
-{{public:ahsay_wiki_module_microsoft_sql_requirement_5.png?800}}
-<WRAP info>
-Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation.
 </WRAP>
 === MS SQL Recovery Model ===
 VSS backup mode does not support backup of transaction log files, but for databases configured in either Full or Bulk-logging recovery model, this may eventually result in transaction logs filling up the available disk space on the volume of the MS SQL Server. Refer to: [[https://technet.microsoft.com/en-us/library/cc966520.aspx]]
@@ Line 158: / Line 195: @@
 </WRAP>
-==== ODBC Backup Mode ====
+===== ODBC Backup Mode =====
 By using the ODBC mode for MS SQL backup, databases files are spooled to a temporary directory before being uploaded to the backup destination.
 <WRAP indent>
-=== Hardware Requirement ===
-Refer to the following article for the list of hardware requirements for AhsayOBM: FAQ: Ahsay Hardware Requirement List (HRL) for version 8.1 or above
-=== Software Requirement ===
-Refer to the following article for the list of compatible operating systems and application versions: FAQ: Ahsay Software Compatibility List (SCL) for version 8.1 or above
-=== AhsayOBM Installation ===
-Make sure the latest version of AhsayOBM has been installed on the MS SQL server.
-=== AhsayOBM Add-On Module Configuration ===
-Make sure the Microsoft SQL Server feature has been enabled as an add-on module in your AhsayOBM user account. Contact your backup service provider for more details.
-=== Backup Quota Requirement ===
-Make sure that your AhsayOBM user account has sufficient storage quota assigned to accommodate the storage of MS SQL Server backup set and retention policy.
-=== Continuous Backup Module ===
-The continuous backup add-on module is required if you would like to enable the continuous backup feature.
-=== Java Heap Size ===
-The default Java heap size setting on AhsayOBM is 2048MB. For MS SQL Server backup it is highly recommended to increase the Java heap size setting to be at least 4096MB to improve backup and restore performance. The actual heap size is dependent on amount of free memory available on your MS SQL server.
 === Temporary Directory Folder ===
-  - The temporary directory folder is used by AhsayOBM for storing the database files, incremental/differential delta files and backup set index files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive.
+  - The temporary directory folder is used by CloudBacko Pro for storing the database files, incremental/differential delta files and backup set index files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive.
   - The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows or database. If the temporary directory folder is located on a network drive, make sure the login account has sufficient permission to access the network resources.
   - Please refer to the following URL for more details: \\ [[https://support.microsoft.com/en-us/help/2926557/sql-server-vdi-backup-and-restore-operations-require-sysadmin-privileg]] \\
@@ Line 190: / Line 209: @@
   - It is recommended that the temporary directory folder should have at least free disk space of 150% of the total database size. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
   - The SQL Windows service must have read and write permission to the temporary directory.
-=== Windows Services ===
-Ensure that the following services have been enabled in the Windows Services menu.
-Launch Services in Windows by clicking Start then typing “Services” in the search box. All MS SQL server related services should be started by default, in case if it is not, turn it on by right clicking the item then selecting Start.
-. SQL Server Services \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_6.png?800}}
-. Volume Shadow Copy \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_7.png?800}}
-=== MS SQL Server Registry ===
-Make sure the MS SQL entry is present in the registry key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL".
-To access this path, type “regedit” in the command prompt to launch the Registry Editor.
-{{public:ahsay_wiki_module_microsoft_sql_requirement_8.png?800}}
-<WRAP info>
-Note: Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation.
-</WRAP>
 === Maximum Worker Thread ===
 For SQL instance with large number of database (more than 500 databases), consider to increase the “Maximum Worker Thread” setting. Refer to the article below for further details.
 [[https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option]]
 === MS SQL Recovery Model ===
 ODBC backup mode supports transaction log backup for database with Full recovery model.
@@ Line 223: / Line 224: @@
 . To perform a transaction log backup, please change the recovery model of corresponding databases from Simple to Full.
 [[https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/view-or-change-the-recovery-model-of-a-database-sql-server]]
-</WRAP>
-----
+=== ODBC Mode Authentication Methods ===
-===== Considerations for System Databases Backup and Restore =====
+ODBC backup mode supports two types authentication method:
-Refer to the following tables for considerations for backup and restoration of system databases.
-==== Backup ====
+== Trusted Authentication ==
-SQL server maintains a set of system level database which are essential for the operation of the server instance.
-Several of the system databases must be backed up after every significant update, they include:
-  - **master**
-  - **model**
-  - **msdb**
-  - **distribution** (for SQL database with replication enabled only)
-This table summarizes all of the system databases.
+This is the default authentication method in the MS SQL Server. When using this method, MS SQL Server uses the Windows login account to authenticate the login to the MS SQL Server.
-^ System      ^ Description       ^ Backup          ^ Suggestion          ^
+== MS SQL Authentication ==
-^ master    | The database that records all of the system level information of a SQL server system.     | Yes        | To back up any database, the instance of SQL server must be running. \\ \\ Startup of an instance of SQL server requires that the master database is accessible and at least partly usable. \\ \\ Back up the master database as often as necessary to protect the data sufficiently for your business needs.  \\ \\ Microsoft recommends a regular backup schedule, which you can supplement with manual backup after any substantial update.  |
-^ model    | The template for all databases that are created on the instance of SQL server.     | Yes        | Backup the model database only when necessary, for example, after customizing its database options.  \\ \\ Microsoft recommends that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.        |
-^ msdb    | The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and for recording operators. It also contains history tables (e.g. backup / restore history table).      | Yes        | Back up the msdb whenever it is updated.        |
-^ tempdb    | A workspace for holding temporary or intermediate result sets. This database is recreated every time an instance of SQL server is started.      | No        | The tempdb system database cannot be backed up.        |
-^ distribution    | The distribution database exists only if the server is configured as a replication distributor.  \\ \\ It stores metadata and history data for all types of replication, and transactions for transactional replication.      | Yes        | Replicated databases and their associated system databases should be backed up regularly.        |
-==== Restore ====
+When using this method, the username and password are created and stored in the MS SQL Server.
-^ Heading 1      ^ Heading 2       ^
-^ master    | To restore any database, the instance of SQL server must be running. Startup of an instance of SQL server requires that the master database is accessible and at least partly usable.  \\ \\ Restore or rebuild the master database completely if master becomes unusable.     |
-^ model    | Restore the model database if: \\  * The master database has been rebuilt. \\  * The model database has been damaged, for example due to media failure. \\  * The model database has been modified, in this case, it is necessary to restore model from a backup when you rebuild master, because the Rebuild Master utility deletes and recreates model.     |
-^ msdb    | Restore the msdb database if the master database has been rebuilt.     |
-^ distribution     | For restore strategies of distribution database, please refer to the following online document from Microsoft for more details: [[http://msdn.microsoft.com/enus/library/ms152560.aspx]]    |
-----
+For details on how to verify if the login credentials you intend to use to authenticate the MS SQL Server backup job on AhsayOBM has the correct permissions, and to determine if the drive for temporary folder has enough disk space to accommodate the spooling of the database(s), please refer to Appendix E.
-===== Best Practice and Recommendation =====
+<WRAP indent>
-The following are some best practice and recommendation we strongly recommend you to follow before you start any MS SQL Server backup and restore.
+It is recommended to use the Trusted Authentication method wherever possible as this type of method is tightly integrated with Windows which has an integrated security. MS SQL Server trusts the credentials provided by Windows as Windows authentication uses a series of encrypted messages to authenticate users in the MS SQL Server.
-  - For VSS backup mode, it is suggested to set the backup schedule to a time when system activity is low to achieve the best possible performance. \\ \\
+</WRAP>
-  - It is recommended to use ODBC backup mode for backup of database with a high volume of transaction, since such setup may require frequent backups. Transaction log backup (which is only supported by ODBC backup mode) can be performed periodically, and is less resource intensive than VSS based backup. \\ \\
-  - For maximum data protection and restore options, it is recommended to configure:
+However, when MS SQL Server logins are used, MS SQL Server login names and encrypted passwords are passed across the network, which makes them less secure.
-    - At least one offsite or cloud destination
+</WRAP>
-    - At least one local destination for fast recovery \\ \\
-  - Perform test restores periodically to ensure your backup is set up and performed properly. Performing recovery test can also help identify potential issues or gaps in your recovery plan. It is important that you do not try to make the test easier, as the objective of a successful test is not to demonstrate that everything is flawless. There might be flaws identified in the plan throughout the test and it is important to identify those flaws. \\ \\
-  - The Restore Raw File option is for advanced MS SQL Server administrator and should only be used if you have in-depth knowledge and understanding of your MS SQL Server, otherwise, it is not recommended to use this option as there are additional MS SQL techniques required to perform the manual restore.

User Tools

Site Tools

Differences

Page Tools