Microsoft SQL Server Support FAQ

What is SQL Server CDM? How does it help solve my challenges?

Database administrators working extensively with SQL Server are challenged when faced with mission-critical use cases such as Backup, Recovery, DevOps, and Business Analytics. This is especially true given that their SQL Server databases have expanded in size and number over time, and that the databases need to be up and running 24x7x365.

IBM Spectrum® Copy Data Management simplifies SQL Server copy management by enabling administrators to orchestrate application-consistent copy creation, cloning and recovery in minutes, instead of hours or days. IBM Spectrum Copy Data Management copy management leverages the advanced snapshot and replication features of the underlying storage platform to rapidly create, replicate, clone, and restore copies of SQL Server databases in the most efficient way possible, in both time and space. IBM Spectrum Copy Data Management enables you to focus on the backup and restore requirements of your business rather than the technical details of the underlying storage platforms.

IBM Spectrum Copy Data Management is an intelligent copy data management solution that delivers end-to-end automation, orchestration, and self-service functionality for your SQL Server environment through a comprehensive and scalable catalog. With the self-service features of IBM Spectrum Copy Data Management, your users are empowered to create clones on demand, freeing DBAs, while at the same time offering the advanced recovery features needed for SQL Server environments.

Deployment and Registration

Do I need to deploy any additional agents to protect SQL Server standalone, Failover Cluster or AlwaysON configuration?

IBM Spectrum Copy Data Management for SQL Server is delivered as a VMware OVA that is easily deployed on demand in a matter of minutes. Once deployed, you simply register your SQL Servers with appropriate credentials and then let IBM Spectrum Copy Data Management discover the rest. IBM Spectrum Copy Data Management eliminates the complexity of manually deploying and maintaining application agents on SQL Servers. A lightweight application-aware agent is automatically injected and updated to the required SQL Servers on demand.

SQL Server Backup workflow

Application-consistent SQL Server backup (local and remote) - Step by step

IBM Spectrum Copy Data Management auto-discovers databases and enables copies only of eligible databases. To be eligible for IBM Spectrum Copy Data Management backup, the SQL Server database needs to be residing on a supported storage platform. With IBM Spectrum Copy Data Management, application owners do not need to be concerned about storage infrastructure.

IBM Spectrum Copy Data Management creates and uses in-place copies, so no data is physically moved. IBM Spectrum Copy Data Management generated application-consistent copies are both space and time efficient. With the same ease, a DBA can automate the creation of remote copies for disaster recovery use cases.

Does SQL Server solution leverage the storage consistency group feature?

The storage consistency group feature allows storage administrators to take a snapshot of database applications where the data is spread across multiple volumes to maintain consistency across all volumes.

In a typical SQL Server Database, the data is spread across different volumes for better IO performance and availability. On Physical servers, IBM Spectrum Copy Data Management SQL Server application-consistent copy creation ensures that appropriate consistency groups are automatically created to maintain consistency across all related volumes. IBM Spectrum Copy Data Management SQL Server backup on VM relies on VMware snapshots and doesn’t need to leverage storage consistency group feature.

What level of Application selection granularity is supported for SQL Server Backup jobs?

IBM Spectrum Copy Data Management SQL Server backup job definition supports copy selection at the following levels:

• One or more SQL Server Instances for Standalone SQL Server/Failover Cluster
• One or more Availability groups for AlwaysON
• One or more Databases for Standalone/Failover Cluster and SQL Server AlwaysON

Can I restore a database to an original instance and overwrite existing database in a single step?

Yes, use the Overwrite existing database option in the Application restore job definition.

Will IBM Spectrum Copy Data Management auto discover newly added SQL Server instances in a Standalone SQL Server and automatically protect it?

No. IBM Spectrum Copy Data Management will auto discover and present newly added SQL Server instances in the Backup job but you must explicitly select newly added SQL server instances for protection. Discovery of new SQL Server instances occurs as part of a regularly scheduled Application inventory job.

