March 2, 2012 at 7:06 am
If this should better go to another forum, tell me which one and I'll repost.
I'm trying to integrate two accounting systems with SQL Server back-ends, one is 2005 and the other is 2008.
I want to query 3 fields from one table in the 2008 d/b to populate a view in the 2005 database. There will be an update query job to sweep the 2008 data into the 2005 database once a day. This isn't a onetime shot, but an ongoing connection.
If the two databases were in the same instance, I'd have no problem setting this view up, but I can't seem to make it work when the 2008 db resides on a different instance/different server.
I've added the 2008 db to my 2005 db as a linked server and that let me create the view I need. But I'm not familiar with linked servers being used steadily in a production environment this way and I've read a lot about linked servers coming with a performance hit on both systems.
Is there any way I can build the view in my 2005 database so that I don't need the linked server and the resulting performance drag?
Or is there a way to write a view that can access a database on a remote instance?
Thanks in advance
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
March 2, 2012 at 7:27 am
OPENQUERY is an option which can be used to get the data from remote server without using link server, however I would not recommend it as I think link server will be more efficient here.
What is the size of the table that you are accessing on remote server?
Is transaction replication a possibility in your environment?
March 2, 2012 at 7:28 am
I don't think the performance would be a big deal but there are a number of factors that can come into play.
Have you looked at replication as another option? You could have a table in you 2005 instance that would hold replicated data from your 2008 instance. The performance issues would then be unnoticed by the user because all your queries would still be local to the 2005 instance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 2, 2012 at 7:29 am
Use OPENDATASOURCE feature.
SELECT *FROM OPENDATASOURCE('SQLNCLI',
'Data Source=Server2;Integrated Security=SSPI')
.test.dbo.MyTable
March 2, 2012 at 7:33 am
The remote table is roughly 70,000 rows.
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
March 2, 2012 at 8:10 am
Capt. Sigerson (3/2/2012)
The remote table is roughly 70,000 rows.
Transactional replication should work perfect.
March 2, 2012 at 9:20 am
Thank you very much. I've set up the replication distributor and publisher and subscriber databases but the replication is failing because of a login error when the subscriber tries to get data. I set it up to use the SQL Server Agent service account but that's not working. Is there anything I should make sure of in order the get the subscriptions logged into the distributor? Any advice would be appreciated.
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
March 3, 2012 at 7:58 am
If I understood your requirements correctly - you want to be able to populate data in the 2005 instance once a day from the 2008 instance.
If that is correct, you shouldn't have any problems using a linked server to populate the local table. You could also avoid the linked server by using either SSIS or BCP to export the data from the 2008 instance and import into the 2005 instance.
Where you run into problems with linked servers is when you create a view and then try to join to that view with local tables. In these cases, SQL Server ends up doing some weird things that affect performance like cursoring through the remote table and pulling one row at a time.
I would just schedule a job to run once a day to truncate the local table, and then populate the local table from the linked server. Example:
TRUNCATE TABLE dbo.LocalTable;
INSERT INTO dbo.LocalTable
SELECT {columns} FROM server.database.dbo.RemoteTable WHERE {filters};
If you need real-time access, or near-real-time access then you either have to use the linked server in all of your queries or setup replication.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 4, 2012 at 1:42 pm
Capt. Sigerson (3/2/2012)
Thank you very much. I've set up the replication distributor and publisher and subscriber databases but the replication is failing because of a login error when the subscriber tries to get data. I set it up to use the SQL Server Agent service account but that's not working. Is there anything I should make sure of in order the get the subscriptions logged into the distributor? Any advice would be appreciated.
1. Login that is used to connect publisher must be db_owner on publisher database.
2. Login that you are using to connect to distributor must be db_owner on distribution database,
3. Login that is used to connect to subscriber must be db_owner on subscription database.
Therefore if you are impersonating SQL Server Agent account to access these databases, ensure that you have set above said permissions on the required databases for SQL Server Agent Account.
If above information doesn't help you to resolve the issue, kindly post the exact error that you are getting. Click below link to read more about replication permissions:
http://msdn.microsoft.com/en-us/library/ms151868.aspx
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply