Link to home
Start Free TrialLog in
Avatar of bemara57
bemara57

asked on

How to sync a databse with a linked server in MS SQL Server 2000

I have a linked ODBC datasource in my linked servers for MS SQL Server 2000. My question is how do I make this a database. WIll I have to create a blank database and sync it with the linked server? I want to be able to perform web queries to the SQL Server. Since ODBC connections from the web would be extrememly slow, I would like to query SQL Server's database, but in the background, the database is synced to the linked server at timed intervals. Is anything like this possible?
ASKER CERTIFIED SOLUTION
Avatar of QPR
QPR
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi David,
At the risk of hijacking the OPs question.
Do you know the URL of this recent discussion?

We have a web app we are (test only first) about to put in our DMZ, it has a significant back-end and I'm juggling with the idea of having the web app speak direct to the live DB (on our network) or put a second db in the DMZ and have 2 way replication.
Would be very keen to read a previous experts thread that deals with the pros cons risks etc
Hi QPR,

https://www.experts-exchange.com/questions/22137194/replicate-and-delete-data-between-servers.html?anchorAnswerId=18574465#a18574465

His company was determined to have SQL outside the firewall with a portion of the main database, and have the web-server talk to the outside SQL server.

For my money, I'd just have the Web-server talk to the SQL server inside the firewall ...

Here's the gist of the accepted answer:
I looked up replication in SQL 2005 books on line, and snapshot replication seems to fit the bill.

Not sure about the timing. There looks to be a wizard that would allow the external facing server to pull the snapshot when it wanted it, and that could be scripted.

Regards
  David
PS I haven't used SQL replication yet - haven't needed to.

Regards
  David
Cheers I'll read through it. I'd much rather have the web server (DMZ) talk direct to the internal DB but the network admin talks of security risks. He suggested replication but either way you open a port to the inside world
Hi QPR,

That was my point.

You either open up a SQL port(s) in the firewal, or open up a SQL port(s) in the firewall ... :-)

Regards
  David