Will IBM Spectrum Copy Data Management auto discover newly added databases and automatically protect it?

Yes, if you select at Availability group level protection, IBM Spectrum Copy Data Management will auto discover newly added databases in selected availability group and protect it automatically during next job instance run. Discovery of new SQL Server instances and database occurs as part of regularly scheduled Application Inventory job.

Does IBM Spectrum Copy Data Management backup primary databases or secondary databases in SQL AlwaysOn?

IBM Spectrum Copy Data Management backs up only primary databases across the SQL AlwaysOn cluster.

Do SQL Server databases and logs need to be on supported storage for IBM Spectrum Copy Data Management CDM?

IBM Spectrum Copy Data Management also supports protection of SQL Server running on VMware VM configured on any storage that can be protected to supported storage systems via VM Replication. SQL Server running on physical servers require the database and logs to be on supported storage.

Does IBM Spectrum Copy Data Management perform full backups of databases?

IBM Spectrum Copy Data Management backups of SQL Server databases are always VSS COPY type backups.

SQL Server log management

Does IBM Spectrum Copy Data Management support Transaction log backup and log management?

Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log must be truncated on a regular basis to keep it from filling up.

IBM Spectrum Copy Data Management provides you with an option to back up transaction log files. IBM Spectrum Copy Data Management supports log backup at a specified frequency. You can select one or more databases for log backup in a single backup job definition. Log destination can be specified as a single universal mount point or separate destination mount point for each database. Specified log backup destination path must already exist and must reside on supported storage system. If multiple databases are selected for backup, then each of the servers hosting the database must have their Destination directory set individually.

Does IBM Spectrum Copy Data Management support truncation of database logs?

Yes, IBM Spectrum Copy Data Management will automatically truncate log post log backups of databases that it backs up. If database logs are not backed up with IBM Spectrum Copy Data Management, its logs are not truncated by IBM Spectrum Copy Data Management and must be managed separately.

Can I specify a retention period for backed up transaction logs?

No. Log backup retention and auto-deletion is planned for a future release.

I am backing up transaction logs with IBM Spectrum Copy Data Management, but I don’t want it to truncate logs. Can I control this behavior?

No. This will be enhanced in future release of IBM Spectrum Copy Data Management.

Pre and Post Scripts

Does IBM Spectrum Copy Data Management support pre/post scripts for Application Database Backup jobs?

Yes, IBM Spectrum Copy Data Management supports job-level pre/post scripts and job-level pre/post Snapshot scripts to enable further customization.

Job-level prescripts and postscripts are scripts that can be run before or after a job runs.

Snapshot prescripts and postscripts are scripts that can be run before or after a storage-based snapshot subpolicy runs. (Please refer to pre/post script topic in the IBM Spectrum Copy Data Management User’s Guide for details.)

Data masking

Does IBM Spectrum Copy Data Management SQL Server solution offer Data Masking integration with third party masking tools?

A concern for security officers in any organization is that of keeping confidential information locked down, even internally. Data masking is used to hide confidential data, by replacing it with fictitious data, when making data copies for DevTest or other use cases. It prevents leakage of sensitive data in non-production databases via static data masking [SDM], and production data in transit via dynamic data masking [DDM].

The following Data Masking integration features will be available in a future release.

IBM Spectrum Copy Data Management will include integrated data masking workflows with the ability to leverage third party masking tools. Traditionally, data masking is difficult, slow, and storage-consuming, but with IBM Spectrum Copy Data Management it will be easily integrated into the SQL Server backup workflow, allowing creation of masked copies at a specified frequency. Masked copies are automatically marked in the catalog. Access to secure copies is managed by the administrator by leveraging the application-level RBAC.

In addition, SQL Server will enable you to leverage the Dynamic Data Masking feature of SQL Server 2016.

Is sample masking script provided with IBM Spectrum Copy Data Management SQL Server solution?

A sample data masking script can be provided upon request. A sample masking script demonstrates data masking integration with built-in Dynamic Data masking script of SQL Server 2016. This feature will be available in a future release.

