Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


SQL Server 2005 Bug Alert: Merge Replication Could Mean Data Loss : Page 2

The SQL Server 2005 merge replication engine introduced breaking changes that can cause data loss for both MSDE and SQL Server Express subscribers. Find out how this bug manifests itself in a real-world scenario.




Application Security Testing: An Integral Part of DevOps

Steps to Reproduce the Data Loss
The following steps walk you through reproducing the SQL Server 2005 data loss and are interjected with real-world examples from the Brangelina assistant scenario:
  1. Create the publication database. Find the script for creating the publication database in the code download.
  2. Create the tables inside the database. This script is also provided in the code download. The database's design is simplistic (see Figure 1) and suitable only for demonstration purposes.

    Click to enlarge

    Figure 1. The Simplistic Database Design Diagram:
    This database design is suitable only for demonstration purposes.

  3. Insert data for the tests. (At this point, the orders are created blank with an OrderStatus=0, not completed.) In the example scenario, this is done when the assistant enters new orders on her Tablet PC:

    DECLARE @userid int INSERT INTO [MergeRep_Test].[dbo].[User] ([FirstName] ,[LastName] ,[Username] ,[Department]) VALUES ('Brad' ,'Pitt' ,'bxxx' ,'Ocean13') SET @userid= SCOPE_IDENTITY(); DECLARE @orderdetailid int INSERT INTO [MergeRep_Test].[dbo].[OrderDetail] ([CustomerId] ,[ProductListId] ,[SpecialConditionId]) VALUES (null ,null ,null) SET @orderdetailid=SCOPE_IDENTITY(); INSERT INTO [MergeRep_Test].[dbo].[Order] ([OrderStatus] ,[Description] ,[CompletionDate] ,[OrderDetailId] ,[UserId]) VALUES (0 , 'Order some pizza for the kids' ,getdate() ,@orderdetailid ,@userid)

  4. Create a publication that publishes all the tables in the database, and maintain SQL Server 2000 compatibility levels to test this scenario with MSDE. (Use the create publication scripts from the code download.) The Brangelina family's savvy DBA created the publication on the family's SQL Server 2005.

    The row filtering should look like you see in Figure 2 (The snapshot agent security is chosen to run with the SQL Server Agent account in this case).

    Click to enlarge

    Figure 2. Row Filtering at the Publication:
    This publication publishes all the tables in the database.

  5. Now it is time to run the snapshot agent. Create the subscriber database and the subscription (see the scripts in code download). The subscription is set to run the agent at each subscriber (pull subscription), and the merge agent security is "By impersonating the process account." The merge agent is located at the subscriber and should run on demand only.

    The Brangelina family's database developer creates the subscriber database on the SQL Server Express instance running on the assistant's Tablet PC and creates the subscription. The assistant takes her Tablet PC from the developer and connects it to the house's network. She launches the replication process through her client application so she can review the orders the family needs for the day.

  6. Replicate using Windows XP Synchronize (see Figure 3 and Figure 4):

    Click to enlarge
    Figure 3. Windows XP Synchronization Tool:
    This is the Windows XP Synchronization Tool interface for replication.
      Click to enlarge
    Figure 4. The First Synchronization Is Complete:
    This is the Windows XP Synchronization Tool interface for a successful synchronization.

    The assistant now reviews the orders on her client application and changes the status to "Open" ( OrderStatus=1). The data she now has on the subscriber looks like you see in Figure 5. She then rushes to the closest supermarket to buy the merchandise.

    Click to enlarge

    Figure 5. Data on the Assistant's Tablet PC:
    This is the data sitting on the assistant's Tablet PC after the first synchronization.

    While she fulfills the first order, she updates both the Order and the OrderDetail tables. Finding suitable organic pizza can be a very time-consuming task, but she finally managed to find an Italian shop that uses organic flour, cheese, tomatoes, and ham. She buys the pizza there and marks her order as complete.

  7. Run the update script on the subscriber database. (See the scripts in the code download.) The assistant's local data looks like you see in Figure 6 and Figure 7 after she completes her update.

    Click to enlarge
    Figure 6. Assistant's Order Table:
    The assistant's Order table looks like this after her update.
      Click to enlarge
    Figure 7. Assistant's OrderDetail Table:
    The assistant's OrderDetail table looks like this after her update.

    The publisher database at Brangelina's house looks like you see in Figure 8 and Figure 9.

    Click to enlarge
    Figure 8. Order Table on Brangelina's Publisher Database:
    The Order table looks like this on the Brangelina publisher database.
      Click to enlarge
    Figure 9. OrderDetail Table on Brangelina's Publisher Database:
    The OrderDetail table looks like this on the Brangelina publisher database.

    The assistant now wants to inform Brad Pitt that the first order (order some pizza for the kids) has been fulfilled. She synchronizes her client application with the publication database, to which Brad has select access. (This over-simplified scenario uses Windows XP synchronization again.) The client application will not notify the assistant of these conflicts (after all, the example is set to have the server win in every situation).

  8. Replicate again (see Figure 10).

    Click to enlarge

    Figure 10. The Windows XP Synchronization Tool Shows Conflicts:
    These conflicts are not recorded for Microsoft SQL Server Desktop Engine subscribers.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date