public:module:microsoft_sql_database:requirements
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision | |||
|
public:module:microsoft_sql_database:requirements [2021/04/19 11:35] ronnie.chan |
public:module:microsoft_sql_database:requirements [2021/04/19 11:41] (current) ronnie.chan |
||
|---|---|---|---|
| Line 164: | Line 164: | ||
| === MS SQL Server Volumes === | === MS SQL Server Volumes === | ||
| - | ---- | + | |
| MS SQL Server volumes must use a file system which supports the use of VSS snapshot, for example NTFS. | MS SQL Server volumes must use a file system which supports the use of VSS snapshot, for example NTFS. | ||
| === Windows Services === | === Windows Services === | ||
| - | ---- | + | |
| Ensure that the following services have been enabled in the Windows Services menu. | Ensure that the following services have been enabled in the Windows Services menu. | ||
| Line 174: | Line 174: | ||
| <WRAP indent> | <WRAP indent> | ||
| - | 1.SQL Server VSS Writer | + | |
| + | **SQL Server VSS Writer** | ||
| {{public:module:microsoft_sql_database:cloudbacko_module_sql_002.png?800}} | {{public:module:microsoft_sql_database:cloudbacko_module_sql_002.png?800}} | ||
| - | + | **Volume Shadow Copy** | |
| - | 3. Volume Shadow Copy | + | |
| {{public:module:microsoft_sql_database:cloudbacko_module_sql_004.png?800}} | {{public:module:microsoft_sql_database:cloudbacko_module_sql_004.png?800}} | ||
| + | |||
| </WRAP> | </WRAP> | ||
| === MS SQL Recovery Model === | === 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]] | 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 194: | Line 195: | ||
| </WRAP> | </WRAP> | ||
| + | ===== ODBC Backup Mode ===== | ||
| - | CloudBacko Pro supports 2 backup modes when creating a backup set for MS SQL server, VSS mode and ODBC 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. |
| - | ===== 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> | <WRAP indent> | ||
| - | === CloudBacko Pro Installation === | + | === Temporary Directory Folder === |
| - | ---- | + | |
| - | Make sure the latest version of CloudBacko Pro has been installed on the MS SQL server. | + | |
| - | + | ||
| - | === SQL Add-On Module === | + | |
| - | ---- | + | |
| - | Make sure the Microsoft SQL Server backup module has been purchased for your CloudBacko Pro license. | + | |
| - | + | ||
| - | === 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 CloudBacko Pro is 2048MB. For MS SQL Server backup it is highly recommended to [[https://forum.cloudbacko.com/viewtopic.php?f=40&t=1628&p=7639&hilit=java+heap#p7639|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. | + | |
| - | + | ||
| - | + | ||
| - | + | ||
| - | ==== 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> | + | |
| - | === CloudBacko Pro Installation === | + | |
| - | ---- | + | |
| - | Make sure the latest version of CloudBacko Pro has been installed on the MS SQL server. | + | |
| - | === SQL Add-On Module === | ||
| - | ---- | ||
| - | Make sure the Microsoft SQL Server add-on module was purchased for your CloudBacko Pro license. | ||
| - | |||
| - | === Java Heap Size === | ||
| - | ---- | ||
| - | The default Java heap size setting on CloudBacko Pro is 2048MB. For MS SQL Server backup it is highly recommended to [[https://forum.cloudbacko.com/viewtopic.php?f=40&t=1628&p=7639&hilit=java+heap#p7639|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 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 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. | - 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. | ||
| Line 245: | Line 210: | ||
| - The SQL Windows service must have read and write permission to the temporary directory. | - The SQL Windows service must have read and write permission to the temporary directory. | ||
| - | === Windows Services === | + | === Maximum Worker Thread === |
| - | ---- | + | |
| - | 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. | + | |
| - | + | ||
| - | 1. SQL Server Services \\ {{public:module:microsoft_sql_database:cloudbacko_module_sql_006.png?800}} | + | |
| - | + | ||
| - | 2. Volume Shadow Copy \\ {{public:module:microsoft_sql_database:cloudbacko_module_sql_007.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:module:microsoft_sql_database:cloudbacko_module_sql_008.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. | 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]] | [[https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option]] | ||
| === MS SQL Recovery Model === | === MS SQL Recovery Model === | ||
| - | ---- | + | |
| ODBC backup mode supports transaction log backup for database with Full recovery model. | ODBC backup mode supports transaction log backup for database with Full recovery model. | ||
| Line 280: | Line 224: | ||
| 2. To perform a transaction log backup, please change the recovery model of corresponding databases from Simple to Full. | 2. 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]] | [[https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/view-or-change-the-recovery-model-of-a-database-sql-server]] | ||
| + | |||
| + | === ODBC Mode Authentication Methods === | ||
| + | |||
| + | ODBC backup mode supports two types authentication method: | ||
| + | |||
| + | == Trusted Authentication == | ||
| + | |||
| + | 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. | ||
| + | |||
| + | == MS SQL Authentication == | ||
| + | |||
| + | When using this method, the username and password are created and stored in the MS SQL Server. | ||
| + | |||
| + | 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. | ||
| + | |||
| + | <WRAP indent> | ||
| + | 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. | ||
| + | </WRAP> | ||
| + | |||
| + | 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. | ||
| </WRAP> | </WRAP> | ||
public/module/microsoft_sql_database/requirements.txt · Last modified: 2021/04/19 11:41 by ronnie.chan
Page Tools
