I'm still wrestling with this problem of how to keep my databases in synch between the dev versions, the backup version and the production versions in my shared hosting environment. It's proving a VERY time consuming (and utterly tedious) process to delete all the records in a table, then use SSIS one table at a time to copy all the records from the other database. I have about 40 databases with a total of 350 tables, and while I don't have to keep all of them in synch it's taking up the equivalent of a whole day each week just copying records.
Previously, on SQLServer2000 it was done automatically every night without my having to do anything manually. I thought the "Transfer database" prebuilt task would be the go- then I found out that in a shared hosting environment, I don't have permission to create databases - only the control panel does, so it can keep track of quotas and disk space etc. I thought I could use SSIS and have it loop through the tables, copying one after the other, but apparently that wont work because you can't loop through table names, only column names (unless I am misunderstanding the docs) and besides, you have to delete all the records from the receiving table before you can upload the records from the sending table, or all the linked tables will have the wrong indexes. Then I thought perhaps replication might be the way to go. Apparently it's designed for just this task. But there's no way to set it up on the tools Microsoft gave me. Does anyone know how I can set up replication with these tools? There's no tab, menu, right-click option or anything I can find that mentions "replication" that I've found. Here are the tools I have available - perhaps someone knows them better than I do and can offer a suggestion as to where to look: 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 Does anyone have any ideas how I can proceed on this? I'm not going to be able to keep up this amount of time spent on simple routine database maintenance for much longer. I refuse to believe I'm the only one in the world that needs to do this kind of thing. Cheers Mike Kear Windsor, NSW, Australia Macromedia Certified Advanced ColdFusion Developer AFP Webworks Pty Ltd http://afpwebworks.com Full Scale ColdFusion hosting from A$15/month ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2462 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
