Linked Server part II

[gbaughma]

OK, I've managed to link the (huge) access database into my SQL Express 2008 server. It is working, and permissions are fine, etc. etc.... performance, however, as abysmal.

The queries that I have to run against it are a spaghetti-dish full of joins and so forth, and since the access database sits on another server and has a couple of dozen people attached to it at any time, queries are very slow (to the point of timing out on my ASP pages and so forth).

SO......

Is there a way to copy / replicate / synchronize the tables from the remote linked database to tables running on the local SQL server?

I'm sure I could do an "Insert Into" type of command, but that would include truncating all of the tables and re-copying them regularly. I'd like a more "live" option.

So, I guess what I'm saying is; is there a way to replicate the tables on the linked server to the tables on the local SQL Express server?

TIA!


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[mrdenny]

Not really, you'd have to write something. Instead of doing that, why not look at tuning the SQL Server database now that it has your data in it.

Odds are you are simply missing some indexes. As your database is in SQL Express it can't be more than a couple of Gigs in size, so unless 0 tuning has happened it should run ok. SQL Server databases can get up into the Tera-byte range and still perform fine. (I've got takes that are 80-100 Gigs in size that can be queried in less than 2 seconds.)

Start by running SQL Profiler and getting a trace of all the commands being run against the database. Run this through the database tuning adviser and create the indexes that it says are missing. This should give you a decent start at least.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[gbaughma]

Can I run the profiler on a linked server that's linked to a MDB file on another machine?



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[mrdenny]

Sorry, I missread your initial question wrong. I thought you had loaded the data into SQL Server, not that you were linking to it.

Queries from SQL to Access aren't going to be all that quick usually.

Can you move the data into SQL, and convert the Access data to use SQL Server for it's backend?

Profiler will tell you how long the Access queries are taking to run. So it could be helpful. If you are running large queries against Access, depending on the query SQL may be bringing a large amount of data from Access into SQL, and doing the joins, filtering, etc in SQL.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[gbaughma]

mrdenny:

Unfortunately, while I have had some moderate success at moving this particular access database to a SQL backend (I got it to move)... the performance was abysmal.

Unfortunately, the way this particular Access application was written, it does some *REALLY* irritating things.

For example, loading 27,000 records all at once, then filtering off the last 3 months to show the user.

It *can* be made faster, by re-writing a bunch of the underlying queries, however my boss told me that we would only be using that particular system for another 6 months, when it would be replaced by another (access) system, and I was not to put any time into development on the system that would be retired.

{Shudder}

Meanwhile, one of the customers has pretty much demanded a different electronic deliverable, which means that to do this, I have to do some pretty intense queries against the MDB backend on my webserver. I thought that linking the MDB to my SQL server on my web server would be the answer, but unfortunately, the queries are very slow (over a minute to pull one report from a little over a million records).

So.... one of the things I'm going to try doing *today* is a copy of the backend to the local machine; I can fire off a scheduled event to copy it every hour; so while the data won't be "Live" (as in "Instant") on the web page, an hour delay is still quicker than nothing.

What would be nice is if I could figure out a way in SQL Express to replicate the *linked* database to local SQL tables (keeping them up to date and synchronized with the MDB file) so that web queries run fast, but the data is still "live".

I have this sinking suspicion that it's not going to happen in Express... that I will need full-blown SQL to do this... . which might be interesting, since I'm running DotNetNuke for our web page, and moving that to a different SQL server backend might be painful....



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[mrdenny]

There's no real time replication using access as the source, which would be what you need.

You'd have to write your own solution using T/SQL, or a Windows app to read the changed values from the access database and write them to the SQL Server on a regular basis. As SQL Express doesn't have a scheduler you'd have to use Task Scheduler (or another scheduler) to handle running the replication.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog