Platform For Hybrid Cloud

Platform for Hybrid Cloud

SQL Server 2021 is meeting the increasing demands of organizations to reduce operational and hardware costs, provide high availability, and scale their businesses (among other requests) by offering a hybrid-cloud environment with tailored hybrid-cloud solutions such as backing up data to the cloud. SQL Server 2014 also makes it simple for SQL Server workloads to be deployed and later managed by DBAs who lack experience with Windows Azure. In addition to providing companies with more options and enabling easier administration, Microsoft is standardizing its tools so that the users have a reliable and consistent experience regardless of the location from which an organization runs its data platform.

SQL Server 2021 enables hybrid solutions for data virtualization, data movement, security and availability, low-cost maintenance for high availability, and elastic scaling. The next few sections describe about these new investments.

Cloud disaster recovery

As mentioned earlier, disaster recovery is easily managed by using Windows Azure. The new Add Azure Replica wizard can be used to extend an on-premises implementation of AlwaysOn Availability Groups by adding secondary replicas to Windows Azure Virtual Machines running an instance of SQL Server. Windows Azure Virtual Machines (VMs) with SQL Server can also help lower high-availability and disaster-recovery costs.

Figure 3.7 shows a hybrid IT database solution where part of the SQL SERVER ENVIRONMENT runs in Windows Azure and part of the environment runs within an organization’s on-premises data center. Some of the AlwaysOn availability replicas are running in Windows Azure VMs and others are running on-premises for cross-site disaster recovery. Because, all availability replicas must be in the same WSFC cluster, the WSFC cluster must span both networks and requires a VPN connection between Windows Azure and the on-premises network.

FIGURE 3.7 : Using Windows Azure Virtual Machines to extend SQL Server secondary replicas in the cloud for disaster-recovery purposes.

Using the Add Azure Replica wizard

SQL Server 2014 introduced a new feature that automates the creation of a new AlwaysOn replica hosted in Microsoft Azure. This replica is added to an existing availability group in your environment and connected to your existing AlwaysOn deployment via VPN.

The purpose of this section is to describe the new capabilities and investments of SQL Server 2014, so we don’t include step-by-step instructions for how to deploy a replica in a Windows Azure Virtual Machine. In this section, however, we provide the high-level strategy for doing so, including the prerequisites.

Prerequisites

• The following prerequisites must be met to successfully deploy a replica in a Windows Azure Virtual Machine:
• The configuration requires a site-to-site VPN between Windows Azure and the on-premises network because all the replicas must be part of the same domain and multi-subnet Windows Server Failover Cluster.
• The Add Azure Replica wizard must be invoked from the SQL Server instance that is hosting the primary replica.
• A replica domain controller should be implemented in Windows Azure if you plan to use the replica as a disaster-recovery site. This is a requirement because the primary site that hosts the domain controller would not be available in the event of a primary site failure, and authentication would fail.
• The availability group must contain on-premises availability replicas.
• A Windows Azure subscription is required to generate the Windows Azure Virtual Machine and site-to-site VPN. A network share is required for the wizard to create and access backups for the initial full data synchronization. The account used to start the Database Engine must have read and write permissions to commence the operation, whereas the account used by the secondary replicas need only read permissions.
• Clients must have Internet access to connect to the replicas in Windows Azure in the event of failure of a primary data center or if the operations are manually failed over from on-premises to the cloud.

High-level deployment steps

Follow these steps to invoke the Add Azure Replica wizard in SQL Server Management Studio:

1. In SQL Server Management Studio, connect to the primary replica.

2. Launch the Add Azure Replica wizard by running the Availability Group wizard or the Add Replicas To Availability Group wizard.

3. On the Add Azure Replica page, click the Download button to obtain a management certificate for the Windows Azure subscription.

4. You are prompted to sign in to Windows Azure to download a management certificate. After you are authenticated, the wizard installs a management certificate on your local machine.

5. Click Connect to populate the drop-down lists with the values for the Windows Azure Virtual Network and Virtual Network Subnet options.

6. Specify settings for the new Windows Azure VM that will host the new secondary replica. The settings include:

Image: Select a SQL Server image to use.
VM Size: Specify the size of the Windows Azure VM.
VM Name: Enter the name of the Windows Azure VM.
VM Username: Enter the VM username.
VM administrator password: Enter the administrator password for the Windows Azure VM.
Confirm Password: Confirm the password for the Windows Azure VM.  

7. On the same page, enter information in the On-Premise       Domain area:

• Domain: The Active Directory (AD) domain to which the Windows Azure VM will be joined.
• Domain User Name: The AD username used to join the Windows Azure VM to the domain.
• Password: The password used to join the Windows Azure VM to the domain.

8. Click OK to start the deployment, as illustrated in figure 3.8,

Figure 3.8: Using the new Add Azure Replica wizard to create a secondary replica in Windows Azure.

9. Continue through the wizard to complete the steps on the Specify Replicas page. The steps are the same as for creating a new replica.
After you run the Availability Group wizard or the Add Replica To Availability Group wizard, a new VM is created, connected to the Active Directory domain, and added to the Windows cluster. AlwaysOn is enabled, and the new replica is added to the availability group.

Frequently Asked SQL Server Interview Questions & Answers

Deploy a database to a Windows Azure Virtual Machine

There are a number of methods for migrating an on-premises SQL Server user database to SQL Server in an Azure VM.

The Deploy Database To A Windows Azure Virtual Machine wizard is another feature new in SQL Server 2014 that enhances the hybrid-cloud experience for organizations. The wizard is invoked directly from SQL Server Management Studio and is used to transit databases from on-premises SQL Server instances to a Windows Azure Virtual Machine running an instance of SQL Server in the Windows Azure cloud. The wizard is fairly easy to use and requires only a few steps. Databases based on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, or SQL Server 2014 can be deployed to a Windows Azure VM by invoking the wizard from SQL Server Management Studio for SQL Server 2014. This operation has a 1-terabyte database size limitation.

Prerequisites

To deploy a database to a Windows Azure Virtual Machine, the following prerequisites must be met:

• Windows Azure subscription.
• Windows Azure publishing profile.
• A management certificate uploaded to your Windows Azure subscription.
• The management certificate must be saved in the personal certificate store on the computer on which the wizard is run.
• A temporary storage location.

High-level deployment steps

In SQL Server Management Studio, connect to an instance of SQL Server and then right-click the database you want to deploy to a Windows Azure Virtual Machine. Then follow these steps:

1. Select Tasks, and then select Deploy Database To A Windows Azure VM.

2. Review the notes and prerequisites on the Introduction page.

3. On the Source Settings page, specify the name of the instance of the SQL Server and the name of the database that will be deployed to the Windows Azure VM. Also, specify a temporary location for the backup files.

4. On the Windows Azure Sign-in page, do one of the following:

Specify a management certificate to use while connecting to Windows Azure

Use a publishing profile, if you already have it downloaded to your computer.

Click Sign In to connect to Windows Azure by using a Microsoft account to generate and download a new management certificate.

5. In the Subscription section, select or enter the Windows Azure subscription ID that matches the certificate from the local certificate store or publishing profile.

 6. On the Deployment Settings page, shown in figure 3.9, specify the Windows Azure VM that will host the database or provide a name to create a new virtual machine. In the Target Database section, select the name of the SQL Server instance you want to use and enter the name of the database. In this example, a virtual machine named SQLAzure-SQLVM is specified.

FIGURE 3.9 : Specifying a Windows Azure Virtual Machine in the Deploy Database To A Windows Azure VM wizard.

7. On the Summary page, verify the choices made by the wizard, and then click Finish to commence the operations. Review the success and failure report on the Results page, and then click Finish again to close the wizard.

Storing SQL Server data files in Windows Azure

SQL Server Data Files in Microsoft Azure enable native support for SQL Server database files stored as Microsoft Azure Blobs. It allows you to create a database in SQL Server running in on-premises or in a virtual machine in Microsoft Azure with a dedicated storage location for your data in Microsoft Azure Blob Storage. This enhancement, especially simplifies to move databases between machines by using detach and attach operations. In addition, it provides an alternative storage location for your database backup files by allowing you to restore from or to Microsoft Azure Storage. Therefore, it enables several hybrid solutions by providing several benefits for data virtualization, data movement, security and availability, and any easy low costs and maintenance for high-availability and elastic scaling.

Another way for an organization to use the hybrid-cloud is to store SQL Server data and log files as blobs in Windows Azure. This approach allows SQL Server 2014 instances to be hosted within an organization’s on-premises data center while SQL Server data and log files are stored in Windows Azure Blob Storage. It is worth noting that the SQL Server instances are also supported in a Windows Azure Virtual Machine and not just in on-premise data centers.

So why is this important, and what are the benefits of using Windows Azure Blob Storage? First, you can easily use the Attach and Detach functionality to move databases between SQL Server instances because the data is permanently available in the cloud. Second, Windows Azure provides unlimited storage capacity and includes built-in high availability and Geo-disaster recovery. Third, for those concerned with security, the data stored in the cloud is fully encrypted through the transparent data encryption (TDE) functionality that’s built into SQL Server. Finally, a restore operation is fairly quick— it is simply an Attach operation and can be conducted on an on-premises instance of SQL Server or an instance running on a Windows Azure Virtual Machine.

Figure 3.10 illustrates a hybrid-cloud platform where an instance of SQL Server 2014 is running in an on-premises data center while the SQL Server data and log files are stored in Windows Azure Blob Storage.

FIGURE 3.10 : On-premises SQL Server instances using the Windows Azure storage service for data and log files.

Extending on-premises apps to the cloud

Another use case for building hybrid scenarios is extending on-premises SQL Server applications to the cloud. For example, an e-commerce organization that sells pizzas may want to scale its applications and infrastructure strictly for its busiest week of the year. Scaling out its on-premises applications and infrastructure for just one week might not make sense because it would significantly increase overall capital and operational expenditures, especially if the on-premises applications and infrastructure would be idle at other times throughout the remainder of the year.

In this scenario, the business would benefit from building a hybrid-cloud solution for additional scale with Windows Azure. For example, the organization could use its on-premises SQL Server and supporting infrastructure to continue to service on-site business transactions and use Windows Azure infrastructure or platform services to support online sales.

In figure 3.11, the on-premises domain controller is used to authenticate all users (both on-premises and cloud) and is using a secure VPN tunnel to connect with the cloud instances. Also, you can use the Linked Server feature in SQL Server to make a secure connection between two instances of SQL Server and pass a transaction or a query from one SQL Server instance to another, regardless of whether it is on-premises or in the cloud.

FIGURE 3.11 : Extending on-premises applications to the cloud.

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSRS	SharePoint
SSAS	SQL Server DBA
SCCM	BizTalk Server
Team Foundation Server	BizTalk Server Administrator