This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
public:microsoft_sql_database [2018/05/08 17:56] edward.chan |
public:microsoft_sql_database [2019/08/19 14:16] ronnie.chan |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ======MS SQL Database===== | + | ====== Microsoft SQL Database Backup Set ===== |
- | [[public:5059_issue:no_database_file_found_ms_sql_database_backup_for_database_file_name_with_extension_in_upper_case|ISSUE: No database file found (MS SQL database backup) for database file name with extension in upper case]] | + | AhsayOBM allows you to back up databases in your Microsoft SQL Server with the MS SQL Server Backup Module, which provides you with a set of tools to protect your MS SQL Server, whether in VSS backup mode or ODBC backup mode. |
- | [[public:5067_issue:incorrect_error_message_another_backup_job_is_still_running_is_displayed_ms_sql_database_backup|ISSUE: Incorrect error message 'Another backup job is still running' is displayed (MS SQL database backup)]] | + | {{public:ahsay_wiki_microsoft_sql_database_backup_set.png?640}} |
- | [[public:5069_issue:the_sql_server_vss_writer_service_does_not_exist_as_an_installed_service_cannot_create_ms_sql_database_backup_set_on_non-english_windows|ISSUE: The SQL Server VSS Writer service does not exist as an installed service (cannot create MS SQL database backup set on non-English Windows)]] | + | ===== System Architecture ===== |
+ | Below is the system architecture diagram illustrating the major elements involved in the backup process among the MS SQL server, AhsayOBM and AhsayCBS. | ||
- | [[public:5081_issue:ms_sql_database_backup_job_cannot_run_to_completion_for_backup_set_with_backup_schedule_disabled|ISSUE: MS SQL database backup job cannot run to completion (for backup set with backup schedule disabled)]] | + | ===== Requirements ===== |
+ | You are strongly recommended to configure or check all the requirements below before you proceed with the MS SQL server backup and restoration. AhsayOBM supports 2 backup modes when creating a backup set for MS SQL server, VSS mode and ODBC mode. | ||
- | [[public:5151_issue:no_database_file_found_ms_sql_database_backup_for_database_file_name_with_unicode|ISSUE: No database file found (MS SQL database backup) for database file name with unicode]] | + | ==== 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 === | ||
+ | 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 | ||
- | [[public:5201_issue:no_database_file_found_for_db_name_error_on_mssql_server_backups|ISSUE: "No database file found for "%DB_Name%" error on MSSQL Server backups]] | + | === 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 | ||
- | [[public:5202_db_not_shown_in_backup_source_if_hostname_of_server_is_longer_than_15_char|ISSUE: MSSQL Server databases are not shown in AhsayOBM backup source if the hostname of the database server is longer than 15 characters]] | + | === AhsayOBM Installation === |
+ | Make sure the latest version of AhsayOBM has been installed on the MS SQL server. | ||
- | [[public:5203_issue:ahsayobm_crashes_when_creating_a_mssql_server_database_backup_set_on_a_windows_2008_32bit_machine|ISSUE: AhsayOBM crashes when creating a MSSQL Server database backup set on a Windows 2008 32bit machine]] | + | === 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. | ||
- | [[public:5204_issue:mssql_server_database_backup_fails_with_cannot_start_shadow_copy_reason_null_error|ISSUE: MSSQL Server database backup fails with "Cannot start shadow copy, reason =null" error]] | + | === 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. | ||
+ | === 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. | ||
+ | - 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: | ||
+ | <code> | ||
+ | C:\Users\Administrator>vssadmin list writers | ||
+ | vssadmin 1.1 - Volume Shadow Copy Service administrative command-line tool | ||
+ | (C) Copyright 2001-2013 Microsoft Corp. | ||
+ | |||
+ | Writer name: 'Task Scheduler Writer' | ||
+ | Writer Id: {d61d61c8-d73a-4eee-8cdd-f6f9786b7124} | ||
+ | Writer Instance Id: {1bddd48e-5052-49db-9b07-b96f96727e6b} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | |||
+ | Writer name: 'VSS Metadata Store Writer' | ||
+ | Writer Id: {75dfb225-e2e4-4d39-9ac9-ffaff65ddf06} | ||
+ | Writer Instance Id: {088e7a7d-09a8-4cc6-a609-ad90e75ddc93} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | |||
+ | Writer name: 'Performance Counters Writer' | ||
+ | Writer Id: {0bada1de-01a9-4625-8278-69e735f39dd2} | ||
+ | Writer Instance Id: {f0086dda-9efc-47c5-8eb6-a944c3d09381} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | |||
+ | <html><strong> | ||
+ | Writer name: 'SqlServerWriter' | ||
+ | Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a} | ||
+ | Writer Instance Id: {3de4f842-4d57-4198-9949-3b3f8c2629dc} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | </strong></html> | ||
+ | |||
+ | Writer name: 'System Writer' | ||
+ | Writer Id: {e8132975-6f93-4464-a53e-1050253ae220} | ||
+ | Writer Instance Id: {32d2fccc-624f-4baa-beb3-17b27fcae9ee} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | |||
+ | Writer name: 'ASR Writer' | ||
+ | Writer Id: {be000cbe-11fe-4426-9c58-531aa6355fc4} | ||
+ | Writer Instance Id: {e8580fb0-b51f-40ab-91bf-4eff5107c4d1} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | |||
+ | Writer name: 'WMI Writer' | ||
+ | Writer Id: {a6ad56c2-b509-4e6c-bb19-49d8f43532f0} | ||
+ | Writer Instance Id: {de1b6322-1d96-4f85-adbf-05cb517322ea} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | |||
+ | Writer name: 'BITS Writer' | ||
+ | Writer Id: {4969d978-be47-48b0-b100-f328f07ac1e0} | ||
+ | Writer Instance Id: {a623b49f-a3d4-42d2-af9a-4e924fb31262} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | |||
+ | Writer name: 'Registry Writer' | ||
+ | Writer Id: {afbab4a2-367d-4d15-a586-71dbb18f8485} | ||
+ | Writer Instance Id: {cc6b42f1-ebd0-429f-b3d3-e860905d40d3} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | |||
+ | Writer name: 'Shadow Copy Optimization Writer' | ||
+ | Writer Id: {4dc3bdd4-ab48-4d07-adb0-3bee2926fd7f} | ||
+ | Writer Instance Id: {957ff981-d54f-4a1f-8798-bd9bd76396bd} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | |||
+ | Writer name: 'COM+ REGDB Writer' | ||
+ | Writer Id: {542da469-d3e1-473c-9f4f-7847f01fc64f} | ||
+ | Writer Instance Id: {801fea63-6bfc-406d-9a40-4ad5af484773} | ||
+ | State: [1] Stable | ||
+ | Last error: No error | ||
+ | </code> | ||
+ | |||
+ | |||
+ | </WRAP> | ||
+ | |||
+ | ===== Documentation ===== | ||
+ | |||
+ | * [[https://www.ahsay.com/download/download_document_v8_obm-user-guide-sql.jsp|Microsoft SQL Server Database Backup and Restore Guide]] | ||
+ | |||
+ | ===== Issues ===== | ||
+ | |||
+ | * [[public:5059_issue:no_database_file_found_ms_sql_database_backup_for_database_file_name_with_extension_in_upper_case|No database file found (MS SQL database backup) for database file name with extension in upper case]] | ||
+ | * [[public:5067_issue:incorrect_error_message_another_backup_job_is_still_running_is_displayed_ms_sql_database_backup|Incorrect error message 'Another backup job is still running' is displayed (MS SQL database backup)]] | ||
+ | * [[public:5069_issue:the_sql_server_vss_writer_service_does_not_exist_as_an_installed_service_cannot_create_ms_sql_database_backup_set_on_non-english_windows|The SQL Server VSS Writer service does not exist as an installed service (cannot create MS SQL database backup set on non-English Windows)]] | ||
+ | * [[public:5081_issue:ms_sql_database_backup_job_cannot_run_to_completion_for_backup_set_with_backup_schedule_disabled|MS SQL database backup job cannot run to completion (for backup set with backup schedule disabled)]] | ||
+ | * [[public:5151_issue:no_database_file_found_ms_sql_database_backup_for_database_file_name_with_unicode|No database file found (MS SQL database backup) for database file name with unicode]] | ||
+ | * [[public:5201_issue:no_database_file_found_for_db_name_error_on_mssql_server_backups|"No database file found for "%DB_Name%" error on MSSQL Server backups]] | ||
+ | * [[public:5202_db_not_shown_in_backup_source_if_hostname_of_server_is_longer_than_15_char|MSSQL Server databases are not shown in AhsayOBM backup source if the hostname of the database server is longer than 15 characters]] | ||
+ | * [[public:5203_issue:ahsayobm_crashes_when_creating_a_mssql_server_database_backup_set_on_a_windows_2008_32bit_machine|AhsayOBM crashes when creating a MSSQL Server database backup set on a Windows 2008 32bit machine]] | ||
+ | * [[public:5204_issue:mssql_server_database_backup_fails_with_cannot_start_shadow_copy_reason_null_error|MSSQL Server database backup fails with "Cannot start shadow copy, reason =null" error]] |