November 25, 2011 at 9:25 am
1) We have two sites Silverton and Port Elizabeth, the data from Silverton needs to be read and reports need to be generated for Silverton site from Port Elizabeth. Is it possible to link both the servers?
2) The server in Silverton is behind a firewall and hence we will not be able to access the server the usual way. We have to get to the firewall team to open up ports are provide us with specific access to the server.
3) The Server at Silverton is running SQL Server 2008 R2 Standard edition.
4) The server at Port Elizabeth is running SQL Server 2005 Standard edition
Please someone give suggestion to me
November 25, 2011 at 9:56 am
You pretty much summarize everything yourself. Yes, you can use linked servers between the two sites. You will want to have a VPN connection between the two sites, and open the required ports in any firewalls. For a bit information about ports, you can have a look at my blog where I wrote about SQL Server ports.
November 25, 2011 at 10:18 am
Looks like a good scenario for transactional replication, at least on the (2008 publisher) + (2005 subscriber) side.
-- Gianluca Sartori
November 29, 2011 at 9:05 am
solomon.jernas (11/25/2011)
1) We have two sites Silverton and Port Elizabeth, the data from Silverton needs to be read and reports need to be generated for Silverton site from Port Elizabeth. Is it possible to link both the servers?2) The server in Silverton is behind a firewall and hence we will not be able to access the server the usual way. We have to get to the firewall team to open up ports are provide us with specific access to the server.
3) The Server at Silverton is running SQL Server 2008 R2 Standard edition.
4) The server at Port Elizabeth is running SQL Server 2005 Standard edition
Please someone give suggestion to me
We do this all the time for an application we use to service our Ports/Terminals. Here are my recomnedations.
Check MSDN online to make sure you have the SQL Server and Windows Authentication ports open required for Database publishing between the two sites. Make sure they allow any protocol going in either directions for all of the ports listed. This may not be needed, but if thier are issues it will make it easier for your Network team to debug.
Set up database publishing on the Siverton server and publish the database tables containing the data you are giong to report on using a push transactional publication.
Subscribe to the published data from Port Elizabeth SQL server. If the SQL servers are on different windows AD domains, a trust will need to be set-up between them before you can do this. You may need to make sure the SQL Server service account and SQL Server Agent account in the Port Elizabeth domain has access to the resources on the server in the Silverton domain. There are many articles and forum post on MSDN online about Database Publishing to help you with planning this.
Good Luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply