| Author |
Topic |
|
spinout
Starting Member
2 Posts |
 Posted - 2004-01-24 : 08:37:50
|
| G'day!Is anyone able to give me any hints on how to achieve transferring data from a local SQL server running on my internal network across to a SQL server running at a web hosting company.What I need to be able to do is replicate any insert, update or delete method's performed on my local SQL server across to the SQL server at the web hosting company.Any ideas on how best this could be achieved? Any help is greatly appreciated.Many Thanks!! |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-01-24 : 19:46:15
|
| I would have thought you would need to use replicationsteveSteve no function beer well without |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-26 : 19:27:02
|
| You will need to implement transactional replication or create triggers on the tables. I would go with replication if it were up to me.Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-01-26 : 19:44:56
|
| Would log shipping solve this problem as well??Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-27 : 12:08:34
|
| No, log shipping is for disaster recovery purposes only. With log shipping, your destination database is either in loading or read only mode.Tara |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2004-01-27 : 12:21:55
|
| Well, it's not necessarily only for disaster recovery. We use it here for our client facing websites which only need it to be read only. If the web hosted site he's talking about only needs a read only db to run against, then it would be a viable solution.Mike"oh, that monkey is going to pay" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-27 : 12:28:53
|
| Log shipping is an overkill for a system that just needs the transactions to be replicated to another server. That's the whole purpose of replication.Tara |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2004-01-27 : 17:51:09
|
| I agree with you, but I've found that if you're in a situation where you have schema changes, then replication is a collosal pain the butt and log shipping is the easier choice. I think it all really depends on what fits your situation. I'm just not a huge fan of replication.Mike"oh, that monkey is going to pay" |
|
|
|
spinout
Starting Member
2 Posts |
Posted - 2004-01-28 : 05:13:00
|
| G'day FolksThank you for all of your responses. I think that transactional replication would be the way to go in a normal situation but I have a bit of a problem. My web hosting company only gives me very restricted and limited access with what I can do on their SQL server. Basically, I only have access to the server via FTP where I can upload scripts followed by a trigger file to run them. I don't have access to be able to get their SQL server and my local SQL server talking to each other to replicate with each other. |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2004-01-28 : 12:01:27
|
| Wow, that sucks. I guess you could create a trigger that buffers the changes into a different table and then create a stored procedure that checks the buffer table, takes any changes, generates the approperiate script, and then ftp uploads it, along with the trigger file, to the web host. Then schedule that stored proc to run on a specific interval, mabey ever 5 mins.Go here, http://www.nigelrivett.net/s_ftp_PutFile.html for info on ftp uploading within a stored procedure.- Eric |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-28 : 13:19:38
|
quote:
Originally posted by mfemenel
I agree with you, but I've found that if you're in a situation where you have schema changes, then replication is a collosal pain the butt and log shipping is the easier choice. I think it all really depends on what fits your situation. I'm just not a huge fan of replication.Mike"oh, that monkey is going to pay"
Tara: You don't think replication isn't a pain?Plus, what benefits does log shipping have over a simple set of home grown sprocs?I don't get.....(but then again, being in the dark most of the time DOES have it's benefits...)Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-28 : 13:36:16
|
| Yes replication is a pain but so is log shipping. Replication can be scripted out so easily. So when you do have to make table changes, you just run the drop script, then make your change, then run the create script. I wouldn't say that there are any benefits to log shipping over home grown sprocs. Log shipping does have a monitor though in EM, which is kind of cool, but then again you can write a script to check the status and run it in QA. I guess one benefit of log shipping is that you can switch the roles around so that the primary becomes the secondary and the secondary becomes the primary. We've never done it, but it does have that capability. Yukon is supposed to improve on log shipping, but I'm not sure in what aspect.I should mention that when I got here that log shipping was already implemented. I don't have the power to change it either. I would prefer to write my own scripts since log shipping is a royal pain in the a$s to configure.Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-01-29 : 13:25:41
|
| Log shipping is a pain? You set up a maintenanc plan, set the proper permissions for the SQL Server Service acounts, and run a stored procedure on the receiving server. It's cake. The only issue is, even if your clients only need read-only access to the database, as long as someone is connected, it can't restore the next log, so you can get out of date quickly if someone leaves themselves connected.I had a much more difficult time with replication...you have to have primary keys on all your tables and stuff...it was such a pain that it wasn't worth my time. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-29 : 16:06:05
|
| Well it's a pain in my a$s then. Using a maintenance plan is a pain since it uses a wizard. Using the log shipping stored procedure to create log shipping is a royal pain.Tara |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-01-29 : 20:35:49
|
| Spinout, who are you using ? It's not webtrader is it ? Get a new webhost that isn't so restrictive about what you can do.Damian |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-01-30 : 19:54:41
|
| Can you not negotiate something with the web hosting company? I think it would be my first option if only because it could save loads of worksteveSteve no function beer well without |
|
|
|
|
Transferring data between local and remote servers