| Author |
Topic |
|
pjn
Starting Member
27 Posts |
 Posted - 2005-09-05 : 10:05:20
|
| I've been reading up on older forum posts about this subject, and am still undecided.I'll try to explain our scenario.A number of PCs are being used to log sales transactions at each site. These PCs need to work locally in case of any network problems, so we are thinking of using MSDE on these PCs purely as a local datastore. If the network is available we'll bounce these transactions up to the main server using replication, otherwise they'll be replicated as soon as the main server becomes available again.However, the main server may then be part of a larger picture, and in turn has to replicate its changes to an HQ server etc.First question - should we be using transactional replication or merge replication? Transactional replication could be an issue because I believe this isn't available in MSDE - and the cost would be a big factor for our clients if we need full SQL Server.Second question - we're going to have problems with our Identity keys across sites. If we use merge replication, I'm assuming we'll need to add GUIDs to each table. Therefore are we better off just using uniqueidentifiers as our PKs from the outset, or using a combination of guids and Identity columns with 'not for replication'?And thirdly... any gotchas I need to look out for.I'm currently googling for recommended practices, but thought I'd also post here.Any pointers on this will be greatfully received - my colleague has disappeared on holiday for 2 weeks so I need someone to bounce ideas off!ThanksPete |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-05 : 10:28:48
|
well if you want to do merge replication you go with guids.we do that and it works ok...what size do you expect for your db?Go with the flow & have fun! Else fight the flow  |
 |
|
|
pjn
Starting Member
27 Posts |
Posted - 2005-09-05 : 10:42:22
|
| Thanks. The DB size will vary a lot between clients - the larger ones will do millions of transactions per year - these ones will probably be expected to buy the full SQL Server for their main server as I expect they hit the 2GB database size limit for MSDE.How will the GUID's affect performance on very large tables? There'll be plenty of reporting and joins being done on the main transaction tables - although earlier records may get archived off to an OLAP system.You mentioned that you've had no problems with GUIDs, so that's put my mind more at rest. We're still at the design stage at the moment so we're open to suggestions. Neither of us have touched upon replication before so we're probably trying to run before we can walk! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-05 : 10:47:26
|
well.... we don't have millions of records...  guids can only be a problem because of their size and index fragmentation.that's going to be a problem if you perform a lot of range scans.i was wondering something else.... anyone is welcome to join in...since we do need replication (guids good, identity bad) and we have NC and C indexes on themwould adding an identity and putting a Clustered index on it and also having a guid for replication be ok?Go with the flow & have fun! Else fight the flow |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-05 : 15:45:31
|
| "will probably be expected to buy the full SQL Server for their main server as I expect they hit the 2GB database size limit"Assuming this is a commercial environment I would run a mile if a client wouldn't afford the SQL licencse - MSDE is fine for a travelling salesman, but for a mission critical central database?I ain't got no [Micro$oft!] axe to grind ... but ...!"How will the GUID's affect performance on very large tables"There isn't really any problem on [singleton] Retrieving based on GUIDs, as I undersantd it the issues are when inserted "shed-loads" of GUIDs in quick succcession.1,000,000 in a year (200-odd days, 8 working hours) is about 10 a minute. That probably won't cause you any problems."You mentioned that you've had no problems with GUIDs"Note that since some bright spark decided that basing GUIDs on the Network card's MAC address represented an invasion of privacy (For goodness sake!!) it is no longer guaranteed that GUIDs will be unique. Long odds, but you might be the poor blighter that gets a GUID number that was intended for Alpha Centuri!! At the very least its probably wise to make sure that you have a UNIQUE INDEX on all GUID columnsKristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-09-05 : 17:28:43
|
| I'd say merge replication.Use guids or a composite key with site + identity.Knowing the requirements beforehand as you do, i would probably use site + identity.(smaller keys, less fragmentation, and you know where they come from) |
|
|
|
pjn
Starting Member
27 Posts |
Posted - 2005-09-05 : 18:34:12
|
| Thanks for the further replies.I wholeheartedly agree about the MSDE/full version issue - I've had many heated discussions with the powers-that-be about that :-) Our system is mainly centred around EPOS, so we're (hopefully) only looking at having MSDE as the temporary data store on each till until they've had chance to update the main server. As we're intending to use Reporting Services I really hope we're not expected to use MSDE as the main server too!! Our MD doesn't quite grasp the concept of licensing a full SQL Server - our current system is FoxPro-based so he doesn't like the idea of having to pay for a database engine. We're slowly bringing him round though.Site + Identity is something we're also looking into, especially as we're going to be storing a site ID anyway. We were originally aiming to avoid composite-keys but there's no point making life more difficult for ourselves.Although we need GUIDs if we go with merge-replication, the site+identity is probably going to be nicer to work with.Thanks again for the suggestions - it's much appreciated. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-06 : 01:57:56
|
| "Our MD doesn't quite grasp the concept of licensing a full SQL Server"Well ... he'll get what he pays for then!!"site+identity"Probably a daft idea, but if you have few enough sites you could set the increment for IDENTITIES so that the numbers don't clash - if you have six sites then first starts IDENTITY at 10000 increment by 6, the second starts at 10001 increment by 6, and so on. You can have them increment negatively too (for future expansion maybe!)Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-06 : 05:55:06
|
quote:
Originally posted by KristenProbably a daft idea, but if you have few enough sites you could set the increment for IDENTITIES so that the numbers don't clash - if you have six sites then first starts IDENTITY at 10000 increment by 6, the second starts at 10001 increment by 6, and so on. You can have them increment negatively too (for future expansion maybe!)
that's not a bad idea... never thought of that...Go with the flow & have fun! Else fight the flow |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-06 : 06:12:54
|
| "Our MD doesn't quite grasp the concept of licensing a full SQL Server"Does he get the concept of your customers paying for, and getting value from, your products?It's that same with SQLServer......a product to be exploited, to give a return. The issue at stake is the classsic cost v return. Search for or create a comparison of the features of the 2 products....if the extra's you get from SQLServer are worthwhile, the cost will minimise. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-06 : 11:19:58
|
| "that's not a bad idea... never thought of that..."You probably discounted it for lack of scalability!Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-06 : 11:27:58
|
| true true...Go with the flow & have fun! Else fight the flow |
|
|
|
pjn
Starting Member
27 Posts |
Posted - 2005-09-07 : 04:03:39
|
| I'm currently bashing my head against the wall after a discussion yesterday with the powers-that-be!They seem unable to accept that clients should have to pay for a SQL Licence, especially for the much smaller 1-till 1-PC system that only costs a few thousand. Saying that, they weren't happy about doing it for the 50-100K system either.Their answer - "maybe we should just stick with our existing FoxPro system - there's no additional cost there"My answer - *shakes head and leaves room*I've been having the same conversation since July 2003...Anyway... I've gone off at a tangent there! I'm prototyping a few ideas based on everyone's suggestions here - I think we might be heading towards the staggered-identity columns as mentioned by Kristen. Found a page here that explains it in more detail - http://www.quest-pipelines.com/newsletter-v4/0903_F.htm |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-07 : 06:55:00
|
| MSDE for a stand-alone field-app is usually a reasonable approach.However, for the central database it would be nuts!"staggered-identity columns"It doesn't scale, so will be limited to a finite number of "sites".For a more scalable solution add a column for "Site Code", and then a second Identity columns (all sites can start from the same seed)Kristen |
|
|
|
|
That old identity vs. uniqueidentifier thing..