User Tools

Site Tools


public:microsoft_sql_database

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
public:microsoft_sql_database [2019/08/19 15:06]
ronnie.chan
public:microsoft_sql_database [2019/08/26 11:12] (current)
ronnie.chan
Line 4: Line 4:
  
 {{public:​ahsay_wiki_microsoft_sql_database_backup_set.png?​640}} {{public:​ahsay_wiki_microsoft_sql_database_backup_set.png?​640}}
 +
 +----
  
 ===== System Architecture ===== ===== 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. Below is the system architecture diagram illustrating the major elements involved in the backup process among the MS SQL server, AhsayOBM and AhsayCBS.
 +
 +{{public:​content_components_mssql.png?​800}}
 +
 +----
 +
 +===== Backup Modes =====
 +You can choose from one of the two backup modes when creating a backup set for MS SQL server. The information below provides you with more details on each backup mode.
 +<WRAP info>
 +For MS SQL server backup sets which are upgraded from v6, the default backup mode will be ODBC.
 +</​WRAP>​
 +
 +==== VSS Mode ====
 +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.
 +
 +{{public:​ahsay_wiki_module_microsoft_sql_requirement_9.png?​640}}
 +
 +(Diagram from Microsoft)
 +
 +<WRAP indent>
 +
 +=== Process ===
 +
 +{{public:​ahsay_wiki_module_microsoft_sql_requirement_11.png?​640}}
 +
 +=== Temporary Folder Requirement ===
 +<WRAP indent>
 +== Location for temporary 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 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 and or database.
 +
 +== Temporary folder capacity ==
 +With VSS-based backup, the disk space of the temporary folder required for storing the VSS image is significantly smaller than using the ODBC spooling backup method. As the extra space is not required to hold the full database.
 +
 +It is recommended that the temporary directory should have at least free disk space of 50% of the total database size.  The rationale behind this recommended free disk space is the default in-file delta ratio settings is 50%, therefore AhsayOBM could generate incremental or differential delta file(s) of up to 50% 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.
 +</​WRAP>​
 +
 +=== Pros ===
 +<WRAP indent>
 +== Fast and minimal interruption ==
 +The database snapshot capture process is fast and can take place on a running server, as you may continue to work when the snapshot capturing is taking place, there may be another process that holds your input in some memory section until the snapshot capture is completed. That said, the whole snapshot capture is fast, so there is no need for you to stop working and it causes minimal interruption to your business operation.
 +
 +== Significantly lesser disk burden ==
 +VSS Snapshot typically requires much less additional disk space than clones which is the traditional backup method by spooling database into the temporary folder. Oftentimes, the capacity of the database to back up is huge and therefore the temporary folder would overload with the equal or even larger disk space if traditional backup method is used. By utilizing the VSS technology, it helps your system greatly reduce disk capacity burden and promote optimized performance.
 +</​WRAP>​
 +
 +=== Cons ===
 +<WRAP indent>
 +== No Transaction Log Backup ==
 +MS SQL does not support transaction log backup when VSS is used, therefore, transaction log backup will have to be done manually.
 +
 +== Workaround is time consuming ==
 +In order to truncate the transaction logs, you have to either change the Recovery model to Simple or perform a manual log truncation, which could be time consuming.
 +</​WRAP>​
 +
 +=== Transaction Log Handling ===
 +VSS based backup no longer requires backup of the transaction log files, however 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. [[https://​technet.microsoft.com/​en-us/​library/​cc966520.aspx]]
 +
 +To prevent this from occurring, it is recommended to change the recovery model of database selected for backup to simple recovery model.  ​
 +Refer to the following steps for details:
 +
 +  - In SQL Server Management Studio, expand Databases, select a user database, or expand System Databases and select a system database. \\ \\
 +  - Right-click the corresponding database, then click Properties to open the Database Properties dialog box. \\ \\
 +  - In the Select a page pane, click Options. \\ \\
 +  - The current recovery model is displayed in the Recovery model list box.  Modify the recovery model by selecting Simple from the model list.
 +
 +<WRAP important>​
 +Only modify the recovery model of a live database during low activities hour. It is also recommended to perform a full backup before changing the recovery model.
 +</​WRAP>​
 +
 +For MS SQL Server setups where you cannot modify the recovery model of the database, please refer to [[#​truncating_transaction_log|here]] for details on how to truncate transaction log (e.g. perform a transaction log backup manually).
 +</​WRAP>​
 +
 +==== ODBC Mode ====
 +By using the ODBC mode for MS SQL backup, database files are spooled to a temporary directory before being uploaded to the backup destination.
 +
 +{{public:​ahsay_wiki_module_microsoft_sql_requirement_10.png?​640}}
 +
 +<WRAP indent>
 +
 +=== Process ===
 +
 +{{public:​ahsay_wiki_module_microsoft_sql_requirement_12.png?​640}}
 +
 +=== Temporary Folder Requirement ===
 +
 +<WRAP indent>
 +== Location for temporary 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 folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows and or database.
 +
 +== Temporary folder capacity ==
 +ODBC backup requires a significantly larger disk space of temporary folder as it need to store the database files spooled during the backup process.
 +It is recommended that the temporary directory have disk space of at least 150% of the total database size. For each database backup, AhsayOBM will spool the database files to the temporary directory before they are uploaded to the backup destination. Also, additional space is required for in-file delta generation the default in-file delta ratio settings is 50%, therefore AhsayOBM could generate incremental or differential delta file(s) of up to 50% of the total database size. The actual disk space required depends on various factors, including the size of the database, number of backup destinations,​ backup frequency, in-file delta settings etc.
 +</​WRAP>​
 +
 +=== Pros ===
 +<WRAP indent>
 +== Support Automated Transaction Logs Backup ==
 +Schedule backup of transaction log can be configured so that the transaction logs can be backed up periodically and the transaction logs are truncated automatically after each backup job.
 +
 +== Support Point in Time Recovery ==
 +The ability to restore to a point in time for all of your transaction log backups.
 +
 +== Support Backup of High Transaction Databases ==
 +For databases which supports a high number of transaction which may require frequent backups. ​ Transaction log backups at regular intervals are more suitable and less resource intensive than VSS based backups, i.e. transaction log backup every 60 minutes, 30 minutes, 15 minutes etc depending on the database transaction volume. ​
 +</​WRAP>​
 +
 +=== Pros ===
 +<WRAP indent>
 +== Large disk space required ==
 +Since the database files will be spooled to a temporary folder before uploading to backup destination,​ investment on hard disk could be high if your MS SQL database size is large. ​
 +
 +== Slower backup process ==
 +By utilizing the conventional spooling method, it could take a long time to back up the database and the speed is subject to various factors, including database size, network transfer speed, backup frequency, etc. 
 +</​WRAP>​
 +</​WRAP>​
  
 ---- ----
Line 135: Line 251:
 1.SQL Server VSS Writer 1.SQL Server VSS Writer
  
-{{public:ahsay_wiki_module_microsoft_sql_requirement_1.png?800}}+{{public:ahsay_wiki_module_microsoft_sql_requirement_2.png?800}}
    
 2. SQL Server Services 2. SQL Server Services
  
-{{public:ahsay_wiki_module_microsoft_sql_requirement_2.png?800}}+{{public:ahsay_wiki_module_microsoft_sql_requirement_3.png?800}}
    
 3. Volume Shadow Copy 3. Volume Shadow Copy
  
-{{public:ahsay_wiki_module_microsoft_sql_requirement_3.png?800}}+{{public:ahsay_wiki_module_microsoft_sql_requirement_4.png?800}}
 </​WRAP>​ </​WRAP>​
  
Line 150: Line 266:
 To access this path, type “regedit” in the command prompt to launch the Registry Editor. ​ To access this path, type “regedit” in the command prompt to launch the Registry Editor. ​
  
-{{public:ahsay_wiki_module_microsoft_sql_requirement_4.png?800}}+{{public:ahsay_wiki_module_microsoft_sql_requirement_5.png?800}}
  
-<code> +<WRAP info>
-NOTE+
 Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation. ​ Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation. ​
-</code>+</WRAP>
  
 === MS SQL Recovery Model === === MS SQL Recovery Model ===
Line 162: Line 277:
 To prevent this from occurring, you can modify the recovery model of database selected for backup to Simple. To prevent this from occurring, you can modify the recovery model of database selected for backup to Simple.
  
-Alternatively,​ to truncate the transaction log files, you can perform a transaction log backup manually, or create an additional MS SQL database backup set in ODBC backup mode to perform a transaction log backup.+Alternatively,​ to [[#​truncating_transaction_log|truncate the transaction log files]], you can perform a transaction log backup manually, or create an additional MS SQL database backup set in ODBC backup mode to perform a transaction log backup.
  
-<WRAP indent> 
-== Truncating Transaction Log == 
-The instructions below only apply for database with full recovery model. ​ 
- 
-Since AhsayOBM v8 utilizes VSS-based backup, which does not support log backup ([[https://​technet.microsoft.com/​en-us/​library/​cc966520.aspx]]),​ transaction log of database in full / bulk-logging recovery model may eventually fill up all disk space available on the volume. 
- 
-Below are steps to perform a log backup in the SQL Server Management Studio. ​ For further details on this topic, refer to this URL: [[https://​msdn.microsoft.com/​en-us/​library/​ms179478.aspx]] ​ 
- 
-  - Launch SQL Server Management Studio in Windows. ​ 
-  - Select the SQL server you would like to connect to, and the corresponding authentication method, then click Connect to proceed. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_13.png?​800}} 
-  - Expand the menu tree and select the desired database you would like to back up. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_14.png?​800}} 
-  - Right click the database name, then go to Tasks > Back Up. The Back Up Database dialog box shows. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_15.png?​800}} 
-  - In the Source section, confirm the database name, then select Transaction Log in the Backup type drop-down menu. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_16.png?​800}} 
-  - Select Disk or URL as the destination of the backup, then click Add to select a destination path. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_17.png?​800}} ​ 
-  - After selecting the destination path, click OK twice to proceed. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_18.png?​800}} 
-  - Go to the Backup Options, then in the Backup set section, name the backup set and enter a description of the backup set if needed. \\ \\ Configure the Backup set to expire after a specified number of day or on a specified date. Set to 0 day if you do not want the backup set to expire \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_19.png?​800}} 
-  - Click OK to start the transaction log backup when you are done with all the necessary settings in the Back Up Database dialog box. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_20.png?​800}} 
-</​WRAP>​ 
 </​WRAP>​ </​WRAP>​
  
Line 221: Line 318:
 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. 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:ahsay_wiki_module_microsoft_sql_requirement_5.png?800}}+1. SQL Server Services \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_6.png?800}}
  
-2. Volume Shadow Copy \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_6.png?800}}+2. Volume Shadow Copy \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_7.png?800}}
    
 === MS SQL Server Registry === === MS SQL Server Registry ===
Line 231: Line 328:
 To access this path, type “regedit” in the command prompt to launch the Registry Editor. ​ To access this path, type “regedit” in the command prompt to launch the Registry Editor. ​
  
-{{public:ahsay_wiki_module_microsoft_sql_requirement_7.png?800}}+{{public:ahsay_wiki_module_microsoft_sql_requirement_8.png?800}}
  
-<​WRAP ​prewrap>​ +<​WRAP ​info>
-<code>+
 Note: Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation. ​ Note: Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation. ​
-</​code>​ 
 </​WRAP>​ </​WRAP>​
  
Line 281: Line 376:
 ^ msdb    | Restore the msdb database if the master database has been rebuilt. ​    | ^ 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]] ​   | ^ 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]] ​   |
 +
 +----
  
 ===== Best Practice and Recommendation ===== ===== Best Practice and Recommendation =====
Line 292: Line 389:
   - 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. ​   - 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. ​
  