SQL Server Restore Workflow

Can I leverage SQL Server database clones for multiple use?

Limitations of current tools and approaches:

• Database cloning requires action by DBAs and is gated by process
• QA relies on DBAs for cloning the databases for functional testing
• The database cloning is gated by processes (space requisition, approvals, etc.)
• Database cloning is not time- or storage-efficient
• Usage of common cloning tools or custom scripts creates full copy requiring large amounts of additional storage
• Creating full copies is slow

IBM Spectrum Copy Data Management solves these challenges with simple, automated end-to-end clone lifecycle management:

• Self-service access to secure clones by QA team eliminates administrative and process bottlenecks
• IBM Spectrum Copy Data Management enables rapid database clones that are both time- and space-efficient
• Provision clones in minutes regardless of its size
• Leverages underlying storage snapshots for space efficiency
• IBM Spectrum Copy Data Management promotes standardization and governance through centralized catalog, granular RBAC, and automated policies

Your SQL Server clones can be utilized and consumed instantly – for whatever your use case -- through IBM Spectrum Copy Data Management “Instant Disk Restore” jobs. IBM Spectrum Copy Data Management catalogs and tracks all cloned instances. Instant Disk Restore can leverage iSCSI or FC protocol to provide immediate mount of LUNs without transferring data.

Can I create an Instant Clone of a SQL Server database for DevOps and Business Analytics?

IBM Spectrum Copy Data Management provides automated workflows to create instant clones of SQL Server database regardless of its size.

• Instantly create database clones from any of the copies in the IBM Spectrum Copy Data Management inventory, at local or remote locations, to accelerate Business Analytics.
• Enable and accelerate DevOps by providing Instant Disk Restore to secure clones of databases to appropriate users via application-level RBAC.

Then, when your TestDev, DevOps, or research/analytics work is completed, you can save the clone to more permanent storage or simply tear it down.

What is the granularity of database recovery supported by IBM Spectrum Copy Data Management?

Supported recoveries for standalone or AlwaysOn:

• Database can be recovered to point of snapshots to original or new instance (Instant Disk Restore)
• Database can be recovered to point in time leveraging backed up transaction logs (Instant recovery) to original or new instance
• Database can be recovered using new name to original or new instance
• You can select one or more databases in a single restore job definition.
• Each selected database in a Restore job definition can have separate destination specification
• Databases are always recovered in online mode
• Database can be recovered from standalone instance to AlwaysON Availability group
• Database from AlwaysON Availability can be recovered to standalone instance
• Database running on older version can be recovered to instance running same or newer version.

What granularity of Point in Time recovery is supported?

IBM Spectrum Copy Data Management enables database recovery to a specific point in time, allowing you to:

• Restore to the state just before the point of failure
• Restore multiple databases to a consistent time

Can I restore a Database to a Transaction Mark?

No. This will be enhanced in a future release of the product.

Does IBM Spectrum Copy Data Management support recovering a database in online mode?

Yes, Instant Disk Restore or Instant Database Restore recovers databases in online mode.

Does IBM Spectrum Copy Data Management support recovering databases in an offline state (norecovery)?

No. This will be enhanced in a future release of the product.

Does IBM Spectrum Copy Data Management support recovering databases in a standby/read-only state (standby)?

Yes, IBM Spectrum Copy Data Management provides an application option to control this behavior.

Roll back uncommitted transactions and leave the database ready to use

• Select this option to restore the database to an online state. If selected, additional transaction logs cannot be restored. If deselected, uncommitted transactions are not rolled back, leaving the database non-operational. Additional transaction logs can then be restored.

Does IBM Spectrum Copy Data Management support recovering database with Restricted Access?

No. This will be enhanced in a future release of the product.

Does IBM Spectrum Copy Data Management support restoring only logs so that they can be applied to a standby database?

