Aracılığıyla paylaş

Back up SQL Server on Azure Stack using Azure Backup

This article describes how to configure Microsoft Azure Backup Server (MABS) to protect SQL Server databases on Azure Stack.

SQL Server databases protection workflow

Management of the SQL Server database backup to Azure and recovery from Azure involves:

  1. Create a backup policy to protect SQL Server databases
  2. Create on-demand backup copies
  3. Recover the database from Disks, and from Azure

Desteklenen senaryolar ve sınırlamalar

Before you back up SQL Server on Azure Stack, review the following supported scenarios and limitations:

  • Uzak dosya paylaşımında dosyaları olan bir veritabanınız varsa, koruma Hata Kimliği 104 ile başarısız olur. MABS, uzak bir dosya paylaşımındaki SQL Server verileri için korumayı desteklemez.
  • MABS, uzak SMB paylaşımlarında depolanan veritabanlarını koruyamaz.
  • Kullanılabilirlik grubu çoğaltmalarının yalnızca okunabilir olarak yapılandırıldığından emin olun.
  • NTAuthority\System sistem hesabını SQL Server'da Sysadmin grubuna açıkça eklemeniz gerekir.
  • Kısmen kapsanan bir veritabanı için alternatif bir konum kurtarma işlemi gerçekleştirdiğinizde, hedef SQL örneğinde Kapsanan Veritabanları özelliğinin etkinleştirildiğinden emin olmanız gerekir.
  • Dosya akışı veritabanı için alternatif bir konum kurtarma işlemi gerçekleştirdiğinizde, hedef SQL örneğinde dosya akışı veritabanı özelliğinin etkinleştirildiğinden emin olmanız gerekir.
  • SQL Server AlwaysOn için koruma:
    • MABS, koruma grubu oluşturma sırasında sorgu çalıştırırken Kullanılabilirlik Gruplarını algılar.
    • MABS detects a failover and continues protection of the database.
    • MABS, SQL Server örneği için çok siteli küme yapılandırmalarını destekler.
  • Always On özelliğini kullanan veritabanlarını koruduğunuzda MABS aşağıdaki sınırlamalara sahiptir:
    • MABS, yedekleme tercihlerine göre SQL Server'da ayarlanan kullanılabilirlik grupları için yedekleme ilkesini aşağıdaki gibi kabul eder:
      • Prefer secondary - Backups should occur on a secondary replica except when the primary replica is the only replica online. Birden çok ikincil replika varsa, yedekleme amacıyla en yüksek yedekleme önceliğine sahip düğüm seçilir. If only the primary replica is available, then the backup should occur on the primary replica.
      • Sadece ikincil - Yedekleme birincil kopyada gerçekleştirilmemelidir. If the primary replica is the only one online, the backup shouldn't occur.
      • Birincil - Yedeklemeler her zaman birincil çoğaltmada gerçekleşmelidir.
      • Any Replica - Backups can happen on any of the availability replicas in the availability group. Yedekleme yapılacak olan düğüm, her bir düğümün yedekleme önceliklerine göre belirlenecektir.

    • Uyarı

      • Yedeklemeler, okunabilir herhangi bir kopyadan (birincil, zaman uyumlu ikincil, zaman uyumsuz ikincil) gerçekleşebilir.
      • If any replica is excluded from backup, for example Exclude Replica is enabled or is marked as not readable, then that replica won't be selected for backup under any of the options.
      • Birden çok çoğaltma varsa ve okunabiliyorsa, yedekleme için en yüksek önceliğe sahip düğüm seçilir.
      • Seçilen düğümde yedekleme başarısız olursa yedekleme işlemi başarısız olur.
      • Özgün konuma kurtarma desteklenmez.
  • SQL Server 2014 veya üzeri yedekleme sorunları:
    • SQL server 2014 added a new feature to create a database for on-premises SQL Server on Microsoft Azure Blob storage. MABS, bu yapılandırmayı korumak için kullanılamaz.
    • SQL Always On seçeneği için "İkincil tercih et" yedekleme tercihiyle ilgili bilinen bazı sorunlar vardır. MABS her zaman ikincilden yedek alır. İkincil bulunamazsa yedekleme başarısız olur.

Önkoşullar

Before you back up SQL Server on Azure Stack, install and prepare Azure Backup Server.

Yedekleme ilkesi oluşturma

