Is replication transparent? 1

[florindaniel]

Hello,
I've searched for "replication" and there's a lot of topics; however I could not find an answer for my question,
perhaps is too basic and obvious.Z

So, is replication application-transparent? I mean, if I have an app. using data from a local MSDE database (running on some remote workstations, connected via VPN) and this local server synchronizes (via replication) with a central SQL server, does the application need to have some special architecture?
Or it is unaware of that mechanism and only finds updated data on the local server?
Also, does the data tables need to have some special design features?

Thank you,

 

[johnherman]

In theory, Applications are unaware that their data is being replicated. No particular design is needed. Replication should be transparent. That said, it is still susceptible to network glitches, CPU stress, etc. Don't count on the replicated data being completely up to date at any point in time. It takes time to replicate and it's done in the background.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)

 

[gk53]

It is transparent. I used that architecture. One server in central office and servers in different locations witch connected to internet and running VPN into central office network.

 

[Olaf Doschke]

You can see the replication as another user, all things to consider are quite similar to the challanges of multi user enabled applications. That also means it's quite doable and maintainable.

One thing making replication possible is using uniqueidentifier, RowGuid. If your database uses integer identity as PK replication may somehow be possible, but at least the replication process needs to temporarily set IDENTITY_INSERT with all it's downsides, even if you reserve ID values for each location.

Retrofitting RowGuids into a database is not easy and does influence the application itself, so that can hinder you to introduce replication as a fully transparent and independent process without any impact on the application.

Bye, Olaf.

Edit: MS SQL Server offers options to manage replication of data based on Int IDENTITY, but the management of this is a hurdle you don't have with GUIDS.
See

https://technet.microsoft.com/en-us/library/ms146907(v=sql.105).aspx

 

[florindaniel]

Thank you all for help,

I still don't know how replication handles Identities and relation between tables based on that but I'll build a test database and see how it works.

Thank you again,
Daniel

 

[johnherman]

Essentially, if you have an identity column in a table, and replicate that table, the numbers assigned to the identity column may not be in synch, may not stay in synch, etc. You could, for instance, have a stored procedure to keep a record of the primary table's identity column as a data column in the target system, and even have a sp to update the target table's identity column value to a data column in the source table. It's a lot of extra work, so hopefully you don't need identity columns. Alternately, you could use a separate table to handle your identity values and increment the identity column programmatically rather than via internal SQL Server processes. Hope this helped a little.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)

 

[gk53]

It is number of ways to handle identity columns in replication (if you use identity columns). Even if you not doing anything when you set up replication system will create additional columns with and system tables which will track and handle all conflicts It is works pretty well.

 

[florindaniel]

Well, thank you, I was pretty confused about identities, now I am really confused

I do use identity columns; basically I have three tables:
- Products with idProduct identity
- Documents with idDocument identity
- Rows with idRow identity AND related into Products and Documents via idProduct and idDocument

There is more than one workstations sending data to the server so, for example, the idDocument=7344 will
occur several times, each time with a set of rows according to an workstation.

I don't care the values as long as Documents ans Rows are well paired.
I expect Replication to generate new Identities but maintain the proper pairing for Document-Line relation.

That is my main concern now: can I trust replication to properly match relations between tables by generating new identities or I must use some other means of unique relationship, like GUID?

Thank you,
Daniel