Microsoft Dynamics CRM 2011 Cookbook
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

Backing up Dynamics CRM 2011 Database Server

A database server backup plan is very important for ensuring the ability to recover in case of any disaster. The Dynamics CRM 2011 Database Server backup strategy will usually be of the following two types:

  • To back up the Windows Server machine, which is running the Dynamics CRM 2011 Database server
  • To back up the Dynamics CRM 2011-related databases using Microsoft SQL Server's in-built backup and recovery feature

Moreover, some level of hardware fault tolerance has to be present for a database server. This can mean the usage of a RAID-5 disk array for the databases and a RAID-1 (mirror) for the transaction logs.

Windows Server backup can be used to create and manage automatic scheduled backups of the database server. More information about Windows Server backup and recovery can be found at the following link:

http://technet.microsoft.com/en-in/library/dd979562(v=ws.10).aspx

In this recipe, we will discuss backing up the database server using SQL Server's backup feature. The Dynamics CRM 2011 Database backup plan should include database maintenance and database backups.

Getting ready

Our database backup plan should include each of the Dynamics CRM 2011 databases to make sure of a proper recovery in case one or all databases fail. The backup of MSCRM_CONFIG and OrganizationName_MSCRM databases should include a full database backup and some number of transaction log backups. For databases that are updated infrequently, such as the master and msdb databases, we can have the full database backed up.

One important aspect of the database backup strategy is the frequency of the backup, that is, how frequently a database should be backed up. The recommendation here is to make frequent backups, but you need to make sure that the backup process does not impact the availability of the system to the end users to a great extent.

Note

Frequent, full database backups reduce the number of restores after any database failure.

The other aspect of the database backup strategy depends on how much data loss is acceptable to business users in case of a database failure. For example, if a maximum of an hour's data loss is acceptable, transaction logs should be backed up every one hour and an entire database should be backed up every day to reduce the number of restores.

How to do it…