No, not from the IBM Spectrum Copy Data Management application restore workflow. A user can easily access the transaction log backup location from the SQL server and perform this outside of the product.

Does IBM Spectrum Copy Data Management support out-of-place restore?

Out-of-place restore is used to relocate a database file to a new location:

• Copying/moving a database to a different location on a same SQL Server instance
• Copying a Database to a different SQL Server Instance at a different location

This feature will be enhanced in a future release of the product.

Where are SQL specific and IBM Spectrum Copy Data Management specific logs if errors occur?

All required logs (IBM Spectrum Copy Data Management and application) are collected as part of the current log collection functionality. There should be no need to manually obtain SQL application logs from within SQL Server VMs.

How do I refresh? How to promote to Production?

All database recovery operations can leverage Instant mode (Test) and then can either be deleted or promoted to permanent mode via workflow control. This behavior can be controlled via the Make Permanent job option.

• Enabled - Always make permanent
• Disabled - Never make permanent
• User election - Allows the user to select Make Permanent or Cleanup when the job session is pending

Does IBM Spectrum Copy Data Management use existing hardware providers for physical SQL backups?

No. IBM Spectrum Copy Data Management automatically deploys its own VSS HW provider service for SQL Server running on physical servers. It is automatically started on demand during SQL Server Backup jobs. At the completion of the backup job, the VSS HW provider service is automatically stopped.

When IBM Spectrum Copy Data Management protects a SQL VM with pRDM, can it restore a database back to the original node as a pRDM?

Currently, a SQL VM with pRDM must be registered as Physical in IBM Spectrum Copy Data Management. Hence, the restoration of that data obeys the Physical restore restrictions, which means it can only restore back to the original host via iSCSI. If the target host being restored to was registered as Virtual, then the database would be restored as a pRDM.

This functionality will be improved in future release.

Why must I choose a proxy node when performing a restore to a SQL Failover cluster?

Windows requires signatures to be unique, so when you attach a disk that has a signature equal to one that is already attached, Windows keeps the disk in “offline” mode and doesn’t read its partition table or mount its volumes. To prevent disk signature collision, during Instant Database Restore, IBM Spectrum Copy Data Management leverages Windows proxy servers to temporarily mount disks from snapshots, generate a new signature, then mount to original server.

Any Windows node with iSCSI or Fibre Channel access to the storage can be selected as a proxy server, provided that the node is not part of the original cluster. It is recommended to select a standalone virtual or physical Windows node as a proxy server.

Self Service

Does IBM Spectrum Copy Data Management support RBAC? What is the level of granularity supported for SQL Servers?

Role-based access control allows you to set the resources and permissions available to IBM Spectrum Copy Data Management accounts. Through role-based access control you can tailor IBM Spectrum Copy Data Management for individual users, giving them access to the features and providers they need.

Using IBM Spectrum Copy Data Management RBAC functionality, user can delegate IBM Spectrum Copy Data Management role to enable and accelerate DevOps by providing Instant Access to secure clones of databases to appropriate users via application-level RBAC. Then, when your TestDev, DevOps, or research/analytics work is completed, you can save the clone to more permanent storage or simply tear it down.

Can developers access IBM Spectrum Copy Data Management operations using command line or APIs?

A rich set of REST APIs are provided to enable full access to IBM Spectrum Copy Data Management functionalities for further customization.

System Requirements

What SQL Server versions are supported and on what Windows OS? What are supported Storage Systems for Microsoft SQL Server?

For the most recent requirements and support, see IBM Docs for the version of IBM Spectrum Copy Data Management that is deployed.

What are Environment and permission requirements for SQL Server solution?

Note the following Microsoft environmental requirements:

• Windows Remote Shell (WinRM) must be enabled
• The SQL user must enable the public and sysadmin SQL permissions.
• The user identity must have sufficient rights to install and start the IBM Spectrum Copy Data Management Tools Service on the virtual machine node. This includes "Log on as a service" rights. For more information about the "Log on as a service" right, see https://technet.microsoft.com/en-us/library/cc794944.aspx.
• The fully qualified domain name must be resolvable and route-able from the IBM Spectrum Copy Data Management appliance
• The virtual machine node DNS name must be resolvable and route-able from the IBM Spectrum Copy Data Management appliance
• The VMGuest version must be current
• VMware Tools must be installed on the virtual machine node

