Introduction
In this article I will show you, how we can achieve merge replication by web synchronization in SQL Express.Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.
In above diagram you can see data will replicate between publisher and there subscribers.
Merge replication is implemented by the SQL Server Snapshot Agent and Merge Agent. If the publication is unfiltered or uses static filters, the Snapshot Agent creates a single snapshot. If the publication uses parametrized filters, the Snapshot Agent creates a snapshot for each partition of data. The Merge Agent applies the initial snapshots to the Subscribers. It also merges incremental data changes that occurred at the Publisher or Subscribers after the initial snapshot was created, and detects and resolves any conflicts according to rules you configure.
The following diagram shows the components used in merge replication.
Subscribe to Publications
A subscription is a
request for a copy of the data and database objects in a publication. A
subscription defines which publication will be received, and where and when it
will be received. When planning for subscriptions, consider where you want
agent processing to occur. The type of subscription you choose controls where
the agent runs. With a push subscription, the Merge Agent or Distribution Agent
runs at the Distributor, whereas with a pull subscription, agents run at the
Subscribers. After a subscription is created, it cannot be changed from one
type to another.
Subscription
|
Characteristics
|
Use When
|
Push Subscription
|
With a push subscription, the Publisher
propagates changes to a Subscriber without a request from the Subscriber.
Changes can be pushed to Subscribers on demand, continuously, or on a
scheduled basis. The Distribution Agent or Merge Agent runs at the Distributor.
|
·
Data will typically be synchronized continuously or on a
frequently recurring schedule.
·
Publications require near real-time movement of data.
·
The higher processor overhead at the Distributor does not
affect performance.
·
Most often used with snapshot and transaction replication.
|
Pull Subscription
|
With a pull subscription, the Subscriber
requests changes made at the Publisher. Pull subscriptions allow the user at
the Subscriber to determine when the data changes are synchronized. The
Distribution Agent or the Merge Agent runs at the Subscriber.
|
·
Data will typically be synchronized on demand or on a schedule
rather than continuously.
·
The publication has a large number of Subscribers, and/or it
would be too resource-intensive to run all the agents at the Distributor.
·
Subscribers are autonomous, disconnected, and/or mobile.
Subscribers will determine when they will connect and synchronize changes.
·
Most often used with merge replication.
|
Web Synchronization for Merge Replication With Pull Subscription
When Web synchronization is used, updates at the Subscriber are packaged and sent as an XML message to the computer that is running IIS by using the HTTPS protocol. The computer that is running IIS then sends the commands to the Publisher in a binary format, typically by using TCP/IP. Updates at the Publisher are sent to the computer that is running IIS and then packaged as an XML message for delivery to the Subscriber.The following illustration shows some of the components that are involved in Web synchronization for merge replication.
The SQL Server Replication Listener (Replisapi.dll) is configured on the computer that is running IIS and is responsible for handling messages that are sent to the server from the Publisher and Subscribers. Each node in the topology handles the XML data stream by using the Merge Replication Reconciler (Replrec.dll).
Now I will explain how to Web Synchronization With Pull Subscription For SQL Express.
First I have to create new publisher for SQL server and map this publisher to IIS web site which will be consumed by subscriber.
As we know SQL Express has no SQL Server Agent so we need create a agent which can get data from publisher.We can create that agent by replication management objects(RMO Programming).
As we know SQL Express has no SQL Server Agent so we need create a agent which can get data from publisher.We can create that agent by replication management objects(RMO Programming).
When we have multiple stores and want to replicate data between stores by the help of publisher. We can achieve this by Web Synchronization.
Now I will show below things to you.
- How configure IIS for SQL publisher
- Create subscriber by RMO Programming
- Synchronization data by RMO Programming.
How configure IIS
These below steps for configure the IIS for web synchronization
1. First of all you have open sql server run as administrator
2. Login with SQL credentials
3. Right click on publisher select configure web synchronizations
4. After click on web synchronizations click on next you have two option
- Create new virtual directory
- Use existing virtual directory
Checked Allow basic authentication
Add user for virtual directory access.
5. Click on next your web synchronization configuration has been done. You can see you website in IIS 7
6. Click on SQLReplication virtual directory double click on handler mappings and add new module mapping.
7. Click on ok. Register C:\inetpub\wwwroot\SQLReplication\replisapi.dll by following command
8. Created new self-signed certificate for web synchronization
9. Add created certificate with web synchronization web site.
10. Export the certificate by run above command and install in server and client sql server.
11. Run https://win-d838rz3d8ed/SQLReplication/replisapi.dll?diag on your machine you will get following screen
12. If you are getting error in window server which is running in 64-bit must run following command in command prompt.
cscript %SystemDrive%\inetpub\AdminScripts\adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1
Create subscriber by RMO Programming
We need to write below code for created subscriber agent for SQL express by help of RMO. You have to write below code.
public static void CreatePullSubcription()
{
// Define the Publisher, publication, and databases.
string publicationName =”pawnmasterPublication”;
string publisherName = “WIN-D838RZ3D8ED”;
string publisherUid = "deepakj";
string publisherPass ="123";
string publicationDbName = "demo";
string subscriberName = @"WIN-D838RZ3D8ED\SQLEXPRESS";
string subscriberUid = "deepakj";
string subscriberPass = "123";
string subscriptionDbName = "demo";
string hostname =@"WIN-D838RZ3D8ED\deepakj";
string winLogin = @"WIN-D838RZ3D8ED\deepakj";
string winPassword = "123";
string webSyncUrl = "https:// win-d838rz3d8ed/ SQLReplication/replisapi.dll";
//Create the Subscriber connection.
ServerConnection conn = new ServerConnection(new SqlConnection(sqlconnectionstring));
// Create the objects that we need.
MergePullSubscription subscription;
try
{
// Connect to the Subscriber.
conn.Connect();
// Define the pull subscription.
subscription = new MergePullSubscription();
subscription.ConnectionContext = conn;
subscription.PublisherName = publisherName;
subscription.PublicationName = publicationName;
subscription.PublicationDBName = publicationDbName;
subscription.DatabaseName = subscriptionDbName;
subscription.HostName = hostname;
// Specify an anonymous Subscriber type since we can't
// register at the Publisher with a direct connection.
subscription.SubscriberType = MergeSubscriberType.Anonymous;
// Specify the Windows login credentials for the Merge Agent job.
subscription.SynchronizationAgentProcessSecurity.Login = winLogin;
subscription.SynchronizationAgentProcessSecurity.Password = winPassword;
// Enable Web synchronization.
subscription.UseWebSynchronization = true;
subscription.InternetUrl = webSyncUrl;
// Specify the same Windows credentials to use when connecting to the
// Web server using HTTPS Basic Authentication.
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication;
subscription.InternetLogin = winLogin;
subscription.InternetPassword = winPassword;
// Ensure that we create a job for this subscription.
subscription.CreateSyncAgentByDefault = true;
// Create the pull subscription at the Subscriber.
subscription.Create();
}
catch (Exception ex)
{
// Implement the appropriate error handling here.
throw new ApplicationException(String.Format(
"The subscription to {0} could not be created.", publicationName), ex);
}
finally
{
conn.Disconnect();
}
}
Synchronization data by RMO Programming
We have to write a code for pull the data from publisher to subscriber database. Below code will help to us run the pull agent in subscriber machine.
We have to write a code for pull the data from publisher to subscriber database. Below code will help to us run the pull agent in subscriber machine.
public static void RunPullAgent()
{
// Define the Publisher, publication, and databases.
string publicationName =”pawnmasterPublication”;
string publisherName = “WIN-D838RZ3D8ED”;
string publisherUid = "deepakj";
string publisherPass ="123";
string publicationDbName = "demo";
string subscriberName = @"WIN-D838RZ3D8ED\SQLEXPRESS";
string subscriberUid = "deepakj";
string subscriberPass = "123";
string subscriptionDbName = "demo";
string hostname =@"WIN-D838RZ3D8ED\deepakj";
string winLogin = @"WIN-D838RZ3D8ED\deepakj";
string winPassword = "123";
string webSyncUrl = "https:// win-d838rz3d8ed/ SQLReplication/replisapi.dll";
// Create a connection to the Subscriber.
ServerConnection conn = new ServerConnection(new SqlConnection(sqlconnectionstring));
MergePullSubscription subscription;
MergeSynchronizationAgent agent;
try
{
// Connect to the Subscriber.
conn.Connect();
// Define the pull subscription.
subscription = new MergePullSubscription();
subscription.ConnectionContext = conn;
subscription.DatabaseName = subscriptionDbName;
subscription.PublisherName = publisherName;
subscription.PublicationDBName = publicationDbName;
subscription.PublicationName = publicationName;
// If the pull subscription exists, then start the synchronization.
if (subscription.LoadProperties())
{
// Get the agent for the subscription.
agent = subscription.SynchronizationAgent;
// Check that we have enough metadata to start the agent.
if (agent.PublisherSecurityMode == null)
{
// Set the required properties that could not be returned
// from the MSsubscription_properties table.
agent.PublisherSecurityMode = SecurityMode.Integrated;
agent.DistributorSecurityMode = SecurityMode.Standard;
agent.DistributorLogin = publisher.UserId;
agent.DistributorPassword = publisher.Password;
agent.Distributor = publisherName;
agent.HostName = hostname;
// Set optional Web synchronization properties.
agent.UseWebSynchronization = true;
agent.InternetUrl = webSyncUrl;
agent.InternetSecurityMode = SecurityMode.Standard;
agent.InternetLogin = winLogin;
agent.InternetPassword = winPassword;
}
// Enable agent output to the console.
agent.OutputVerboseLevel = 4;
agent.Output = subscriber.LogFilePath;
// Synchronously start the Merge Agent for the subscription.
agent.Synchronize();
}
else
{
// Do something here if the pull subscription does not exist.
throw new ApplicationException(String.Format(
"A subscription to '{0}' does not exist on {1}",
publicationName, subscriberName));
}
}
catch (Exception ex)
{
// Implement appropriate error handling here.
throw new ApplicationException("The subscription could not be " +
"synchronized. Verify that the subscription has " +
"been defined correctly.", ex);
}
finally
{
conn.Disconnect();
}
}
Enjoy the web synchronization
Nice Article.......
ReplyDeleteI was looking for this.
Great Job Mr Deepak
ReplyDeleteThanks kalyan
DeleteHi Deepak..
ReplyDeletenice work budy..
keepit up
thanks for appreciation :)
Deletevery nice article deepak.
ReplyDeleteThank you Cheers :)
DeleteSo's it... You've chosen to venture out into the pool of social media.best smm panel
ReplyDeleteThank you ☺️
DeleteThank you ☺️
DeletePretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. download
ReplyDeleteThank you Cheers :)
DeleteI read that Post and got it fine and informative. useless website generator
ReplyDeletePositive site, where did u come up with the information on this posting? I'm pleased I discovered it though, ill be checking back soon to find out what additional posts you include. sviluppo siti web Milano
ReplyDeleteThank you :)
DeleteAttractive, post. I just stumbled upon your weblog and wanted to say that I have liked browsing your blog posts. After all, I will surely subscribe to your feed, and I hope you will write again soon! how to get hired as a freelance writer
ReplyDeleteIt is somewhat fantastic, and yet check out the advice at this treat. New web series
ReplyDeleteOne significant manner by which you can expand the advantage of utilizing a social systems administration site, is to assemble connections.smm panel
ReplyDeleteThese days, a large portion of the web fashioners are equipped for offering productive specialized and graphical web plans to the web locales. Website laten maken
ReplyDeleteOn this subject internet page, you'll see my best information, be sure to look over this level of detail. more about the author
ReplyDeleteImmaterial connections, and the more you have the more adverse it is, features to Google the unimportance of your site - paying little heed to how significant it very well may be. https://www.webdesign-seo-limburg.be/
ReplyDeleteThese modules remember TutuApp web for request to get to the gadget capacities like contacts, accelerometer, camera, and then some.
ReplyDeleteMy partner and i adored up to you will acquire performed below. The particular draw will be tasteful, the written subject https://tipandroid.com/ matter elegant.
ReplyDeletelaptop stands will help you to keep laptop at its own position
ReplyDeleteWebsite design is not merely creating sensation but also creating new standard in this discipline, which eventually giving hard time to the full player around the world. https://www.sandeepmehta.co.in/affordable-seo-services-delhi/
ReplyDeleteEach web designs made by the web designer ought to be reduced.
ReplyDeletehttps://onohosting.com/
The inquiry is, which "secret" picture is naturally chosen for web-based media and Facebook? have a peek at these guys
ReplyDeleteyour content is very inspiring and appriciating I really like it please visit my site for Satta King Result also check Satta king 24X7 and also check sattaking and for quick result check my site Satta matka and for super fast result check Satta king
ReplyDeleteWhat the digital demands visit here of ATSC (The USA And Also Canada) as well as likewise DVB (Rest Of The World) used was the ability to reuse the existing analog variety a lot more effectively. This suggested a routine 8 MHz provider utilized for analog program could be changed to DVB-T (Digital Video - Terrestrial) making it practical to haul 9 normal definition networks or 3 HD networks plus one SD network for the exact same amount of information transfer.
ReplyDeletethank you for sharing very informative information on web synchronization for merge
ReplyDeletehttps://thewebgross.com/seo-services-delhi-india/
Battering, this is hanging as you really want to find extra, I welcome to This is my page. dark0de market link
ReplyDeleteadvantages of setting up a Facebook business page for private venture (with him obviously) and selling on Facebook. temp edu email
ReplyDeleteBest wallpaper site in the world: https://cutewallpaper.org/
ReplyDeleteThank you sharing very informative information and check this best Digital Marketing Services at Siliguri
ReplyDeleteWith the most effective odds in any on line casino, video poker 먹튀사이트 먹튀프렌즈 will give you the most effective successful alternatives. Online video poker rapidly became admirable amongst on-line gamblers. The component of skill and risk of holding some cards to higher your hand made video poker an-industry-dynamite. Steadily, bodily casinos started dropping video poker shoppers.
ReplyDeleteTantalizingly, however not realistically, one may hope that a quick glance at the spinning rotor may spur a stable guess and an auspicious placement of chips. This roulette wheel features the 1xbet usual black and purple numbers and includes a ball. Place the green felt cowl down and you immediately have your individual gaming desk. Also included are betting chips and a set arm, supplying you with every little thing want to|you should|you have to} throw the proper on line casino party or Vegas-themed party. Close-up white on line casino roulette with slot machine, chips and cube.
ReplyDelete