Merge Replication between SQL 2000 or SQL 2005 using SQLCE

• Download MERGE__REPLICATION.zip - 1,165.6 KB

Introduction

This article will give you a step by step instruction on how to configure a Merge replication using SQL Server 2000 and 2005 and SQL CE.

Background

If you want to get an idea how to create replication read Pitfalls with Merge Replication in a SQL 2000 Environment. If you all want learn how to publish read SQL Server 2000 - Merge Replication Step by Step Procedure.

Step by Step Procedure for Merge Replication Setup

You can see these steps with pictures in downloadable file.

STEP I

1. Create New User
2. Go to start->Control Panel ->Administrative tools ->Computer management



3. Local users and groups ->right click ->select new user



4. Press Create

STEP II

1. Go to C directory and create a new folder as shown below



2. Name it as snapshot.
3. Right  Click  the folder and select sharing and security



4. Select share this folder and enter the name as PDA
5. Click permission and give full control. Clicks apply then click ok

STEP III

1. Download SQL SERVER 2005 Replication components from Microsoft by clicking this site
2. http://www.microsoft.com/downloads/details.aspx?FamilyID=6ed0fb7e-7c05-4f59-879a-8fb619e36612&displaylang=en
3. Click the Sqlce30setupen.msi in C:\



4. Click Next



5. Select I accept the terms in the license agreement





Click Install



6. We can see window like this



7. Click Finish

STEP IV

1. Go to start ->programs ->sql 2005 mobile edition ->configure Web Synchronization wizard



2. Click Next



3. Select SQL mobile edition and click Next



4. Select Create  new Virtual directory  and select default website ,then click Next



5. Enter PDA and select the snapshot folder (here C:\snapshot)  (share name of the snapfolder as in step …..)



6. It you are the running first time it will show a message box saying that “The folder does not exist,do you want the folder created?” press Yes
7. Another message box will popup saying that this folder
8. “Does not contain copy of the SQL Mobile Server Agent. Do you want to copy and register the sql mobile server agent?” press YES
9. Secure Communication select do not require a secure channel



10. Client Authentication
11. Select Clients will connect anonymously



12. Anonymous Access



13. Click Change 



14. Click Advanced and click Find Now



15. Select the user which you created in STEP I from the list and click Ok
16. Snapshot Share Access



17. Click Next and if it showing a warning saying that “The snapshot share is empty. Do you want to continue?”. Click Yes
18. Click Finish
19. 
20. Last Screen
21. 

STEP V

1. Open Internet Explorer and enter http://localhost/PDA/sqlcesa30.dll



If you can see the same in your internet explorer, so the IIS we configures successfully.

STEP VI

1. Go to Security



2. Select sqldbalocal from the list as shown below and click Add, then press ok



3. Under Server Role Tab



4. In Database Access Tab select the same as shown below

STEP VIII

1. Configuring Replication



2. Click Next





3. Click Next and click OK for the warning message



4. Press Ok



5. Press Ok



6. Click Ok and enter the name of the snapshot folder



7. Select Your database which you need to replicat from the list below
8. Select Merge Replication and click Next



9. Select Device Running SQL Server 2000 and SQL CE, then click Next



10. Select Tables which you want to replicate and click the box selected at the end for each table



11. Select All tables specified Below and do the same process specified above
12. Click Next and enter the publication name



13. Select No Create the publication specified
14. Next screen click Finish



15. Click Publication Properties



16. Select publication Access list

STEP IX

1. After all this configuration you need to create one POCKET PC application and paste this code

Using the Code

You can see one XML file in the downloadable file you need to change the file according to your configuration. After that add a class file in to your PDA application, copy this lines of codes in to the class file.

//

Imports System.Data.SqlServerCe

Imports System.Data.SqlServerCe.SqlCeException

Imports System.Xml

Imports System.Reflection

Imports System.io

Imports System.text

Imports System.Data

Imports System.Net

Public Class DatabaseConfig

#Region "Variables"
 Private Shared boolConfigLoaded = False

Private Shared strDatabaseServer As String

Private Shared strPublisherDB As String

Private Shared strPublisher As String

Private Shared strPublisherLogin As String

Private Shared strPublisherPassword As String

Private Shared strSubscriberConnectionString As String

Private Shared strSubscriber As String

Private Shared strSqlCeUrl As String

Private Shared strLocalDBLocation As String

Private Shared strLocalDBName As String

Private Shared strIISLogin As String

Private Shared strIISPassword As String

 

#End Region

#Region "Properties"
 Private Property ConfigLoaded() As Boolean

Get

Return boolConfigLoaded

End Get

Set(ByVal Value As Boolean)

boolConfigLoaded = Value

End Set

End Property

Public Property DatabaseServer() As String

Get

' Check to make sure config has been loaded

If Not ConfigLoaded Then

LoadDBConfigSettings()

End If

Return strDatabaseServer

End Get

Set(ByVal Value As String)

strDatabaseServer = Value

End Set

End Property

Public Property PublisherDB() As String

Get

' Check to make sure config has been loaded

If Not ConfigLoaded Then

LoadDBConfigSettings()

End If

Return strPublisherDB

End Get

Set(ByVal Value As String)

strPublisherDB = Value

End Set

End Property

Public Property Publisher() As String

Get

' Check to make sure config has been loaded

If Not ConfigLoaded Then

LoadDBConfigSettings()

End If

Return strPublisher

End Get

Set(ByVal Value As String)

strPublisher = Value

End Set

End Property

Public Property PublisherLogin() As String

Get

' Check to make sure config has been loaded

If Not ConfigLoaded Then

LoadDBConfigSettings()

