June 15, 2011 at 7:00 am
I have two heavily used databases that are on the same server. We would like to move one of these databases to a new server for speed reasons. These servers are heavily intertwined with views and functions and asp pages. Is there a easy way to move the database without us having to rewrite everything? Any help would be very apreciated.
June 15, 2011 at 7:08 am
mbender (6/15/2011)
These servers are heavily intertwined with views and functions and asp pages. Is there a easy way to move the database without us having to rewrite everything? Any help would be very apreciated.
What does this mean? Are you trying to say the databases are related to each other? That procedures, views, and functions reference each database?
June 15, 2011 at 7:11 am
Yes in the pages and views they join together
June 15, 2011 at 7:44 am
I have a similar situation where I need to separate dependent databases onto independent servers to increase performance. The queries have massive joins between the database's tables - I just recently inherited the databases - so not my design.
I moved one of the databases to a 12 proc server with 24GB while the other remained on a similar server with a Gb interconnection. Then I tried linking the servers - the join performance was abysmal.
I don't have an answer yet but at least you don't have to waste your time trying linking the servers.
June 16, 2011 at 7:11 am
You're going to have to rewrite everything.
Sorry.
The thing is, if you try using linked servers, what happens is, let's say you join two tables across databases, in a single server, they move the data they need because all the access is in one place. With two, or more, servers, all the data in the table being joined gets moved to the server making the query and then the join process filters out what it doesn't need. To say the least this has an impact on the performance of the queries. To fix it, you can use OPENQUERY to pass a query to the linked server to retrieve filtered data, and then join on that result set, but like I said, it means you're rewriting everything.
Another option is to replicate (or set up an SSIS job or something) to move the data that is needed by the other system so that it's there on the new box, in the original form. But if that data is highly volatile you're still looking at the same issues you were trying to escape from by migrating one of the db's to a new server.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 16, 2011 at 7:16 am
thats what i figured. Thanks for your help
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply