Replication or ...?

Problem:

I have SQL server 2005 database; one central server on which is installed 2005 Standard Edition and X subscribers which have installed SQL Server Express edition.
The structure of the database is the same as the one on the central server. The difference is the data set on each subscriber (the central server has the complete database with all the data (complete data set) and on each subscriber will have only part of the data form the central server database (data subset of the central server data set). This subset is specific for each subscriber. (This dataset depends of the subscriber)
I have to do a classical data merge (bidirectional update of the data)replication. The difference is that the central servers (the publisher) have to merge different subset (set) of data depending on the subscriber (different data merge for each subscriber separately).

The connection between the central server and the subscribers are different for each subscriber. Some of the subscribers will be in the same domain, some through internet connection or some of them will have dial-up connection.

Can someone, anyone help me on this or give me some advice or suggestion?

Thanks.

niko_san

I'll try set merge replication between each site and central server if each site has own tables on central server. Otherwise, may need transactional replication with update subscriber enabled. And use filter on each site for specific data set.

Apparently, (and i hope I"m wrong here) none of the freely available tools allow replication to be set up.

I have dozens of Databases hosted with my web sites on a shared server, running SQLServer2005 (dont know what "Yukon" means so I'm not sure if it's that or not). I have local versions I work on, doing updates, some sites I build locally, and "publish' the site contents once a week, other sites I have to download the remote data to my local machine so I have complete copies to work on and do development.

However there doesnt appear to be any equivalent to DTS that I used to use in EnterpriseManager/SQL2K.

Oh I know there's SSIS- but apparently no way to replaces whole tables in one database with their equivalents in another. And there's a Transfer Database task predefined in SSIS, but you have to have database creation privileges to run it. No one on a Shared server has that - only the Control Panel does.

So there's replication. But none of the tools freely available seem to have replicatoin available. No menus on any of the tols I have use the word "replication" anywhere, and books on line doesnt have the word anywhere searchable.

So what do i do? At the moment I have to use SSIS in debug mode, using a separate query window to delete all reacords in a table, then run SSIS in debug mode to upload (or download) a single table at a time.

But in total i have 352 tables over all my databases. this is extremely tedious and time-consuming, considering it all used to happen every night automatically using my old SQLServer2K and EngMrg.

What can i do now?

Here are the tools i have available:

EMS SQLManager for 2005 Lite - no mention on any menu of replication or SSIS
Microsoft Visual Studio 2005 - no mention of replication on any menu
Microsoft SQL Server Management Studio 9.00.1399.00 no replication mention anywhere
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Visual Studio 2005 Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework Version 2.0.50727 Installed Edition: IDE Standard
SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer Version 9.00.1399.00
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer Version 9.00.1399.00
SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers Version 9.00.1399.00

Sql2k5 comes with replication feature, and SSIS has transfer objects task. You can transfer table with it.

Thank you rmiao, but since none of my tools have any sort of menu item, either on the main menus, or on any right-click context menu that has the word "Replication" on it, perhaps you could tell me how i set any replication settings.


And I know there is a transfer objects task, but as far as I know it can only transfer one object at a time, and can't delete records first, so unless i have missed something i have to manually delete all the records from the receiving table, then transfer a single table, then manually delete all the records from another table, then manually transfer the ntext table.

Since i have 352 tables in all, and i can't automate it at all, it's a laborious process. Which was the point of my post.

SO you're saying I'm stuck with it the way i have it now?

If so that makes SQL2005 a MAJOR step backwards.

Which tool do you use? There is replication folder in SSMS object browser. You can choose objects to transfer and replace data as you like by editing the task.

Thanks again rmiao. I'm not sure what you mean. In SqlServer Management Studio, there's an Object Explorer, but that has no mention of replication anywhere. not on tabs, not on menus, right click menus, anywhere that i can see.

And there's nothing called Object Browser.

I can transfer one object at a time using debug in SQLServer Business Intelligence Development Studio, but as I said i have a total of 352 tables to manage. I can't spend half my week transferring tables between servers and still not have the daily copying i'm supposed to be doing.

There is surely a way to automate it. I've seen tutorials about importing and exporting to and from excel, text files, xml files but nothing anywhere about to and from tables between servers.

I can't believe i'm the only one in the world who regularly needs to move databases between servers in a shared web hosting environment. IN fact I KNOW I'm not. but after 3 weeks of asking everywhere I can find to ask, i havent found ONE person who can either show me how to do it or point me at a resource that'll show me how.

Sorry, I mean object explorer. What you should do is right click on the server in registered servers -> database engine in SSMS, go to connect -> object explorer. You'll see that server in object explorer. Expand the server, replication folder is in between server objects and management folders.

When you right click transfer db objects task and go to edit in control flow in BIDS, it opens task editor. Click on objects, you'll see options like copydata, existingdata, copyallobjects, and objectstocopy. Edit them properly, you should be able to copy multiple tables.

Since sql2k5 is new to everyone, need time to get know it.