Software Architecture – Sync Data between offline-Disconnected databases –Sync Framework

October 3, 2010

Problem definition:ERP Product is installed in head office and 5 branch offices such that each location installation has its on database. Locations are connected via Internet that is they are acting as diconnected databases.
We want to sync databases of different branches.
Solution: As discussed in previous post at here
Some possible solutions to problem are as:
• Replication
• ETL
• Sync Providors
• Sync Framework
• Change Data Capture
In this post we will follow Sync Framework to solve this problem.
According to MSDN:

Database synchronization providers are a part of the Microsoft® Sync Framework. Sync Framework is a comprehensive synchronization platform that enables developers to add synchronization capabilities to applications, services, and devices. Sync Framework solves the problem of how to synchronize any type of data in any store using any protocol over any topology. Fundamental to Sync Framework is the ability to support offline and collaboration of data between any types of endpoints (such as device to desktop, device to server, etc.).
Sync Framework database synchronization providers enable synchronization between ADO.NET-enabled databases. Since the database synchronization providers are part of the Sync Framework, any database that uses these providers can then also exchange information with other data sources that are supported by Sync Framework, such as web services, file systems, or custom data stores.
The primary focus of this document will be on synchronizing information between database systems and how Sync Framework helps developers avoid many of the common issues associated with OCAs.

Download Sync Framework 2.1 and install it. Add references to the following DLLs:
o Microsoft.Synchronization.dll
o Microsoft.Synchronization.Data.dll
o Microsoft.Synchronization.Data.SqlServer.dll

Lets consider Employeetable in 2 databases DB1 and DB2, which needs to be synchronized.
L ets declare few global variables as

  static string  SourceSqlConnection = @"Data Source=SOFTCOM-PC\XGURU;Initial Catalog=DB1;Integrated Security=True";
       static string TargetSqlConnection = @"Data Source=SOFTCOM-PC\XGURU;Initial Catalog=DB2;Integrated Security=True";
       static string SourceScopeName = "MySourceScop";
       static string TargetScopeName = "MyTargetScop";
       static string TableToSync = "Employee";

Variable SourceSqlConnection represents the connection string of first database that will take part in sync process. Variable TargetSqlConnection represents the connection string of second database that will take part in sync process. Variable SourceScopeName and TargetScopeName contains name of scope for the two databases. Scope defines what you want to synchronize. Normally tables are added to scope which needs to be sync. Variable TableToSync contain the name of table that will be synced.
Step 1:
Defining Scope and Provisioning for Source SQL Server Database can be done via following method.

 private static void ProvisionSource()
        {
            // create connection to the Source server database
            SqlConnection serverConn = new SqlConnection(SourceSqlConnection);
 
            // define a new scope named MySourceScope
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(SourceScopeName);
 
            // get the description of the GeneralAreaMaster table from SyncDB dtabase
            DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(TableToSync, serverConn);
 
            // add the table description to the sync scope definition
            scopeDesc.Tables.Add(tableDesc);
 
            // create a server scope provisioning object based on the ProductScope
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
 
            // skipping the creation of table since table already exists on server
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
 
            // start the provisioning process
            try
            {
                serverProvision.Apply();
            }
            catch { }
        }

Step 2:
Defining Scope and Provisioning for Target SQL Server Database can be done via following method.

private static void ProvisionTarget()
        {
            // create connection to the server database
            SqlConnection serverConn = new SqlConnection(TargetSqlConnection);
 
            // define a new scope named MyTargetScope
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(TargetScopeName);
 
            // get the description of the GeneralAreaMaster table from SyncDB dtabase
            DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(TableToSync, serverConn);
            // add the table description to the sync scope definition
            scopeDesc.Tables.Add(tableDesc);
 
            // create a server scope provisioning object based on the ProductScope
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
 
            // skipping the creation of table since table already exists on server
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
 
            // start the provisioning process
            try
            {
                serverProvision.Apply();
            }
            catch { }
        }

Step 3:
Synchronization between the 2 databases can be done via following code.