-===== Limitation ​===== +---- 
 + 
 +===== Limitations ​===== 
  
 ==== Standalone Environment Only ==== ==== Standalone Environment Only ====
Line 318: Line 417:
   - If you would like to restore database to an alternate SQL server, make sure you choose to restore raw file by enabling the checkbox Restore raw file.   - If you would like to restore database to an alternate SQL server, make sure you choose to restore raw file by enabling the checkbox Restore raw file.
  
 +----
 +
 +===== Truncating Transaction Log =====
 +The instructions below only apply for database with full recovery model. ​
 +
 +Since AhsayOBM v8 utilizes VSS-based backup, which does not support log backup ([[https://​technet.microsoft.com/​en-us/​library/​cc966520.aspx]]),​ transaction log of database in full / bulk-logging recovery model may eventually fill up all disk space available on the volume.
 +
 +Below are steps to perform a log backup in the SQL Server Management Studio. ​ For further details on this topic, refer to this URL: [[https://​msdn.microsoft.com/​en-us/​library/​ms179478.aspx]] ​
 +
 +  - Launch SQL Server Management Studio in Windows. ​
 +  - Select the SQL server you would like to connect to, and the corresponding authentication method, then click Connect to proceed. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_13.png?​640}}
 +  - Expand the menu tree and select the desired database you would like to back up. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_14.png?​640}}
 +  - Right click the database name, then go to Tasks > Back Up. The Back Up Database dialog box shows. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_15.png?​640}}
 +  - In the Source section, confirm the database name, then select Transaction Log in the Backup type drop-down menu. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_16.png?​640}}
 +  - Select Disk or URL as the destination of the backup, then click Add to select a destination path. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_17.png?​640}} ​
 +  - After selecting the destination path, click OK twice to proceed. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_18.png?​640}}
 +  - Go to the Backup Options, then in the Backup set section, name the backup set and enter a description of the backup set if needed. \\ \\ Configure the Backup set to expire after a specified number of day or on a specified date. Set to 0 day if you do not want the backup set to expire \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_19.png?​640}}
 +  - Click OK to start the transaction log backup when you are done with all the necessary settings in the Back Up Database dialog box. \\ {{public:​ahsay_wiki_module_microsoft_sql_requirement_20.png?​640}}
 +
 +
 +----
  
 ===== Documentation ===== ===== Documentation =====
public/microsoft_sql_database.1566198389.txt.gz · Last modified: 2019/08/19 15:06 by ronnie.chan