SQL Server introduction and architecture overview
SQL Server is Microsoft’s relational database which is typically run on Windows Server hosts. With Delphix, you can use various SQL Server configurations, ranging from Failover Clusters to Availability Groups. In this section, you’ll find an overview of how Delphix works with SQL Server.
There are three key concepts when using Delphix with any data platform:
Environments: The server and software required to run a data set.
Source Environment: Source data to be ingested into Delphix. These will be used to create dSources.
Target Environment: Target hosts to provision VDBs.
dSources: A database that the Delphix Virtualization Engine uses to create and update or maintain virtual copies of your database
VDBs: A database provisioned from either a dSource or another VDB which is a copy of the source data. A VDB is created and managed by the Delphix Virtualization Engine.
With these concepts in mind, let’s explore how Delphix connects to SQL Server environments and creates SQL Server dSources and VDBs.
Staging push mechanism with SQL server
Starting Delphix Engine version 6.0.13.0 a new data ingestion mechanism has been introduced that will help users to push data into the staging database on their own.
The previous data ingestion mechanism of the SQL Server has a few limitations like dependency on the source access and limited backup vendor's support. Currently, Delphix supports Native, Commvault, NetBackup, Lightspeed, and Redgate backup vendors.
With Staging Push implementation, we have removed the dependency of accessing the customer's production database and also enabled the customers who are using the backup vendors that Delphix does not support.
To summarize the Staging Push mechanism.
Customers now have ownership of the staging databases.
Customers are now responsible to keep the staging database in sync with the source database. Note that the database files of the staging database are stored on Delphix Storage.
Delphix is still responsible to snapshot the underlying data files and gathering any metadata required for provisioning from the snapshots.
Delphix in multi-domain windows environments
General Overview
When considering the Delphix logical architecture, there are four primary components:
Source host(s)
Continuous Data Engine
VDB Target host(s)
Staging Target Host(s)
In SQL Server environments, the staging target host is used for staging data from the source database on the source host into Delphix. Although you can use any VDB target host on which the Delphix Connector service has been installed for this purpose, Delphix recommends a dedicated Staging Target Host for load isolation and separation of roles.
This page focuses on the process of getting source SQL Server data into the storage of the Continuous Data Engine (DVE) via the Staging Target Host.
When considering SQL Server deployments in different enterprise environments, we often see cases where the production, development, test, or reporting environments exist in different Windows domains which may or may not have trust relationships. Such varying domain approaches can come into play due to security, organizational, geographical, or other technical reasons, and can make communication between Windows hosts more complicated to manage. Delphix is flexible enough to work in many configurations, but we want to help you choose the solution that best suits your unique environment.
We listed the four primary components of the Delphix logical architecture for SQL Server above. In addition, a fifth component in the Delphix logical architecture might be considered for use-cases #3, #4, and #5 in the Technical Overview below: a Connector host. The function of the Delphix Connector on that host is the discovery of the source environment via remote registry and ODBC calls. There are no Delphix software installation requirements for Windows source hosts, but it might be helpful to note this role can co-reside directly on the Windows source host for consolidation purposes if desired.
Technical overview
Keep in mind that the Delphix Engine is always syncing with backups of the source database. It is never the live data that is ingested; it is always backups of different flavors.
If SQL Server simple recovery mode is used, these can be full or differential backups initiated by the source database. If full recovery mode is enabled, the Delphix Engine will typically leverage only transaction log backups after the initial data load. Again, the source database would initiate backups, and the Delphix Engine would collect the backup files that have been created by SQL Server. This approach of using transaction logs minimizes spikes in system load by ingesting smaller backups more often. Another option is copy-only backups, which the Delphix Engine initiates in a configuration known as Delphix Managed Backups. For more information refer to Linking SQL Server dSources with Delphix managed backups.
Delphix can ingest database and log data from native backups, as well as a number of third-party backup products. SQL Server restores the backups onto the shared Delphix storage on the staging target host running the databases in recovery mode. We call this process “validated sync,” which is why you may hear the staging target also referred to as a validated sync server.
It is important to note that the Continuous Data Engine (based on DxOS, itself derived from a UNIX-based OS) is not a domain member itself. The credentials we discuss in this document are between Windows servers, and the key domain-specific authentication is between the staging host and the UNC path to the SMB share where the backup data is stored.
In the rest of this section, we will describe multiple scenarios. Review them to determine which will fit best in your environment.
Case 1: Staging target in test environment
In this case, we will review an environment with two domains: PRODUCTION and TEST, which have a domain trust relationship. This is one of the simplest and most straightforward approaches, as illustrated in the Staging Target in Test Environment diagram below.
In this example, the staging target host exists in the non-production TEST domain, but because of domain trust, accounts located in that domain can access resources in the PRODUCTION domain. This would allow the staging target host to connect to the PRODUCTION source host both for environment discovery and to the shared backup location “\source\backups” over Server Messaging Block (SMB) to access database and transaction log backups.
Staging Target in Test Environment
Case 2: Staging target in production environment
The scenario illustrated in the Staging Target in Production Environment diagram below shows a TEST domain that does not have access to resources in the PRODUCTION domain. However, the customer has determined that Delphix VDBs must be provisioned to the TEST domain. In this scenario, you can use the staging target host in the PRODUCTION domain to link to the PRODUCTION database and perform the normal restore of the DB and/or log files to the Delphix storage. You can then provision VDBs in the TEST domain.
In this case, VDBs can be completely isolated from the PRODUCTION domain, and there is no requirement for hosts in the TEST domain to have any direct access to resources in the PRODUCTION domain.
Staging Target in Production Environment
Case 3: Domain-agnostic storage
This example shows a shared backup location that is not dependent on trust relationships between the PRODUCTION and TEST domains. Because Delphix uses UNC paths, it can support any protocol which provides UNC access for that backup data access – for example, SMB or iSCSI.
This is shown by the Domain-agnostic Storage diagram below by the arrow – stretching from bottom-left toward the upper-right and crossing the domain boundary – representing any UNC-compatible protocol connecting the staging target host to the data on the NAS host. Provided that the Delphix environment users on both the source host and staging target host have read/write access to the shared backup location on network-attached storage (NAS), the SQL Server instance running on the staging target host will be able to access the backup files needed.
Although this option is not specific to this case, you may notice we separated a connector role to its own connector host. As you can infer from the diagram, the Delphix Connector’s primary function on that host is the discovery of the source environment via remote registry and ODBC calls. Despite the fact that there are no software installation requirements for the source hosts in PRODUCTION, it may be helpful to note that you can even install this role directly on the source server for consolidation if you want to.
Domain-agnostic Storage
Case 4: Migrating backup files
In this somewhat more complex configuration, backup files are sent to storage in the PRODUCTION domain, while the host used to link to the source and perform the validated sync is in an isolated TEST domain. We have used a separate connector host in the PRODUCTION domain again, to perform the environment discovery of the source host there. Backup files for SOURCE are being stored on NAS.
We will link using the Staging Target Host and create VDBs in the TEST domain. When the Delphix Engine discovers that a new backup of PRODUCTION has been taken, it will attempt to find the relevant files in the shared backup location provided during linking. It does this by periodically performing a recursive search for the file names on the shared backup location. If it does not find the specific files, it will try again later. Knowing this, we can specify a shared backup location in the TEST domain and set up an automated process to copy the backup files from \nas\backups in the PRODUCTION domain to \staging\backups in the TEST domain. We can use any copy mechanism to transfer the files, such as FTP or ROBOCOPY. The files must be available long enough for the Delphix Engine to detect and apply them to the recovery database on the Staging Target Host before removal.
We have customers who also use this model in cases with multiple data centers (on-premise deployments) or virtual private clouds (cloud deployments) rather than multiple domains. These customers want database and transaction log backups to be available in secondary data centers or private clouds, but they want to make sure that the data is only copied over the WAN once.
Migrating Backup Files
Case 5: SMB anonymous access
In this example (shown by the SMB Anonymous Access diagram below), a Windows SMB connection is traversing domains that do not have a trust relationship. This approach is problematic because there is no simple configuration for SMB file sharing that does not rely on domain trusts. As a result, there is no way to specifically grant accounts in the TEST domain access to SMB shares in the PRODUCTION domain.
Because such users cannot be authenticated, they are treated as “anonymous” users and do not have permission to any resources by default.
Windows provides an “Everyone” group. However, this group still only applies to accounts that can be authenticated in the domain, so you cannot use that group in this case. There is still a way to configure access to the shared backup location on \source\backups by accounts in other domains, such as TEST. However, because it relies on anonymous access, you will need to consider the security implications of enabling this method, as well as measures that could mitigate any additional risk in your environment – for example, a private VLAN or IPSEC between hosts.
Enable the “Guest” account on the server source Server – for example, \SQLPROD.
Create a share where full and transaction log backups will be stored – for example, \SQLPROD\backups.
Configure read-only security access for both the folder security permissions on the shared directory and the share permissions for the “guest” account.
SMB Anonymous Access
Here are some additional links from Microsoft that relate to anonymous sharing: