Data Elasticity Through Database Sharding

Around late 2008, I came across my first real exposure to a customer who wanted to scale their read-write RDBMS.  It was a very interesting problem.  At the time I didn’t evaluate if RDBMS was an appropriate data store, but rather approached it from “let’s make this work”.  A complex PoC was pulled together that relied on a Peer-to-Peer Replication topology, that was super fragile.  Since then I have had several opportunities to work with customers chasing this objective.

In most cases the sought after distributed data store is achieved by leveraging a NoSQL system.  Of course that introduces other considerations.  At one point the team I was working on came up with this sharding pattern, which I’ve blogged about on at least one occasion, however it was predominantly about app and web server scale.  The data tier was simply, how do I say this succinctly, let me try “atrocious”.  It lacked real depth, and real versatility in terms of how it was to be cared for by DBAs, nor did it really consider the SDLC aspects of developers that needed an ELASTIC scale solution for the RDBMS tier.

I spent the next 2 years working on a framework that took the lessons from multiple engagements and placed into a consumable package.  It took a great deal of work, and several failures plus a group of excellent colleagues to produce the package.  In particular Silvano Coriani and Jeff Odell, with some great input in the early days by Josh Handel.  The Channel9 video presentation that can be found at http://bit.ly/Tju0aB, is where you get to see some good features of the package.  The articles I’m listing below is the outcome of many months of all that goes into content creation.  It is a great supplement to the video presentation.

Successfully achieving elastic scale is no trivial task, don’t be led to believe otherwise.  It is without a doubt the most fun, challenging and exciting journey with HUGE rewards.

Enjoy It!!

Advertisements
Posted in Scalability | Tagged , , , , | 1 Comment

Migrating Azure services to new regions

(originally posted on azure.microsoft.com http://bit.ly/1sSC5mW)

We recently carried out an engagement where a customer, needed to move their services from US South Central region to US East region data centers. The engagement included the processing of capacity planning an appropriate sized Premium SKU and leveraging Active Geo-Replication for High-Availability and Rollback.

The customer was looking to migrate their Azure services that reside in US South Central to US East region. The primary area required for assistance is around their database, where they need predictability of any potential downtime. The downtime should be as low as possible but can be no longer than 6 hours.

Customer Overview

The customer is a developer of a geosocial mobile game. The game has awareness of towns and cities across the world, with a presence in all but 2 African countries. Players primarily participate in terms of their physical geographical location

Planning

From a planning perspective we considered multiple options. These weren’t particularly long discussions, but were ideas and are worth taking note of as they are inevitably typical of such conversations.

  • How big is the database?
  • What is the current SKU used for the database?
  • How much downtime can you tolerate?
  • How much data loss can you tolerate, if any, during the migration?
  • How much money are you willing to spend?
  • Can you tolerate the use of recently published services and/or services that are still in preview?
  • What are the current concerns related to moving the data?
  • What are you not worried about?

For this customer it was most important to move. So we had quite a fair amount of latitude in terms of what we could do.

The database was approximately 35GB. About 5GB of the data could be considered as needing to be online while the remainder could be added over time that is it would essentially be offline.

The database was a business edition SKU and didn’t leverage any specific Azure features, such as Federations.

They could tolerate up to 8 hours downtime, but managing expectations of business and consumers was much easier if the downtime was less than 2 hours. Additionally certain days of the week and hours of the day were more amenable to downtime, therefore control of the downtime was a desired requirement.

The customer was cost conscious but was open to all ideas provided they included awareness of cost considerations.

The customer was very cautious of the DBCopy process and felt that along with export/import for moving large sets of data took much longer than 10hours, which severely impacted the business.

The moving of the compute nodes was not a concern, and neither was there an immediate need to move the existing blob storage data. The solution is architected such that no cross DC logic is required for accessing the existing blobs

Some options included:

  1. Leverage Active Geo-Replication functionality
  2. Stop all connections, export data, copy files, import into US East region
  3. Create DBCopy, Export data, copy files, restore in US East region

The Active Geo-Replication option was the preferred choice because:

  • Lowest downtime
  • No data loss, during planned migration
  • Rollback option is available
    • A failure in execution is a simple step of re-enabling resource at US South Central data center
  • Lowest TCO
  • Aligned with future plans (Upgrade to Premium SKU)

The one concern was that the service was in Preview, but this was considered a tolerable risk. After reviewing their historical performance, it was decided that a P1 would be sufficient for their needs. Some concerns that we had were whether the replication would be too resource intensive but given that the peak was understood to be one time event and the foreseeable resource requirement was low, the decision was to remain with P1 and be ready to upgrade to P2 at a moment’s notice.

As part of the capacity planning process, the customer was pointed to article Basic, Standard and Premium Preview for Azure SQL Database (http://msdn.microsoft.com/en-us/library/azure/dn369873.aspx).

Execution

The customer had to sign up for the Premium SKU preview. This must be done through the portal, and it takes seconds for the process to complete.

Once signed up, the database can be upgraded to Premium. Refer to “Changing Database Service Tiers and Performance Levels” (http://msdn.microsoft.com/en-us/library/dn369872.aspx) for details of what was leveraged for planning. The below formula was actually quite accurate, in predicting about 12 hours of time spent upgrading.

3 * (5 minutes + database size / 150 MB/minute)

The upgrade occurred smoothly. At one point several connections failed to connect, but this was for a brief moment and expected, as mentioned in MSDN article.

The next step was to create a replica in the desired region, namely US East region. There was uncertainty about how long it would take, and uncertainty about the resources required to carry out the replica. The steps are well documented as per this link “Failover in an Active Geo-Replication Configuration” (http://msdn.microsoft.com/en-us/library/azure/dn741337.aspx). The customer said it best when responding to a question of how long did the initial synchronization of the replica take…

It [Initial Synchronization] was no more than 40 minutes*.  I’m not sure on actual copy time.  This is quite amazing considering making a local bacpac file of this database takes 4 hours.

*Completely online experience

It should be noted that this execution plan was undertaken in-between code deployments. Much of this could’ve occurred within a 24hour period if process was purely technical. The customer chose to wait a couple of days before moving to the next action of the execution plan. The reasoning was related to making a significant code change to rather occur on the existing topology than in a new data center. They were concerned about whether the DDL changes would replicate. All changes are replicated except for those that rely on updating the master database, such as logins. These must be done in each environment, whereas creating a user can be done at the source. Refer to “Security Configuration for Active Geo-Replication” (http://msdn.microsoft.com/en-us/library/azure/dn741335.aspx) which specifically relates to the topic of logins.

The last step was to switch to the new region. Below are the steps that the customer shared for the benefit of others that undertake such a task. For terminating the Active Geo-Replication topology, the instructions are well-described as per link “Terminate a Continuous Copy Relationship” (http://msdn.microsoft.com/en-us/library/azure/dn741323.aspx).

  1. A week in advance, upgrade database to premium and setup geo replication to the East data center.  This could have been done the same day.  It took less than 40 minutes for a 32GB database to fully replicate from US South Central to East.
  2. [Day of move] Create blob containers in East to match the names of the containers in US South Central.  (we did not elect to move blob data at this time, but you would do that here)
  3. Deploy new Web and Worker roles to East.  Point new deployment at DB and blob storage in East.
  4. Setup any needed SSL certs in new (East) environment
  5. (Downtime begins)Take old Web and new Web offline line.  We do this with a configuration switch in the application that shows the users a friendly message that the site is under maintenance.  Only admins can get into the site/API normally.
  6. Change DNS to point to new web roles IP addresses
  7. Deploy new code to old data center (US South Central ) with connection strings for DB and Blob pointing at East
  8. Configure East DB to allow connections from US South Central IP Address (may not be required)
  9. Set new deployment (in staging area) in US South Central to offline mode, swap with production area
  10. Stop DB Active Geo-Replication (Planned Termination of a Continuous Copy Relationship)
    • Steps carried out using the portal
  11. Set US South Central DB into Read-Only mode
  12. Set US East region DB into read/write mode (after replication is completed) (may not be required)
  13. (Downtime ended) Re-enable both web sites/API in both US South Central and US East region data centers
  14. Setup automatic AzureDB backups on new DB.
    • Using the automated export under the “configure” section on the DB in the management portal.
  15. After DNS replication is fully propagated around the world, delete application servers in US South Central.

The following quote from the customer does a great job in articulating the experience

The move went very smoothly.  We were down for about 1 hour, and most of that time was waiting for our new code to deploy, configuration updates and prod/staging swaps.  The actual process of shutting down the Azure DB replication and changing read/write modes was simple.

Posted in Azure Migration | Tagged | Leave a comment

SQL Database Sharding Patterns

(originally posted at channel9 – http://bit.ly/Tju0aB)

Join Scott Klein and myself, as we discuss patterns and practices for sharding Azure SQL Database. The video session is a culmination of many hours of work to produce a framework / pattern that most industries could use to tackle the task of elastically scaling their RDBMS tier. Scott and I talk about some of the complexities of database sharding but introduce and demo a library that help overcome those complexities and provide the elastic scale needed in SQL Database. I highlight the key components and concepts when working with the sharding and then dive right into demoing the library to illustrate how the library can be used to implement sharding patterns into SQL Database. The sample library is available for customers to modify/extend/use and is compiled from engaging with customers who have taken on the task of creating customer sharding solutions.  While it is targeted towards Azure SQL Databases, the pattern is possible against on-premise and VM instances too.

//channel9.msdn.com/Shows/Data-Exposed/SqlDbShardingIntro/player?format=html5

Video | Posted on by | Tagged , | 1 Comment

DAL – Sharding of RDBMS

(originally posted at azure.microsoft.com/blog – http://bit.ly/1vnv3bB)

The “Cloud Service Fundamentals” application, referred to as “CSFundamentals,” demonstrates how to build database-backed Azure services.  This includes a description of the scenario, implementation architecture and reusable components for logging, configuration and data access.  The code base is intended to be a tangible exploration of best practices for delivering scalable, available services on Azure based on production deployments by the Windows Azure Customer Advisory Team.

With the majority of companies these days looking to move their cloud initiatives forward, the business drivers for specific solutions vary greatly, from lowering costs to significantly increasing agility and scale.  When solutions are trying to attain “cloud scale”, the strategy of “vertical scalability”, increasing capacity by upgrading the hardware, is replaced with “horizontal scalability”, increasing the number of machines that share a specific task.  A great example of this tradeoff is the creation of web farms where many servers serve the same web site content versus having a single monolithic machine try to handle the load.

Most people that start down this path plan for horizontal scalability on the compute nodes but bypass the more complex and potentially more critical state tiers, such as the relational database management system (RDBMS) and caches.  These services are often IO-intensive and bound by a single instance.  One technique to implement horizontal scalability in the state tier is known as sharding.  Sharding is when you logically separate your RDBMS data into multiple databases, typically with the same schema. For example, an employee table could be split across three distinct databases where each database stores a different department’s employees.

The benefits of sharding assists in far more than just capacity related scenarios.  For the purposes of this post we will focus on sharding an RDBMS that is implemented in Azure SQL Database platform, and primarily serves OLTP scenarios.  Some example scenarios that could benefit from a sharded database structure include:

  • Throttling thresholds or throughput limits are hit too often.
  • Size of the database becomes unwieldy (index rebuilds, backups).
  • A single unavailable database affects all users (as opposed to a single shard).
  • A database that has difficulty scaling up and down gracefully in respond to demand.
  • Certain business models, such as multi-tenant or Software as a Service offerings.

When using a multi-tenant database as a service solution, such as Windows Azure SQL Database, there are typically Quality of Service (QOS) controls put in place that throttle clients under various conditions. Throttling typically occurs when resource pressure climbs.  Sharding is a key strategy to help reduce resource pressure by taking the load that would typically affect a single server and spreading it across as multiple servers that each contain a shard. For example, assuming an even distribution, creating five shards reduces the load to approximately twenty percent on each database.

As with anything that grants greater power, there are sacrifices that must be made.  Sharding increases the complexity of several key areas, requiring more planning. These include:

  • Identity columns should be globally unique across all shards in case future business needs necessitate the reduction in shard count.  If the identity is not unique across all shards, merging two shards can result in conflicts.
  • Referential integrity cannot reference or enforce relationships to rows in other shards as they are independent databases.
  • Queries that cross shards should be avoided if possible, because they require querying each shard and merging the results.  The need to do “fan out” queries across the shards is not only costly from a performance point of view but increases the complexity of the sharding framework that must support it.  If cross-shard queries are necessary, the typical strategy is to query each shard asynchronously.  However, there are times where a synchronous approach offers more control of the resultset size.

In most cases, sharding is a Data Access Layer (DAL) concept, abstracting most of the intricacies from the higher-level application logic.

How you define a “tenant” is one of the most crucial decisions that you can make when building a sharded architecture.  A tenant is the largest unique categorization of data that is guaranteed to be on the same shard.  Queries that are constrained to a single tenant typically perform better as they do not need to execute fan-out operations during normal operational states.  Some of the factors that influence the decision of the appropriate tenant definition include:

  • The level of awareness of the higher-level application code of the identifier.
  • The ability of most core business transactions to be performed at this level.
  • The ability to avoid throttling in general day-to-day operations at the tenant granularity.

In an effort to surface these concepts and considerations at a high level, the Windows Azure Customer Advisory Team has built out a basic sharding Data Access Layer (DAL) in the Cloud Services Fundamentals (CSF) package (http://code.msdn.microsoft.com/Cloud-Service-Fundamentals-4ca72649).

In CSF the tenant is defined as an individual user.  Some of the factors that led to selecting this tenant were:

  • Most of the core business requirements do not require queries across multiple users.
  • An unavailable shard only impacts a defined set of users, leaving all others to continue their typical usage of the system.  The quantity of users that are on a single shard can be controlled to a number that is tolerable to a business.

The tenant is defined and implemented such that cross-database transactions are not required.  In Figure 1, we refer to this set of data as a shardlet, a Data Model Transaction Boundary.

Figure 1 – Data Model Transaction Boundary

When the user connects to the database for the first time in the session they have a series of simple queries that they can execute to understand if any features are unavailable to them due to offline shards.

In an effort to simplify the sharding techniques being demonstrated in CSF, we decided to create a set of shards that have enough storage to fulfill capacity needs for the foreseeable future. By selecting this size, it eliminated the need to demonstrate increasing and reducing the number of shards, which would also include actions like tenant movement.  An integer is generated by hashing the tenant’s name and this id is used to lookup a matching range in a “shard map”.  CSF uses a range-based mechanism, where a range of these numbers are assigned to a specific shard (captured in the “shard map”).

In the event that shards need to be added or removed from the shard set, it requires that tenants become unavailable until they are transitioned into their new shards. Due to this significant limitation, it is expected that the shard set will be significantly over provisioned when it is first created to reduce or eliminate the need to do complex shard management.

This solution requires that the Data Access Layer (DAL) is aware of the tenant id to determine the placement of the tenants in the shard set.  If a query is executed that includes a shard that is unavailable the entire query will fail.  In the event that the DAL does not include the tenant id all shards will have to be queried, increasing the chance of failure and reducing performance.

There is currently some preliminary work underway to offer further code samples demonstrating more advanced sharding techniques.  The sample will offer improvements in the following areas:

  • Reactive and proactive shard management.
  • Global uniqueness and identity management.
  • Migration of tenants between shards within the set.
  • Expansion and contraction of the shard set.
  • Improvements in queries that are not tenant aware.

In conclusion, the Cloud Services Fundamentals code sample is a great way to start to explore the basic concepts of sharding, which is an important technique for creating “cloud scale” applications.

Posted in Scalability | Tagged , | Leave a comment

Brokerage Cuts Manual Data Workloads by 50 Percent and Strengthens Customer Trust

(originally posted on microsoft.com/casestudies – http://bit.ly/Ltlz2M)

TradeStation provides historical stock-market data so that traders can test investment strategies. To support a massive planned increase in data volumes for an important customer offering, TradeStation needed to update a data management solution that required excessive maintenance work and lacked sufficient scalability. Based on extensive experience, the brokerage deployed a Microsoft solution, which further strengthens customer relationships and cuts manual workloads and processing overhead by half.

Business Needs

TradeStation offers traders analysis tools that include providing historical data against which strategies can be tested. It offers about 30 years’ worth of data for all major stock exchanges, and customers have access to a development interface where they can build and test computer-based buy and sell strategies.

“The SQL Server 2012 solution has reduced the IT department’s manual workloads by at least half” – Jose Ruiz, Quality Assurance Director, TradeStation

To support growth, further strengthen customer relationships, and expand into a new line of business, TradeStation needed to update the solution it was using to manage its historical trading data. One problem with the existing solution was that it did not support having one centralized, authoritative version of the data, so customers had to access copies on different machines. Occasionally, such as after power interruptions or operating system failures, these duplicated files diverged.

Updating these files was also a challenge. “Adding each new day’s data required rebuilding our files every night, which took anywhere from one to six hours,” says Joe Messina, a Senior Engineer for TradeStation. This high processing overhead reduced the company’s ability to deliver data promptly to customers.

In addition to correcting these shortcomings, TradeStation had another goal. “Currently, our system stores only a stock’s best asking price, best bid, and any actual trades,” says Messina. “Our plan is to begin offering access to market depth data, or every single asking price and bid for each stock. That’s going to expand our data volume by about a hundredfold, and it just wasn’t going to be possible with our old, homegrown system.”

TradeStation wanted a solution that could more easily manage binary large objects (BLOBs) and deliver data to customers from one central database. The solution needed to be scalable, easy to update, and simple to maintain.

Solution

TradeStation considered KDB and Vertica solutions but ultimately decided to deploy Microsoft SQL Server 2012 data management software. “We already use a lot of Microsoft technology elsewhere in our IT environment, and we’re very comfortable with it,” says Andrew James, a Product Manager for TradeStation. “This solution is going to be the foundation for the long-term growth of our company, so we wanted to go with a technology that has large teams of people expending all of their effort and brainpower making it even faster and better. That’s the reason we chose Microsoft.”

With SQL Server 2012, TradeStation can use the FILESTREAM feature to store BLOBs in its SQL Server database but still be able to access them from Windows applications as if they were stored in the file system. The database holds 4 terabytes of market data, and TradeStation receives about 175 gigabytes of new data per day. The new solution is deployed on HP ProLiant DL360 G7 server computers.

To simplify maintenance, speed data loading, and optimize query performance, TradeStation is using dynamic partitioning logic in SQL Server 2012 to create a new partition every 100,000 new files. SQL Server 2012 supports as many as 15,000 partitions to support large sliding-window scenarios that can streamline maintenance of extremely large data sets.

In the next phase of the deployment project, TradeStation plans to implement SQL Server 2012 Remote Blob Store (RBS) to further reduce strains on its storage and processing resources. With RBS, the company has the option to use commodity hardware for dedicated storage solutions and make use of a scalable architecture to increase throughput and grow to accommodate thousands of terabytes.

Benefits

The Microsoft solution cut manual workloads for IT and other employees, improved the company’s relationship with its customers, and reduced processing overhead.

Reduces Manual Workloads by 50 Percent

The new solution so simplified the data management infrastructure that it sharply reduced the time that TradeStation IT workers needed to spend on maintenance. “With SQL Server 2012, we’ve reduced the number of physical servers from 50 to 4, or 46 fewer machines that we need to worry about upgrading, managing, and tracking,” says Jose Ruiz, the Quality Assurance Director for TradeStation. “And because we now have one centralized set of data, we’ve cut out all the time we used to spend duplicating data sets from one machine to another. When you combine these improvements, the SQL Server 2012 solution has reduced the IT department’s manual workloads by at least half.”

The new solution is saving time for other departments, too. James says, “The SQL Server 2012 solution is reducing the number of trouble calls to our data-integrity department.” Keith Black, Vice President of Product Development at TradeStation, agrees: “SQL Server 2012 has removed a lot of pain and wasted time. That’s a big win for our employees.”

Further Strengthens Customers’ Trust

The solution’s centralized, authoritative data eliminates the inconsistencies that customers sometimes encountered previously. “Before, when our data was duplicated across so many machines, sometimes customers noticed differences between data sets that should have been identical,” says Ruiz. “By centralizing our data with SQL Server 2012, every customer is going to access the same data, no matter what server they log on to and get served from. That improved consistency is a huge gain for us and for our customers.”

Cuts Processing Overhead

By centralizing data storage, the new solution drastically reduces the amount of processing time needed to update or correct data. “By moving all of our data to SQL Server 2012, we no longer need to create multiple copies and rebuild the files each night, so we’ve dramatically reduced the length of time needed to process our end-of-day files or make corrections,” says Messina. “SQL Server 2012 is also helping us serve requested data to customers more promptly, which makes them happier.”

 

Posted in Case studies | Tagged | Leave a comment

Migrating Data-Centric Applications to Windows Azure

(originally posted at download.microsoft.com – http://bit.ly/1zBhQtA)
migrating

This guide provides experienced developers and information technology (IT) professionals with detailed guidance on how to migrate their data-centric applications to Windows Azure Cloud Services, while also providing an introduction on how to migrate those same applications to Windows Azure Virtual Machines. By using this guide, you will have the planning process, migration considerations, and prescriptive how to’s needed for a positive migration experience.

 

 

Part 1: Planning and Implementing a Migration

Overview of the Migration Life Cycle

Provides step-by-step instructions for migrating your applications and data to Windows Azure.

Planning a Migration

Provides a walk through on several concerns and steps you should consider as you plan a migration to Windows Azure.

Implementing the Migration Plan

Provides guidance on the final steps of migration to Windows Azure.

Part 2: Migration Considerations, Best Practices, and How To

Migrating with Windows Azure Virtual Machines

Provides an overview on the Windows Azure Virtual Machines (VM) and guidance on how you can migrate your existing SQL Server databases to Windows Azure using SQL Server in a Windows Azure Virtual Machine.

Migrating with Windows Azure Cloud Services

Provides an overview on the Windows Azure Cloud Services and guidance on how to migrate your existing applications and databases to Windows Azure using the functionalities provided by the Windows Azure Cloud Services platform.

Posted in Azure Migration | Tagged | Leave a comment

Successfully execute an INSERT, UPDATE and DELETE against a Database Snapshot

(originally posted at blogs.msdn.com/b/sqlcat http://bit.ly/1BL788I)

Reviewers: Mike Ruthruff, Sanjay Mishra, Alexei Khalyako

Not too long ago an ISV that developed solutions using SQL Server as the RDBMS, asked me how they could query a database as at a point in time. This was a relatively easy answer, thanks to the Database Snapshot feature. I was however surprised at the next question “Can we update the database snapshot?”

A reactive response is “No. You cannot update a Database Snapshot”

Msg 3906, Level 16, State 1, Line 1
 Failed to update database "Orig_Snapshot" because the database is read-only.

A creative answer is a tentative “….well maybe…depending on what the objective is?”

This blog demonstrates that it is possible to run an INSERT, UPDATE or DELETE against a Database Snapshot.  This will not update the snapshot, but rather the database that has a “Database Snapshot” associated to it.

Imagine a scenario where a reconciliation of data at a point in time must be carried out. Database Snapshot provides the ability to present the data as at a point in time, however the common understanding is that any compensating modifications requires a second connection or a USE statement. The USE statement is not permitted in a database module:

Msg 154, Level 15, State 1, Procedure testSP, Line 4
a USE database statement is not allowed in a procedure, function or trigger.

Listing 1 creates the objects needed to prove it is possible to successfully execute DML statements against a Database Snapshot.

    IF DB_ID('Orig') IS NOT NULL
        DROP DATABASE [Orig]
    GO
    
    CREATE DATABASE [Orig]
     ON PRIMARY ( NAME = N'Orig', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL02\MSSQL\DATA\Orig.mdf')
    GO
    USE [Orig]
    GO
  
    IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
        DROP TABLE dbo.TestTable
    GO
    
    CREATE TABLE dbo.TestTable (Col1 int)
    GO
    
    IF OBJECT_ID('dbo.UpdView', 'V') IS NOT NULL
        DROP VIEW dbo.UpdView
    GO
    
    CREATE VIEW dbo.UpdView
    AS
    SELECT Col1 FROM Orig.dbo.TestTable
    GO
    
    INSERT INTO dbo.TestTable (Col1) VALUES (1)
    GO
    
    IF DB_ID('Orig_Snapshot') IS NOT NULL
        DROP DATABASE [Orig_Snapshot]
    GO
    
    CREATE DATABASE [Orig_Snapshot]
     ON PRIMARY ( NAME = N'Orig', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL02\MSSQL\DATA\Orig_Snapshot.ss')
     AS SNAPSHOT OF [Orig]
    GO
    
    USE [Orig_Snapshot]
    GO
    
    SELECT * FROM dbo.UpdView
    GO
After running the above code, you should have a database and an accompanying snapshot of that database. Within the database, you’ll have a table with at least 1 row in it and a view that simply returns the contents of that table.  This view is a fully qualified name of the original database, this creates 2 scenarios.  One is that it becomes possible to view the originating database from within the snapshot, and the second is the ability to run DML statements against the source database from within the database snapshot.

Listing 2

    INSERT INTO dbo.UpdView VALUES (2), (3);
    UPDATE dbo.UpdView SET Col1 = 99 WHERE Col1 = 3;
    DELETE FROM dbo.UpdView WHERE Col1 = 1;
    SELECT * FROM dbo.UpdView
    GO

Running the code in listing 2, you should have a result set as shown below:

In conclusion, the error message that informs us the database is read-only, while identical to the message returned when writing to a Read-Only database, is only partially accurate in the context of a Database Snapshot. Questions that come to mind are:

  • Is it a bug?
  • Would this be considered a good practice?
  • What does the code management look like?
  • What useful scenarios could leverage this insight?

Let me know if you think it’s a bug. From my perspective, this makes sense as the database snapshot is actually a read/write database, persisting older values as records change. Additionally, we are not actually updating the Database Snapshot, the changes are still made directly to the main database.

As far as been a good or recommended practice, my reservations about using this in a design are:

  • Views must be created for each table that can be updated from the database snapshot
  • Separate objects/statements must be created for DML operations that already exists for the main table
  • It isn’t intuitive in terms of troubleshooting code. In fact, if you start down this road you may end up with one of those horrendous applications that have layers upon layers of views ultimately leading to poor performance.

It does however reduce the cost of creating and managing a new connection, especially if the DML statements are a result of a query that originated from the snapshot. Additionally, the logic could reside in the same SP as the query, allowing for conditional logic.

Can you think of other scenarios, pitfalls or benefits of updating the main database via a database snapshot?

 

Posted in Thoughts | Tagged , , , , , , , | Leave a comment