Does IBM Spectrum Copy Data Management support SQL Server 2016 running on Windows 2016?

Yes. See the matrix above.

Does IBM Spectrum Copy Data Management support SQL Server configured as Physical RDMs, or Independent disks?

Yes. See footnotes 4 and 6 in the matrix above.

Does IBM Spectrum Copy Data Management support SQL Server configured as Virtual RDMs?

Yes. For limitations see footnote 5 in the matrix above.

Does IBM Spectrum Copy Data Management support SQL Server running on physical machine(s)?

Yes. See the matrix above.

Are there additional requirements for SQL support in IBM Spectrum Copy Data Management?

SQL Support for VMware Virtual Machines

UUID must be enabled to perform Microsoft SQL-based backup functions. To enable, power off the guest machine through the vSphere client, then select the guest and click Edit Settings. Select Options, then General under the Advanced section. Select Configuration Parameters..., then find the disk.EnableUUID parameter. If set to FALSE, change the value to TRUE. If the parameter is not available, add it by clicking Add Row, set the value to TRUE, then power on the guest.

The virtual machine must use SCSI disks only, dynamic disks are not supported.

The latest VMware Tools must be installed on the virtual machine node.

In-Memory OLTP Requirements and Limitations

In-Memory OLTP is a memory-optimized database engine used to improve database application performance, supported in SQL 2014 and 2016. Note the following IBM Spectrum Copy Data Management requirements and limitations for In-Memory OLTP usage:

• The maximum restore file path must be less than 256 characters, which is a SQL requirement. If the original path exceeds this length, consider using a customized restore file path to reduce the length.
• The metadata that can be restored is subject to VSS and SQL restore capabilities.

SQL Server Failover Clustering Requirements for Windows Server 2008 R2

The Failover Cluster Manager Snap-In must be imported and configured before running IBM Spectrum Copy Data Management Backup and Restore jobs. To import, run Windows PowerShell in Windows Server 2008 R2 and enter the following command: import-module failoverclusters

For more information, see Microsoft.FailoverClusters.PowerShell.

Registration and Authentication

Register each SQL server as a provider in IBM Spectrum Copy Data Management by name or IP address. When registering a SQL Cluster (AlwaysOn), register each node by name or IP address. The fully qualified domain name and virtual machine node DNS name must be resolvable and route-able from the IBM Spectrum Copy Data Management appliance.

The user identity must have sufficient rights to install and start the IBM Spectrum Copy Data Management Tools Service on the node. This includes "Log on as a service" rights. For more information about the "Log on as a service" right, see Add the Log on as a service Right to an Account.

The default security policy uses the Windows NTLM protocol, and the user identity format follows the default domain\Name format.

Kerberos Requirements

Kerberos-based authentication can be enabled through a configuration file on the IBM Spectrum Copy Data Management appliance. This will override the default Windows NTLM protocol.

For Kerberos-based authentication only, the user identity must be specified in the username@FQDN format. The username must be able to authenticate using the registered password to obtain a ticket-granting ticket (TGT) from the key distribution center (KDC) on the domain specified by the fully qualified domain name.

Kerberos authentication also requires that the clock skew between the Domain Controller and the IBM Spectrum Copy Data Management appliance is less than 5 minutes. Note that the default Windows NTLM protocol is not time dependent.

Privileges

On the SQL server, the system login credential must have public and sysadmin permissions enabled, plus permission to access cluster resources in a SQL AlwaysOn environment. If one user account is used for all SQL functions, a Windows login must be enabled for the SQL server, with public and sysadmin permissions enabled.

Every SQL instance can use a specific user account to access the resources of that particular SQL instance.