To create a backup policy to protect SQL Server databases to Azure, follow these steps:

  1. On the Azure Backup Server, select the Protection workspace.

  2. On the tool menu, select New to create a new protection group.

    Screenshot shows how to initiate creating Protection Group.

    Azure Backup Server starts the Protection Group wizard, which leads you through creating a Protection Group. sonrakiseçin.

  3. On the Select Protection Group Type blade, select Servers.

    Screenshot shows how to select Protection Group Type - Servers.

  4. On the Select Group Members blade, the Available members list displays the various data sources. Select + to expand a folder and reveal the subfolders. Select the checkbox to select an item.

    Screenshot shows how to select a SQL database.

    All selected items appear in the Selected members list. After selecting the servers or databases you want to protect, select Next.

  5. On the Select Data Protection Method blade, provide a name for the protection group and select the I want online Protection checkbox.

    Screenshot shows the Data Protection Method - short-term disk & Online Azure.

  6. On the Specify Short-Term Goals blade, include the necessary inputs to create backup points to disk, and select Next.

    In the example, Retention range is 5 days, Synchronization frequency is once every 15 minutes, which is the backup frequency. Express Full Backup is set to 8:00 P.M.

    Screenshot shows the short-term goals.

    Uyarı

    In the example shown, at 8:00 PM every day a backup point is created by transferring the modified data from the previous day’s 8:00 PM backup point. Bu işleme Hızlı Tam Yedekleme adı verilir. Transaction logs are synchronized every 15 minutes. If you need to recover the database at 9:00 PM, the point is created from the logs from the last express full backup point (8PM in this case).

  7. On the Review disk allocation blade, verify the overall storage space available, and the potential disk space. sonrakiseçin.

  8. On the Choose Replica Creation Method, choose how to create your first recovery point. You can transfer the initial backup manually (off network) to avoid bandwidth congestion or over the network. If you choose to wait to transfer the first backup, you can specify the time for the initial transfer. sonrakiseçin.

    Screenshot shows the initial replication method.

    The initial backup copy requires transferring the entire data source (SQL Server database) from production server (SQL Server computer) to Azure Backup Server. This data might be large, and transferring the data over the network could exceed bandwidth. For this reason, you can choose to transfer the initial backup: Manually (using removable media) to avoid bandwidth congestion, or Automatically over the network (at a specified time).

    Once the initial backup is complete, the rest of the backups are incremental backups on the initial backup copy. Artımlı yedeklemeler küçük olma eğilimindedir ve ağ üzerinden kolayca aktarılır.

  9. Choose when you want the consistency check to run and select Next.

    Screenshot shows how to schedule the consistency check.

    Azure Backup Server performs a consistency check on the integrity of the backup point. Azure Backup Server calculates the checksum of the backup file on the production server (SQL Server computer in this scenario) and the backed-up data for that file. If there's a conflict, it's assumed the backed-up file on Azure Backup Server is corrupt. Azure Backup Server rectifies the backed-up data by sending the blocks corresponding to the checksum mismatch. Because consistency checks are performance-intensive, you can schedule the consistency check or run it automatically.

  10. To specify online protection of the datasources, select the databases to be protected to Azure and select Next.

    Screenshot shows how to select data sources.

  11. Choose backup schedules and retention policies that suit the organization policies.

    Screenshot shows hot to backup schedule and retention.

    In this example, backups are taken once a day at 12:00 PM and 8 PM.

    Uyarı

    It’s a good practice to have a few short-term recovery points on disk, for quick recovery. Bu kurtarma noktaları, işletimsel kurtarma için kullanılır. Azure serves as a good offsite location with higher SLAs and guaranteed availability.

    Best Practice: If you schedule backups to Azure to start after the local disk backups complete, the latest disk backups are always copied to Azure.

  12. Saklama politikası zamanlamasını seçin. The details on how the retention policy works are provided at Use Azure Backup to replace your tape infrastructure article.

    Screenshot shows how to choose the retention Policy.

    Bu örnekte:

    • Backups are taken once a day at 12:00 PM and 8 PM and are retained for 180 days.
    • The backup on Saturday at 12:00 P.M. is retained for 104 weeks
    • The backup on Last Saturday at 12:00 P.M. is retained for 60 months
    • The backup on Last Saturday of March at 12:00 P.M. is retained for 10 years

  13. Select Next and select the appropriate option for transferring the initial backup copy to Azure. You can choose Automatically over the network

  14. Once you review the policy details in the Summary blade, select Create group to complete the workflow. You can select Close and monitor the job progress in Monitoring workspace.

    Screenshot shows the in-progress job state of the Protection Group creation.

İsteğe bağlı yedekleme çalıştırma

A recovery point is created only when the first backup occurs. After creating a backup policy, you can trigger the creation of a recovery point manually, rather than waiting for the scheduler to take the backup.

To run an on-demand backup of a SQL Server database, follow these steps:

  1. Wait until the protection group status shows OK for the database before creating the recovery point.

    Screenshot shows the Protection Group members.

  2. Right-click the database and select Create Recovery Point.

    Screenshot shows how to start creating the online Recovery Point.

  3. Choose Online Protection in the drop-down menu and select OK to start creation of a recovery point in Azure.

    Screenshot shows how to choose the Online Protection option.

  4. View the job progress in the Monitoring workspace.

    Screenshot shows the monitoring console.

Recover the database from Azure

To recover a protected entity (SQL Server database) from Azure, follow these steps:

  1. Open the Azure Backup Server Management Console. Navigate to Recovery workspace where you can see the protected servers. Browse the required database (in this case ReportServer$MSDPM2012). Select a Recovery from time that's specified as an Online point.

    Screenshot shows how to select a Recovery point.

  2. Veritabanı adına sağ tıklayın ve Kurtar'ı seçin.

    Screenshot shows how to select a database to recover from Azure.

  3. MABS shows the details of the recovery point. sonrakiseçin. Veritabanını üzerine yazmak için, kurtarma türü olarak SQL Server'ın özgün örneğine kurtarma işlemini gerçekleştirin seçeneğini seçin. sonrakiseçin.

    Screenshot shows how to recover database to original location.

    In this example, MABS recovers the database to another SQL Server instance, or to a standalone network folder.

  4. On the Specify Recovery options blade, you can select the recovery options like Network bandwidth usage throttling to throttle the bandwidth used by recovery. sonrakiseçin.

  5. On the Summary blade, you see all the recovery configurations provided so far. Kurtar'ı seçin.

    The Recovery status shows the database being recovered. Sihirbazı kapatmak ve İzleme çalışma alanında ilerleme durumunu görüntülemek için Kapat'ı seçebilirsiniz.

    Screenshot shows how to initiate the recovery process.

    Once the recovery is completed, the restored database is application consistent.

Sonraki adımlar

  • Last updated on