The following steps will help create a database maintenance plan for the Dynamics CRM 2011 databases:

  1. Launch SQL Server Management Studio by navigating to Start | All Programs | Microsoft SQL Server 2008 R2 | SQL Server Management Studio.
    Note

    If the database is running the SQL Server 2012 version, the navigation path will be Start | All Programs | Microsoft SQL Server 2012 | SQL Server Management Studio.

    SQL Server Management Studio can be installed on a machine that is not running the Dynamics CRM 2011 SQL Server databases.

    Log in to the Dynamics CRM 2011 Database Server with a user having a sysadmin role. Then expand the Management node in Object Explorer.

    Note

    A user having the sysadmin role can only create or manage maintenance plans. Object Explorer only displays the Maintenance Plans node for users who are members of the sysadmin fixed server role.

  2. Under the Management node, right-click on Maintenance Plans and select Maintenance Plan Wizard as shown in the following screenshot:
  3. On the SQL Server Maintenance Plan Wizard page, click on Next to continue.
  4. On the Select Plan Properties page, provide the Name and Description values for the maintenance plan.

    Then select one of the following schedules:

    • Separate schedules for each task: This will create a separate schedule for each task added to the maintenance plan.
    • Single schedule for the entire plan or no schedule: This will create one schedule that applies to all tasks added to the maintenance plan.

    In this recipe, select the Single schedule for the entire plan or no schedule option as we intend to create only one schedule for our entire maintenance plan. Then click on Next to proceed.

    If the database is SQL Server 2012, then under Run as select the account that Microsoft SQL Server Agent uses when executing the maintenance plan.

  5. On the Select Maintenance Tasks page, select one or more maintenance plan tasks. Here the recommendation is to select the following maintenance tasks:
    • Check Database Integrity: This maintenance task, by executing the DBCC CHECKDB T-SQL statement, would check the allocation and structural integrity of user and system tables and indexes to ensure that any integrity problems with the database are reported.
    • Back Up Database (Full): This maintenance task, by executing the BACKUP DATABASE T-SQL statement, would back up the whole database.
    • Back Up Database (Transaction Log): This maintenance task, by executing the BACKUP LOG T-SQL statement, would back up the transaction logs of the database.

      Then click on Next to proceed.

  6. On the Select Maintenance Task Order page, select the following order (usually the proposed order):
    1. Check Database Integrity
    2. Back Up Database (Full)
    3. Back Up Database (Transaction Log)

    Then click on Next to proceed.

  7. Then the Define Database Check Integrity Task page is shown, and on this page we need to define the databases that the Check Database Integrity task has to be associated with. Click on the Databases drop-down list and select the These databases option. Thereafter, select the following databases by ticking the checkboxes associated with each of them:
    • master
    • msdb
    • MSCRM_CONFIG
    • <OrganizationName>_MSCRM
      Note

      There can be more than one organization database, so select all of them.

    • ReportServer
    • ReportServerTempDB

    Click on OK to confirm the selection and then click on Next to proceed as shown in the following screenshot:

  8. Then the Define Back Up Database (Full) Task page is shown, and on this page we need to define the databases that the full backup task has to be associated with. Click on the Databases drop-down list and select the These databases option. Thereafter, select the following databases by ticking the checkboxes associated with each of them:
    • master
    • msdb
    • MSCRM_CONFIG
    • <OrganizationName>_MSCRM
      Note

      There can be more than one organization database, so select all of them.

    • ReportServer
    • ReportServerTempDB

    Here select the Create a backup file for every database option, and then we can also modify the following options:

    • Backup files location: We can choose a file location (different from the default one) to store the backup files using a file browser associated with it and can define the backup file extension as well
    • Backup file extension: The default extension is .bak
    • Verify backup integrity: Check this option to verify that the backup set is complete and that all volumes are readable
    • Set backup compression: Select from the list whether we want to compress the database backup or not
    Note

    By default, compression significantly increases CPU usage and the additional CPU consumed by the compression process might adversely affect concurrent operations.

    Then, click on OK to confirm the selection and finally click on Next to proceed.

  9. Next, we will see the Define Back Up Database (Transaction Log) Task page, and on this page you need to define the databases the transaction log backup task will be associated with.

    Click on the Databases drop-down list and select the These databases option. Here, do not select databases, such as master and msdb, as these databases are updated infrequently.

    Apply the same choices defined in the previous step for backup location, backup file extension, and backup file compression choices.

    After making the selections, click on OK to confirm and then click on Next to proceed.

  10. Next, we will see the Select Report Options page. Here a file location other than the default one can be provided in the Folder location: field of the Write a report to a text file option. This location is used to save a report of the maintenance plan actions, and this report can be e-mailed by selecting the Email report option and providing an e-mail address under the To option.
  11. Next, we will see the Complete the Wizard page; click on Finish to complete the maintenance plan.
  12. On the Maintenance Wizard Progress page, monitor the status information of the actions of the Maintenance Plan Wizard, and once all the task statuses end on Success, click on Close to close the wizard.

After creating the maintenance task, we will now schedule it to run automatically at a scheduled time by performing the following steps:

  1. In Object Explorer, expand the SQL Server Agent node and then expand the Jobs node under it.
  2. Select the job that has the name in the <MaintenancePlan_Name>.Subplan_1 format. Then right-click on this task and select Rename. Modify the Job Name field to a better, self-explanatory name.
  3. Again, right-click on the job name and select Properties from the context menu.
  4. This will launch the Job Properties page. Then, click on the Schedules page under the Select a page list.
  5. Click on New from the task pane at the bottom. This will open the New Job Schedule window. Provide the following details, such as the name, schedule type, frequency, daily frequency, and duration in their respective fields. Then click on OK to save the details:
  6. Again, click on OK to save the scheduled job.

How it works…

The maintenance plan created in this recipe includes checking database integrity and taking the full and transactional backups of Dynamics CRM 2011 databases. Once the maintenance plan is created, it can be executed manually as well by right-clicking on the maintenance plan and then selecting Execute. The maintenance plan created can also be modified in the future. This can be done by right-clicking on the maintenance plan name and then selecting Modify.

上一章目录下一章