June 29, 2017 at 12:00 am
Hi All,
I have a requirement to sync local db data with central SQL server. The remote users (mostly around 10 people) will be using laptop which will host application and local db. The internet connection is not 24x7. During no connectivity, the laptop user should be able to make changes in local db and once the connection is restored, the data should be synced with central SQL server automatically. The sync is just usually data updates. I have looked at options Sync frame work and Merge replication. I can’t use sync frame work as I am not C# expert. For Merge replication, additional hardware is required I believe which is not possible. The solution should be easy to develop and maintain.
Are there any other options available ? Is it possible to use SSIS in this scenario ?
Thanks in advance!!
June 29, 2017 at 5:44 am
I think the Sync Framework would be the normal way to approach this.
You could look at using a third party data compare tool that can be scripted. It might require a lot of playing to make this approach robust.
eg http://www.xsql.com/products/sql_server_data_compare/?utm_source=pragmatic&utm_medium=articles&utm_campaign=xsql
June 29, 2017 at 6:06 am
You could also look at using triggers with Service Broker.
Again, conflict resolution might take some work to be robust.
June 29, 2017 at 6:11 am
Thanks a lot for your valuable inputs Ken. Don't you think Merge will also take care of intermittent internet at subscriber and it also has conflict resolution.
June 29, 2017 at 6:15 am
there's a couple of layers to work at here.
for LocalDB to be accessible to anyone other than the user that created it, it has to be started and you have to share it as well. it normally only exists in the context of the current user.
I use LocalDB all the time for testing deployment scripts, but i have not played with sharing. so easy to instantly drop and recreate what looks like an entire express instance
Have you successfully shared the localdb with another account yet? and also accessed it from another machine?
I was under the impression that sharing let another user on the same machine/laptop access that shared instance; I think you'd have to install SQLExpress in order to do what you are asking.
If you switch to using express, and you enable TCP/IP(which is disabled by default on an express)
then you could have an SSIS package pull the data from the laptop easily.
then that package could be scheduled as a job, and just have the job NOT report an error if there was a connectivity error. run it every hour, and take into consideration the usual merge scenario for other data sources
I specifically tried to connect from another machine to a LocalDB, but have not had any luck yet.
--command prompt, and it required Administrative Privileges in order to share:
SqlLocalDB.exe start MSSQLLocalDB
-- i am mydomain\lowell, need to share with another user,
SqlLocalDB.exe share mydomain\sqlserviceaccount MSSQLLocalDB
/*--results
C:\Windows\system32>SqlLocalDB.exe share mydomain\sqlserviceaccount MSSQLLocalDB
Private LocalDB instance "mydomain\sqlserviceaccount" shared with the shared name: "MSSQLLocalDB".
C:\Windows\system32>
*/
Lowell
June 29, 2017 at 6:30 am
Hi Lowell,
Thanks for your reply. The local db is not sharable. Each user will have his own local db and that needs to synced with central sql server. I forgot to mention, the local db and the data transfer to central server must be encrypted. Is that possible with SSIS ?
June 29, 2017 at 6:53 am
if the localdb is not sharable, then the local instance has to push data to the central server, instead of the central server pulling from the laptop.
so an SSIS package would need to reside on the laptop, is that what you want? then that package could be scheduled via Windows Scheduled Tasks i guess.
for a conenctionstring to be encrypted, it's easy peasey, you just add a little something tot eh connecito string:
Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;
Trusted_Connection=yes;Encrypt=yes;
For the local database to be encrypted, that is a design issue. instead of having columns that are varchar(30) for example, they need to be varbinary(max), and then some process needs to encrypt the values, and insert the encrypted binary into the columns. typically you encrypt sensitive columns only(personally Identifiable info, credit card info, etc), and never the "whole" table; that makes it unsearchable.
an ugly, poor example would be how this table converts to the encryption-capable table below...but you have to encrypt and decrypt values to and from that table.IF OBJECT_ID('[dbo].[Customer]') IS NOT NULL
DROP TABLE [dbo].[Customer]
GO
CREATE TABLE [dbo].[Customer] (
[Customer ID] INT IDENTITY(1,1) NOT NULL,
[Customer Name] varbinary(max) NULL,
[Entity Type Description] VARCHAR(50) NULL,
[Customer Address ID] VARCHAR(50) NULL,
[Customer Address Type] VARCHAR(50) NULL,
[Customer Address Effective Date] DATETIME NULL,
[Customer Address County] VARCHAR(50) NULL,
[Customer Address line 1] VARCHAR(50) NULL,
[Customer Address Line 2] VARCHAR(50) NULL,
[Customer Address Line 3] VARCHAR(50) NULL,
[Customer Address City] VARCHAR(50) NULL,
[Customer Address State] VARCHAR(50) NULL,
[Customer Address Zip Code] VARCHAR(50) NULL,
CONSTRAINT [PK__Customer__9CF1F3A854186807] PRIMARY KEY CLUSTERED ([Customer ID] asc) )
IF OBJECT_ID('[dbo].[Customer]') IS NOT NULL
DROP TABLE [dbo].[Customer]
GO
CREATE TABLE [dbo].[Customer] (
[Customer ID] INT IDENTITY(1,1) NOT NULL,
[Customer Name] varbinary(max) NULL,
[Entity Type Description] VARCHAR(50) NULL,
[Customer Address ID] VARCHAR(50) NULL,
[Customer Address Type] VARCHAR(50) NULL,
[Customer Address Effective Date] DATETIME NULL,
[Customer Address County] varbinary(max) NULL,
[Customer Address line 1] varbinary(max) NULL,
[Customer Address Line 2] varbinary(max) NULL,
[Customer Address Line 3] varbinary(max) NULL,
[Customer Address City] varbinary(max) NULL,
[Customer Address State] varbinary(max) NULL,
[Customer Address Zip Code] varbinary(max) NULL,
CONSTRAINT [PK__Customer__9CF1F3A854186807] PRIMARY KEY CLUSTERED ([Customer ID] asc) )
here's a simple example using one of the many SQL-encryption examples, but you could use an application to do the encryption as well, so the encryption is completely separate from SQL--http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm
-- EncryptByPassPhrase(@password,@DataToEncrypt )
select EncryptedData = EncryptByPassPhrase('PEPE', '123456789' )
-- DecryptByPassPhrase(@password,@DataToDecrypt )
declare @val varbinary(max)
SET @val = 0x010000004A298120E629F7F4DC4AD09EB0E380C9E60040BDAE1DB6D8DA98C2A4D249EB71
select convert(varchar(100),DecryptByPassPhrase('PEPE',@val))
Lowell
June 29, 2017 at 7:00 am
Thanks a lot Lowell for granular level details. I want the SSIS package to reside on the central server along with the job and it has to pull data from local db. Is this possible ?
As you said, once the TCP/IP is enabled on local sql server express, i should be able to pull the data from the central server. Please correct me if i am wrong.
Thanks!!
June 29, 2017 at 7:08 am
I'm not a fan of Sync, especially since it is a twilight product. Syncing is a seriously difficult task to do. There will be merge conflicts that need resolved manually. We rolled our own Sync product in-house which was simpler than Sync and gave us the freedom to evolve our database. Sync seems to lack the concept that database design evolves constantly. In any case, I would never give this task to a less than expert developer.
June 29, 2017 at 7:09 am
alladiz - Thursday, June 29, 2017 7:00 AMThanks a lot Lowell for granular level details. I want the SSIS package to reside on the central server along with the job and it has to pull data from local db. Is this possible ?As you said, once the TCP/IP is enabled on local sql server express, i should be able to pull the data from the central server. Please correct me if i am wrong.
Thanks!!
yes exactly...a central server would certainly be able to pull from SQL express instances. you could have it pull every hour, for example,and if the machine to poll was not available, simply exit without raising an error.
I wanted to point out the difference between SQL LocalDB and a SQL Express instance, i think i got caught up on semantics and details...i should have known you meant a local SQLExpress instance.
Lowell
June 29, 2017 at 7:16 am
Awesome!! Thanks again Lowell. I think we will go with SSIS approach as we will have more control on data once we get it from remote local db.
Thanks a bunch!!
June 29, 2017 at 7:22 am
Bill Talada - Thursday, June 29, 2017 7:08 AMI'm not a fan of Sync, especially since it is a twilight product. Syncing is a seriously difficult task to do. There will be merge conflicts that need resolved manually. We rolled our own Sync product in-house which was simpler than Sync and gave us the freedom to evolve our database. Sync seems to lack the concept that database design evolves constantly. In any case, I would never give this task to a less than expert developer.
Thanks for your valuable insights Bill.I think i will go with SSIS approach.
June 29, 2017 at 7:41 am
I'm assuming that your intention is to run an SSIS job on a schedule, say every hour, and to somehow detect which of the laptops are connected and which are not?
You'll have some challenges with this approach, because it needs to be 'connection tolerant'. Eg, what happens if the laptop user disconnects midway through the transfer of their updates?
How many tables (approximately) are we talking about?
Do any of the tables have IDENTITY primary keys? If yes, you are going to have an interesting (I'm sure there's a better word!) time managing clashes (same identity, different data, from multiple local users).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 29, 2017 at 8:27 am
Phil Parkin - Thursday, June 29, 2017 7:41 AMI'm assuming that your intention is to run an SSIS job on a schedule, say every hour, and to somehow detect which of the laptops are connected and which are not?
You'll have some challenges with this approach, because it needs to be 'connection tolerant'. Eg, what happens if the laptop user disconnects midway through the transfer of their updates?
How many tables (approximately) are we talking about?
Do any of the tables have IDENTITY primary keys? If yes, you are going to have an interesting (I'm sure there's a better word!) time managing clashes (same identity, different data, from multiple local users).
Thanks Phil for taking time to look at this. I am assuming I can use execute process task to check for machine availability. I can create metadata table to keep track of time of successful completion and pick up the updated data based on it. I think the number of tables will be closely about 5. There wont be data clashes as data will be different per local user.
June 29, 2017 at 8:27 am
Using Service broker, with triggers, would solve the 'connection tolerant' problem. Service broker will work with SQL Express as long as it is connecting to a non-Express edition:
https://msdn.microsoft.com/en-us/library/ms345154.aspx#sseover_topic10
Of course, the plumbing for Service Broker is fiddly and you would have problems with monitoring but, as Bill mentioned, there are no easy solutions.
I think you should at least read the sync documentation as it will give you an idea of potential man traps.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply