Advice on replicating database nightly to web host

Question

Advice on replicating database nightly to web host

TAMMYAUDETTE

Ten Centuries

Points: 1259
More actions
November 22, 2010 at 12:02 pm

#229453

Hello,
I have SQL server 2000 enterprise, and have created a database that pulls data from an ODBC connection nightly.
It then feeds a second database on the same server which limits the information and has additional views. I would like to replicate this second database to our web host every night (a full replace), but can not figure it out.
I thought I could use a backup/restore procedure, but unfortunately the web host version of sql (while it supports 2000) does not have the sql agent. I can't figure out how to schedule this nightly. I cant setup the hosted db in enterprise manager, it will not let me.
I did try the data publishing wizard, but for some reason a 750 MB database results in .sql files over 2GB in size, despite selecting data only. I also considered exporting the nightly database changes to text and using an ftp program to schedule the flat files to copy, but then i'm still stuck with the fact that the hosted version does not have an agent to scheule these imports.
I'm to sure how to accomplish this, it seems like it should be simple, am i over complicating?
Any ideas are welcomed. FYI, this database feeds an ASP application.
Thanks!
Tammy

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

This situation can be tough. replication and log shipping assume some degree of access to theSQL Server itself. It sounds like you do not have access to that.

Is this shared hosting? is the SQL server the web app connects to also the host for the SQL server, or is it a different machine?

why ship the whole 750 meg db each night, isntead of just changes from the last syncroniuzation? wouldn't that be much smaller than say, 10% of the database?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TAMMYAUDETTE Ten Centuries Points: 1259 More actions · Answer 2

Thanks for the quick reply.

Yes, this is shared hosting so I do not have access to the sql server , save for a limited application they have but it doesn't allow access for packages/scheduling. I can also access the database via sl server management studio (using the ip address), but because there are other databases that are not mine, I can't access anything but Tables, View, synonyms, Programmability, Service broker, and security for my DB only. None of those will help me scheule the import, right?

I would totally go the way of exporting changes only; it would indeed be smaller, but I still don't know how to get that data imported to that sql server via a scheduler. I would have to manually import the changes daily. If I could schedule the changes to export from the source DB, schedule the ftp of the files to the webshot, then schedule the import to the hosted DB, I'd be laughing. I can only manage the first two.

Your thoughts?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

TAMMYAUDETTE (11/22/2010)
Your thoughts?

My first instinct is that you're looking to do administrative level tasks. Are all servers in house to your company, or is the target SQL Server an external hosted server? If they're all internal, it's time to turn to your DBAs and discuss approaches.

If this is a target external hosted server, it's going to be a long and complex SSIS package, I would think, built on the server that you have all the power on and with just data pumps and DDL to the target server that you have at most DBO to.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

Looks like you are using SQL Server express in web server. That is why you are not able to see the SQL Server Agent service.

What you could try is replication. One way. Your web host acts as subscriber. (Since it is express, I think it is). Therefore you could use transactional replication (Push) and the agent runs in your Publisher. But double check if it is sql express.

-Roy

TAMMYAUDETTE Ten Centuries Points: 1259 More actions · Answer 5

Thanks everyone for your help.

I decided to take a dedicated box with sql server enterprise on it and make it a webserver.

This way we're hosting ourselves, I have complete control, and we're using our failover ISP (which never gets used) to handle the traffic, which would be moderate.

Appreciate you help. Sometimes you just have to take it in house!

Tammy