Despite all the hoopla around cloud computing, Microsoft's SQL Azure cloud platform will likely co-exist with on-premises SQL Server installations for some time. The hybrid deployment scenario means integrating data will present challenges to developers, especially since much of the technology is still in flux.
Rather than flip the switch on a wholesale conversion to the cloud, most companies will move applications over to SQL Azure at a more measured pace, initially spinning up applications best positioned to reap the benefits of the cloud's scalability and lower costs. Companies may choose to work through some of their cloud security and performance concerns by using SQL Azure to experiment with new applications or by adding features and data sets that live in the cloud to existing SQL Server systems. Both approaches demand a robust level of integration between the platforms.
"Larger companies and ISVs [independent software vendors] already have SQL Server, so they're looking at SQL Azure as an extension for their existing set of capabilities," said Herve Roggero, managing partner at Boca Raton, Fla.-based Blue Syntax Consulting, a company that focuses on SQL Azure deployments. "They're going to need integration so they can use the best of both worlds. The new [cloud] services are going to have to work seamlessly with the existing infrastructure."
For more on integrating data
Want more on integrating data between SQL Server and MySQL?
In this new hybrid world, there are two likely integration scenarios: Companies will store the same data sets on both SQL Azure and on-premises SQL Server, or they’ll maintain different data on the separate platforms, with the capability to readily exchange information between the two. For example, using SQL Azure as a backup resource for an essential on-site SQL Server application, such as financials, would require hosting the same data on both platforms.
In comparison, companies in industries like health care or finance, which deal with regulatory restrictions, might want a hybrid system on which they can maintain sensitive data on-premises in SQL Server, yet interact with applications and different data sets and applications in the SQL Azure cloud.
"I see co-existence as a real possibility," said Jeff Mlakar, head database architect in Ernst & Young's internal applications department. "There are a lot of reasons people will have a hybrid solution because of regulatory restrictions like PCI [Payment Card Industry Data Security Standard] and HIPAA [Health Insurance Portability and Accountability Act]. I don't think Ernst & Young will ever go completely to the cloud because of the restrictions of where data has to reside in particular country borders."
Workarounds and integration tools
How can developers address this robust level of integration between on-premises SQL Server and SQL Azure? While there are several options, the technologies are evolving. On one hand, SQL Server developers can create workarounds so the transactional work is handled in the application, instructing data to be written to both on-premises SQL Server instances and to SQL Azure in the cloud.
It’s certainly effective, but this approach puts a lot of extra work on the developers’ shoulders, requiring them to double up on programming. It also raises performance concerns related to moving much more data over Internet connections, which are not always up to snuff in terms of transmission speeds.
Microsoft, for its part, isn't letting developers grapple with the integration issue on their own. SQL Server Integration Services (SSIS), a platform for building enterprise-level data integration and extract, transform and load services could be used to create data flows between on-premises SQL Server and SQL Azure. However, there are limitations with SSIS: It's not well-suited for synchronization, because it doesn't support two-way data flow, and it isn't prepared to handle conflict detection and resolution in the event the data exchange is interrupted because of a cloud failure.
The Microsoft offering better positioned to address the integration issue is SQL Azure Data Sync, a tool specifically designed for sharing data between on-premises locations and one or more SQL Azure databases (see “Microsoft gets SQL Server, Azure in sync”). While developers have high hopes for Data Sync's ability to address their integration needs, the tool is still only available as a community technical preview, which means it's in beta testing; thus questions remain over pricing and the final feature set.
"It's like making a decision in the dark right now," said Ike Ellis, a SQL consultant and trainer who carries Microsoft’s Most Valued Professional distinction.
Ellis also cautioned that you don't just deploy Data Sync and call it a day. "It does solve the challenge, but you still have to manage it," he said. "It's not fire and forget—you still have to think about whether it's running, did it break, does it support fast enough links so it can do the job. Also, if there are schema changes, you may have to set it up again."
Looking ahead, Ellis said integrating data will be far less an issue as the cloud gains traction and companies get over any lingering security and compliance concerns. "Eventually, companies will resolve their concerns about the cloud and everything will be on the cloud," he said. "It's just inevitable."
Microsoft gets SQL Server, Azure in sync
Microsoft has two primary tools for integrating its on-premises SQL Server platform and its cloud-based SQL Azure. Here's how company officials differentiate between them:
SQL Server Integration Services (SSIS): Microsoft positions this tool as a platform for building enterprise-level data integration and extract, transform and load (ETL) systems. The primary audience for SSIS is developers, so the onus is on them to continuously update the system to support ongoing incremental changes and track and extract updated data sources. As a result, Microsoft said SSIS is better situated for use in ETL scenarios as opposed to being the go-to tool for cloud and on-premises synchronization.
SQL Azure Data Sync: Still in beta testing, this technology synchronizes data movement between SQL Azure and SQL Server databases. While Data Sync doesn't offer the same breadth of functionality and flexibility as the developer-focused SSIS, it is optimized for full two-way sync between multiple databases and as such, includes capabilities for conflict detection and resolution. Data Sync's main use case scenarios are for enabling hybrid on-premises and cloud applications to share data and for aggregating data in SQL Azure.
Beth Stackpole is a freelance writer who has been covering the intersection of technology and business for 25-plus years for a variety of trade and business publications and websites.
Dig Deeper on SQL Server Migration Strategies and Planning
Microsoft technology refresh touches SQL Server, integration tooling
Microsoft SSIS (SQL Server Integration Services)
SQL Server 2016 Integration Services boosted by Linux support
Microsoft SSIS addresses data integration and data migration functions