private static void SyncSourcewAndTargetDatabases()
        {
            // create a connection to the Source database
            SqlConnection clientConn = new SqlConnection(SourceSqlConnection);
 
            // create a connection to the Target server database
            SqlConnection serverConn = new SqlConnection(TargetSqlConnection);
 
            // create the sync orhcestrator
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();
 
            // set local provider of orchestrator to a CE sync provider associated with the 
            // ProductsScope in the SyncCompactDB compact client database
            syncOrchestrator.LocalProvider = new SqlSyncProvider(SourceScopeName, clientConn);
 
            // set the remote provider of orchestrator to a server sync provider associated with
            // the ProductsScope in the SyncDB server database
            syncOrchestrator.RemoteProvider = new SqlSyncProvider(TargetScopeName, serverConn);
 
            // set the direction of sync session to Upload and Download
            syncOrchestrator.Direction = SyncDirectionOrder.DownloadAndUpload;
 
            // subscribe for errors that occur when applying changes to the client
            ((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(Program_ApplyChangeFailed);
 
            // execute the synchronization process
            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();
 
            // print statistics
            Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
            Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);
            Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
            Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
            Console.WriteLine(String.Empty);
            Console.ReadLine();
        }
 
        static void Program_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
        {
            // display conflict type            Console.WriteLine(e.Conflict.Type);
 
            // display error message 
            Console.WriteLine(e.Error);
        }

This code will sync the data between 2 databases.
For advance cases. See documentation.

Advertisements

Software Architecture – Sync Data between offline/Disconnected databases – SSIS – BIDS

September 18, 2010

Problem definition:
ERP Product is installed in head office and 5 branch offices such that each location installation has its on database. Locations are not connected via Internet that is they are acting as diconnected databases.

We want to sync databases of different branches.

Solution:
Some possible solutions to problem are as:
• Replication
• ETL
• Sync Providors
• Sync Framework
• Change Data Capture
We will compare and analyze pros and corns of these method in separate post. But here we will use ETL approach to solve this problem.
At closing of each working day, data is fetched from each branch database and new data is posted to other branches and head office database Server via email. So each database read email and executes the database differences from other databases.
So we can break whole process in 4 programming Steps.
1. Extract data from source database and convert them into a text file with data/time stamp attached.
[Implemented as SSIS Package and run via C# Code]

For details see post here

2. Read files from a folder in step 1, and send them as attachment in email via Outlook.
[Implemented by interop of MS Outlook API]

For details see post here

3. On other side, emails are pulled automatically via outlook and attachments are downloaded and saved in a local folder in hard disk.
[Implemented by interop of MS Outlook API]
For details see post here

4. Text files s are read from folder and loaded in to database.
[Implemented as SSIS Package and run via C# Code]

For details see post here

Future consideration:

1. How to identify the differential of data between the daily move of data.
2. This solution is working for one table how to handle more tables for data sync process.
3. There are many hardcoded parameters that needs to be fetched from configuration files.

Source: download from here Rename the file to ssis-solution-toupload.zip to extract.
Feedback is welocme


Software Architecture – To Load the data from files of a folder to table of SQL Server via SSIS in SQL Server Business Intelligence Development Studio

September 18, 2010

Objective:
Objective of this post is to load the data from all the files in a folder to a database of SQL Server via SSIS in SQL Server Business Intelligence Development Studio 2008 .

Environment:
SSIS + SQL Server Business Intelligence Development Studio 2008 + C# 4.0

Steps:

1. Create a new Package named “LoadData.dtsx” in BIDS 2008.
2. Create a new variable with name “FileName”.
3. Drag and drop a “Foreach loop container” to SSIS designer.
Set the Enumerator type and configuration settings as shown below.

Set “variable mapping” as shown below.

4. Drag a data flow task on SSIS Designer.
5. On the Data Flow tab, drag and drop the “Flat File Source” from the Data Flow Source in the Data Flow Tool Box. Click on New to create the New Flat File Source Connection Manager. Uses a property expression on the ConnectionString property of a Flat File Connection Manager to point to the file indicated by User::FileName

6. On the Data Flow tab, drag and drop the “OLE DB Designation” from the Data Flow Designation in the Data Flow Tool Box. Create the new OLE DB Designation connection manager by clicking on “New” button on the OLE DB Designation Editor.
Configuration will look like as:

7. So at the end of these changes, Control Flow will look like as:

And Data Flow will look like as:

8. Run the Package from Solution explorer to see the output.
9. Package can be run by following c# code.

string pkgLocation = @"F:\\e-pros\\testbest\\SSISFirst\\SSISFirst\\SSISFirst\\ExtractData.dtsx"; 
 
        Package pkg; 
        Microsoft.SqlServer.Dts.Runtime.Application app; 
        DTSExecResult pkgResults; 
        Variables vars; 
 
        app = new Microsoft.SqlServer.Dts.Runtime.Application(); 
        pkg = app.LoadPackage(pkgLocation, null); 
 
      //  vars = pkg.Variables; 
      //  vars["A_Variable"].Value = "Some value";                
 
        pkgResults = pkg.Execute(null, null, null, null, null);
 
        if (pkgResults == DTSExecResult.Success)
        {
            Console.WriteLine("Package ran successfully");
 
        }
        else
            Console.WriteLine("Package failed"); 

Related Post: https://softarchitect.wordpress.com/2010/09/18/software-architecture-sync-data-between-offlinedisconnected-databases-ssis-bids/


Software Architecture – To extract the data from a table of SQL Server and convert it to text file via SSIS in SQL Server Business Intelligence Development Studio

September 18, 2010

Objective:
Objective of this post is to extract the data from a table of SQL Server and convert it to text file having name as datetime appended via SSIS in SQL Server Business Intelligence Development Studio 2008 .

Environment:
SSIS + SQL Server Business Intelligence Development Studio 2008 + C# 4.0
Steps:

1. Create a new Package named “ExtractData.dtsx” in BIDS 2008.
2. Create a new variable with name “FileLocation” and set its value to folder name, lets say “F:\FileInbox”
3. Append variable “FileLocation” value with datetime stamp by adding a “Script Task” as:
Dts.Variables[“User::FileLocation”].Value = Dts.Variables[“User::FileLocation”].Value.ToString() + “\\data_” + DateTime.Now.ToString(“MMddyyyy_hhmmss”) ;
This command will define the name of data file to be generated.
4. Drag a data flow task on SSIS Designer.
5. On the Data Flow tab, drag and drop the “OLE DB Source” from the Data Flow Sources in the Data Flow Tool Box. Create the new OLE DB source connection manager by clicking on “New” button on the OLE DB Source Editor. Specify the command that will fetch the data from the table as:

SELECT TOP 1000 [AreaId]
,[AreaCode]
,[Description]
,[Active]
,[CreatedDate]
,[ModifiedDate]
,[BranchId]
FROM [GeneralAreaMaster]

6. On the Data Flow tab, drag and drop the “Flat File Destination” from the Data Flow Destinations in the Data Flow Tool Box. Click on New to create the New Flat File Destination Connection Manager. Configure properties accordingly.
7. So at the end of these changes, Control Flow will look like as:

And Data Flow will look like as:

8. Run the Package from Solution explorer to see the output.
9. Package can be run by following c# code.

string pkgLocation = @"F:\\e-pros\\testbest\\SSISFirst\\SSISFirst\\SSISFirst\\ExtractData.dtsx"; 
 
        Package pkg; 
        Microsoft.SqlServer.Dts.Runtime.Application app; 
        DTSExecResult pkgResults; 
        Variables vars; 
 
        app = new Microsoft.SqlServer.Dts.Runtime.Application(); 
        pkg = app.LoadPackage(pkgLocation, null); 
 
      //  vars = pkg.Variables; 
      //  vars["A_Variable"].Value = "Some value";                
 
        pkgResults = pkg.Execute(null, null, null, null, null);
 
        if (pkgResults == DTSExecResult.Success)
        {
            Console.WriteLine("Package ran successfully");
 
        }
        else
            Console.WriteLine("Package failed"); 

Related Post: https://softarchitect.wordpress.com/2010/09/18/software-architecture-sync-data-between-offlinedisconnected-databases-ssis-bids/


Software Architecture – Replication Limitations of SQL Server Express Edition

August 19, 2010

Environment:
SQL Server Express Edition 2008 R2
Problem:
Can SQL Server Express Edition be used in replication?
Solution:
Let’s suppose a typical replication case. Publisher, Distributor and Subscriber are the main elements as shown below.

• SQL Server Express Edition can act a subscriber to a replication process, means that it can receive data in replication.
• SQL Server Express Edition can’t act a publisher/Distributor to a replication process, means that it can’t deliver data in replication.
• So a non Express Edition of SQL Server should be used to act as publisher and in such case Express Edition SQL Server can get data as subscriber.
Reference:
http://msdn.microsoft.com/en-us/library/ms165686.aspx


Software Architecture – Exception -Windows Vista + SQL Server Express edition- Failed to compile the Managed Object Format (MOF) file

August 10, 2010

Environment:
Windows Vista + SQL Server Express edition
Exception:

SQL Server Setup Failed to compile the Managed Object Format (MOF) file ***.mof. To proceed, see “Troubleshooting an Installation of SQL Server 2005” or “How to: View SQL Server 2005 Setup Log Files” in SQL Server 2005 Setup Help documentation.

Actual error will look like as:

Solution:
The problem comes from corruption in the WMI repository, and it can be repaired easily. From a command prompt, running as administrator, run:
winmgmt /salvagerepository
You’ll get an error, but the command has sent a stop request to the WMI service. Run the command a second time, and it will repair the repository and the SQL Server installation can continue.

Reference:
http://msdn.microsoft.com/en-us/library/aa394525(VS.85).aspx


Software Architecture – How to Install Microsoft SQL Server Express Edition with Installer Project and deploy database backup

August 5, 2010

Development Note

Problem definition:
SQL Server Express Edition needs to be installed with Visual studio .Net installer project, so that silent installation of SQL Server Express edition can be performed.

Solution Points:
• Create a new Setup and Deployment project
• Put all the files extracted from SQL Server Express Edition exe in application folder of installer.
• Add Backup file to be restored.
• Check in code project sample,there will be code to check IsDone and IsRestart, left both of this checking as it is, since it is the condition that Installation class left for us to tell the main Startup Form what to do, whether to close the application or to show the form.
• When you first run your application, it checks if the database instance exists, if not it will install the database instance, and restore the backup database to the new database instance.

References:
http://msdn.microsoft.com/en-us/library/bb264562(SQL.90).aspx
http://www.codeproject.com/KB/applications/NET_Installer_With_SQLEXP.aspx
http://www.primordialcode.com/blog/post/install-sql-server-express-with-your-application