Edhy,

Here is a step-by-step on setting up Merge Replication.  Just serach on Google for about 1000 more articles.

http://www.databasejournal.com/features/mssql/article.php/1438231 

And Paul is right on the sequential GUIDs...that is definitely the safest route to go.

Great!

Thanks Trent.

Edhy Rijo

Like Trent said there are a ton of articles explaining the different types of replication written by people that know it and can explain it way better than I could.

Just like the decision to use integer or guid primary keys it really depends' on your business requirements as to what replication scenario would work best for you. Also do not forget that StrataFrame has the Enterprise Server which may be a better option again depending on what your requirements are.

Paul

One consequence of using any form of replication is future changes to the database schema. Most database schema comparison/updating tools have problems upgrading a database currently in use for replication. In an ideal world you would be able to change your database schema which would then be replicated across to other databases. SQL Server 2005 is better at allowing some updating but most upgrading tools will fail on a replicated database which could force you to remove replication, upgrade and then setup replication again.

While we are on the subject, does the Database Deployment Toolkit support upgrading of replicated databases?

I like the idea of sequential GUIDs but does this still guarantee they will be globally unique?

Hi Aaron,

As I understand it is the presence of the network card's mac address (on the machine creating the GUID) in the GUID algorithm assures uniqueness.

Peter

I may be wrong but I believe Microsoft switched to version 4 GUIDs from Windows 2000 onwards. This version doesn't use the mac address as it was deemed a security risk given that it allowed the GUID to be traced back to a PC. Version 4 GUIDs uses random parts for all sections.

While I like the idea of sequential GUIDs the problem with UuidCreateSequential is it still uses the mac address and it may only guarantee uniqueness on the local PC if the PC doesn't have a network adapter.

Hummm,  not NIC, not program to run

Edhy Rijo

I have been sick over the weekend and my wife is trying to keep me in bed so I don't get worse...but she had to go to the store so I thought I would do a little catching up on the forum.

To answer your question, yes, the DDT will most definitely update a database structure for a SQL Server that uses replication.  When doing this, however, it is best to update the structures when there will be the least amount of activity on the databases (none if possible).  but in any case, you can actually write a program to deploy your structures using the DatbaseMigrator class.  There is a sample that comes with the framework that shows how to do this.  You can create a program that updates all servers on separate threads at the same time...reducing deployment time and making the process more streamlined.  It is the same thing that you would do otherwise, just updating multiple servers on different threads at the same time.

On a separate note here, there has been a lot of discussion on GUIDs and sequential GUIDs.  There is one problem wth sequential GUIDs that exists when you will not be talking to a server and you need to come back in and merge the data.  It is the same problem that exists when using integers.  If you come up with a mechanism to create sequential GUIDs, and you take a laptop, let's say, offsite and it creates records, and there are other laptops doing the same thing, then you can definitly have sync issues.  So this then turns into the situation where each laptop has its own pre-set range to work within, with ends up having the same issues as using a PK.

I recommend doing a little test.  I am of the mind that it is easier to deal with the one outlier that may (though it is extremely unlikely) produce a duplicate GUID.  If you create a program that creates new records non-stop using the System.Guid.NewGuid() for 24-48 hours (this will prouce some SERIOUS records!)  I believe that you will find that there will more than likely never be a duplicate.  If you del with disconnected data (pre-merge or replication) in thi maanner and perform a "pre-merge" query to ensure that there are no duplicates, then you don't get into the syncing issue of sequential GUIDs.  This is just some food for thought.

Aaron,

If you have an application that is going to replicate it would need a Nic to be able to do so , but I suppose it is possible that it could be an external type nic that is unplugged whilst the user is adding records. I went back and forth on the what method to use as well, below is some code that uses datetime ticks to sequence a guid.

As far as updating schema, with sql 2005 at least you can make some minor schema changes directly to the publisher. http://msdn2.microsoft.com/en-us/library/ms151870.aspx .

However it is easy to get A.F.U when doing anything major. I have not had to change much except a few alter table's to add a column or 2, but in my next major release I will probably be hating replication due to the number of schema changes I will be making, most likely I'll end up having to drop and re-add subsciptions.

In my case however I can restrict my users from using the software until the database and application has been updated. I create software for internal use only so I have more flexibilty than some when it comes to things like that.

Hi Paul & Trent,

So the DDT can handle replicated databases? That is brilliant!  SQL Server 2005 does allows some minor upgrades to a replicated database but it is not enough and on some occasions it has even rolled back a new field addition 24 hours after it was applied. Too many times the replication has to be dropped, the upgrade applied and then replication turned back on again. This in itself is not necessarily the problem but we could be dealing with 50G+ databases which are then pushed out to the subscribers.

While it is true most PCs will have an NIC, we are faced with the situation when a single site PC with no interface has been creating transactions for years which then has to be merged with another of our systems. Basically, in our application, we really need a globally unique ID and I can't see beyond the ugly Guid(). In our case the sequential ID isn't guaranteed to be globally unique and wouldn't be any different to the integer mechanism we currently use - which still has conflicts from time to time.

Aaron