End If

Return strPublisherLogin

End Get

Set(ByVal Value As String)

strPublisherLogin = Value

End Set

End Property

Public Property PublisherPassword() As String

Get

' Check to make sure config has been loaded

If Not ConfigLoaded Then

LoadDBConfigSettings()

End If

Return strPublisherPassword

End Get

Set(ByVal Value As String)

strPublisherPassword = Value

End Set

End Property

Public Property SubscriberConnectionString() As String

Get

' Check to make sure config has been loaded

If Not ConfigLoaded Then

LoadDBConfigSettings()

End If

Return "data source=" & AppWorkingDir & "\" & strLocalDBName

End Get

Set(ByVal Value As String)

strSubscriberConnectionString = Value

End Set

End Property

Public Property SqlCeUrl() As String

Get

' Check to make sure config has been loaded

If Not ConfigLoaded Then

LoadDBConfigSettings()

End If

Return strSqlCeUrl

End Get

Set(ByVal Value As String)

strSqlCeUrl = Value

End Set

End Property

Public Property LocalDBLocation() As String

Get

' Check to make sure config has been loaded

If Not ConfigLoaded Then

LoadDBConfigSettings()

End If

Return AppWorkingDir & "\" & LocalDBName

End Get

Set(ByVal Value As String)

strLocalDBLocation = Value

End Set

End Property

Public Property LocalDBName() As String

Get

' Check to make sure config has been loaded

If Not ConfigLoaded Then

LoadDBConfigSettings()

End If

Return strLocalDBName

End Get

Set(ByVal Value As String)

strLocalDBName = Value

End Set

End Property

Public ReadOnly Property AppWorkingDir() As String

Get

Return Path.GetDirectoryName([Assembly].GetExecutingAssembly.GetName.CodeBase)

End Get

End Property

#End Region

Private Sub LoadDBConfigSettings()

Dim strElementName As String = String.Empty

'Dim strAppWorkingDir As String = Path.GetDirectoryName([Assembly].GetExecutingAssembly.GetName.CodeBase)

'Dim strAppWorkingDir As String = "\Program Files\LATESTSYNC\"

' Get the configuration data from the config XML file

'Dim ConfigFile As New IO.FileStream("\Program Files\LATESTSYNC\SyncInfo.xml", IO.FileMode.Open)

 

'Dim strAppWorkingDir As String = Path.GetDirectoryName([Assembly].GetExecutingAssembly.GetName.CodeBase)

' Get the configuration data from the config XML file

Dim ConfigFile As New IO.FileStream(AppWorkingDir & "\syncinfo.xml", IO.FileMode.Open)

' MsgBox(Path.GetFullPath("My Documents\SyncInfo.xml"))

 

Dim ConfigReader As New Xml.XmlTextReader(ConfigFile)

ConfigReader.WhitespaceHandling = WhitespaceHandling.Significant

While ConfigReader.Read

If ConfigReader.NodeType = XmlNodeType.Element Then


strElementName = ConfigReader.Name

ElseIf ConfigReader.NodeType = XmlNodeType.Text Then

' Use the last element name read to determine what value we're looking at

Select Case strElementName

Case "DatabaseServer"

strDatabaseServer = ConfigReader.Value

Case "DatabaseName"

strPublisherDB = ConfigReader.Value

Case "DatabasePub"

strPublisher = ConfigReader.Value

Case "DatabaseLogin"

strPublisherLogin = ConfigReader.Value

Case "DatabasePassword"

strPublisherPassword = ConfigReader.Value

Case "SQLCEURL"

strSqlCeUrl = ConfigReader.Value

Case "LocalDBConnect"

strSubscriberConnectionString = ConfigReader.Value

Case "LocalDBLocation"

strLocalDBLocation = ConfigReader.Value

Case "LocalDBName"

strLocalDBName = ConfigReader.Value

End Select

End If

End While


ConfigReader.Close()

ConfigFile.Close()


ConfigLoaded = True

End Sub

End Class

Public Class DBAccess

Dim oDBConfig As New DatabaseConfig

 

Private Sub ReplicateData()

Try

Dim replicator As New SqlCeReplication

replicator.InternetUrl = oDBConfig.SqlCeUrl

replicator.Publisher = oDBConfig.DatabaseServer

replicator.PublisherDatabase = oDBConfig.PublisherDB

replicator.PublisherSecurityMode = SecurityType.NTAuthentication

replicator.Publication = oDBConfig.Publisher

replicator.InternetLogin = "Administrator"

replicator.InternetPassword = "password"

replicator.Subscriber = Dns.GetHostName()

replicator.SubscriberConnectionString = oDBConfig.SubscriberConnectionString

replicator.Synchronize()

MsgBox("Data Synchronized", , "THPMIS - PDA")

Catch ex As SqlCeException

MsgBox(ex.Message, MsgBoxStyle.Critical, "THPMIS - PDA")

End Try

End Sub

Public Sub CreateDB()

Try

If File.Exists(oDBConfig.LocalDBLocation) = False Then

Dim engine As New System.Data.SqlServerCe.SqlCeEngine(oDBConfig.SubscriberConnectionString)

engine.CreateDatabase()

ReplicateData()

engine.Dispose()

Else

ReplicateData()

End If

Catch ex As Exception

MsgBox(ex.Message)

Finally

Cursor.Current = Cursors.Default

End Try

End Sub

End Class
//

Points of Interest

This is one of most intresting part in the PDA developement using SQL CE where you can easily transfer the SQL 2000 or 2005 database to your HAND HELD device. This is my first article on www.codeproject.com. Hope you all enjoy this ...