Accessing two SQL DB's at same time with stored procedures 3

[1DMF]

Hi,

I have two SQL servers in two locations running two different databases.

However, the inhouse application uses both and with MS Access it's easy to link in tables from various data sources and then write SQL joins across these tables as though they were in the same DB.

Is this possible with stored procedures and how?

I want to use SQL and SP's to to do the joining and simply pass back to my application the recordset.

How would I go about doing this?

Do i need to as in the VB forum about getting separate DAO recordset in memory and then performing another join dynamically within code?

MS Access makes this easy, but it is slow, I want to do this the quick way with SP's and DAO.Recordset but I can't get my head round how this would be done.

your help is appreciated.

Regards,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[SQLScholar]

Look up linked servers.

Once you set up a linked server on serverA to serverb you can then query like

select *
from
Serverb.database.schema.table

You should also be fine to reference servers as per the above within a linked server.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[1DMF]

I did look into linked servers, but when I tried to create a link between our in-house SQL 2000 machine and our webserver co-located which run SQL 2005, I couldn't get it to work.

I assume with linked servers i could do..

select *
from
Servera.database.schema.table
JOIN
Serverb.database.schema.table 
ON
Servera.database.schema.table.column = Serverb.database.schema.table.column

OK there is the potential there with the select to have ambiguos columns, but you get the idea.

Do both servers have to be in the same domain and the same versions of SQL ?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Kinda, sorta.

When you use a linked server, you almost always need to use a table alias because SQL Server does not like more than 3 "dots" between your objects.

So your query would look like:

select *
from
Servera.database.schema.table  As TableOnA
JOIN
Serverb.database.schema.table As TableOnB
ON
TableOnA.column = TableOnB.column

Do both servers have to be in the same domain and the same versions of SQL ?

No. They do not need to be on the same domain or the same version of SQL Server. If they are on different domains, it is usually easier to use SQL Authentication to connect from one instance of SQL Server to another. Of course, this means that your SQL Instance allows mixed mode (SQL and Windows) authentication.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

thanks, much appreciated.

I guess I need to get this linked server thing to work then so I can have a dabble.

It will open up a world of faster apps and better programming for my environment than I currently do.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Don't (necessarily) think that linked servers will solve all of the worlds problems. I mean.... it may be faster than your current method, but there are common performance problems with linked servers.

Ideally, you will want the fastest connection possible between the linked servers because a lot of data will likely pass between them. This is especially true if you have large tables.

Think of it this way... in the simple example query where we join two tables based on a single column. How does SQL Server know which rows to return? It must pass all the data from that column in one of the servers to another, so even if there is a small number of rows returned, it may require a lot of data being passed back and forth to figure this out.

I guess my point is... with linked servers, it is even more important that your queries are properly tuned for performance. Instead of passing data around in memory on a single computer, now data is being passed around on a network connection between multiple computers.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

hmm then the problem is unlikely to go away, as basically that's what access is doing!

I just had another go at creating a link and I dont' understand what I need to fill in where.

I can't see any place where i put the remote IP address of the server i want to connect to, it just asks for server name, and as our SQL refuses to work with named pipes, I'm a bit confused over all this.

The only other solution is to use SP's to get the records from each server and then somehow create a memory resident tables and do a join with them.

Any ideas?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[gmmastros]

Are you trying to use the interface built in to SQL Server Management Studio to do this? I always seem to have a problem with the interface so I usually do it in straight TSQL, like this:

sp_addlinkedserver 'AliasNameHere', '', 'SQLNCLI', NULL, NULL, 'SERVER=IPAddressOfServer', NULL 

sp_addlinkedsrvlogin 'AliasNameHere', 'false', NULL, 'UserNameHere', 'PasswordHere'

For the login stuff, you should enter a username and password that represents a SQL Login on the remote server. You should also make sure that this SQL login has the appropriate privileges to the database and tables you want to access.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Yeah tying to use the Studio management snap in, can't work it out at all, so glad I'm not the only one!

I'll try the T-SQL when i get a chance and let you know how I get on, many thanks for the help.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[SQLScholar]

1DMF,

Quick question.

Your webserver - are you only pulling data from it?

If so - have you considered replicating (or log shipping) the tables from the webserver to the other box? All depends on what you want and what you are able to do. Also depends if you can replicate between 2000 and 2005 (not sure).

All being aside then the data you require will be all on the same server and speed issues caused by different servers will go away.

Obviously there is also the overhead that this will take, but its definitely something that should be considered.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[1DMF]

Yes , i have considered replication and everytime i ask my IT provider about it they look horified and ignore the question.

I have no idea why, but it's as though i'm speaking a dirty word, like saying 'Macbeth' if you are a Thespian!

I know that i really need all the data locally , i could then use stored procedures and joins and wow would it be fast, real fast, but I'm yet to find anyone who offers me IT services to take my enquiry seriously when I mention log shipping.

Is there a reason for this?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[jcaulder]

Replication management requires an entirely different skill set to maintain than typical SQL Server maintenance. That's probably why you get the resistance. It isn't terribly difficult to set up but when things go wrong, you better know what you're doing. There are many DBAs with no experience with replication.

I have an environment with 42 remote machines around the country all replicating back to corporate via merge replication. The same corporate database then replicates via one way transactional replication to a reporting server. I'm a programmer and had no experience with replication coming in but was able to design the topology including dynamic partitions, filtering, an archiving solution to keep subscriber databases small, merge table maintenance jobs, etc. It just requires a little effort.

It works beautifully most of the time. One of my biggest problems is connection speed/bandwidth available between my subscribers and publisher. This can cause serious issues when large amounts of data need to be passed and especially if trying to reinitialize them remotely.

You would probably not encounter these issues between web server and database server in your environment. But it is a very big question to answer.

Also, how much latency is acceptable to you? Typically replication is set to occur on a schedule but can be set to occur constantly. If replication occurs on a schedule and data has been updated on the web server but replication has not ran, the new data on the web server will not be available in your subscriber database thereby potentially leading to failed queries/erroneous result sets. I have mine set to run every 15 minutes which is acceptable in my environment. Also, ideally transactional replication occurs between two servers on a LAN or very reliable connection. Unreliable connections are not well suited for transactional replication.

You can absolutely replicate between different server versions. The rules vary depending on type of replication but in general are very flexible. Compatibility mode also comes into play. Usually you are simply limited to the functionality of the lowest version server in the topology. Microsoft has a very thorough white paper/chart on this topic.

J

 

[1DMF]

Thanks for the input, much appreciated.

I have considered all options and downsides, as well as expertise required, stability of connection and data availability.

I have actually decided that a couple of the main tables in questions would probably be best suited to be moved and then keep the current set up.

We are about to implement new systems which will require these tables to no longer be priority to the web users / extranet system, but more in-house.

So the sensible thing to do is have the data sitting on the SQL server where it is needed the most.

This will seriously speed up in-house systems and VPN users with out the risk of corrupt log shipping and lack of support or expertise to implement, manage and fix when (not if) things go wrong.

Regards,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta