July 12, 2011 at 2:09 pm
I need to be able to ongoingly copy a SQL Server Database from one external location to my location, keeping the same table formats. I want to set up a job to run in Management Studio to either retrieve the database and load it to my SQL Server or if it is sent to me, to be able to load it to my database.
This would need to be done possibly serveral times a day in order to have recent transactions,, similar to replication.
The data in the database I want to bring in is inserted and updated to the database at the other location from my location via Internet (Cloud).
Is there an efficient way to do this with the 2 SQL Servers being at different locations?
Thanks.
DMR
July 12, 2011 at 2:25 pm
I am not sure I understand the configuration you have - or what it is you want to accomplish.
There are several ways you can get data from another system, each one with its own advantages and disadvantages.
If you do not want to go the route of replication, you could always look at building an SSIS package to extract the data you want and import it into your local database.
Or, you can take a backup of the remote database - copy the backup to your local system and restore it.
There are probably other options - but I would need a better understanding of your requirements.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 12, 2011 at 2:53 pm
Thank you for your reply.
Both locations have SQL Server 2008 database. My location uses the BI tools SSRS, SSIS, SSAS.
Microsoft .NET framework.
What I need to do is create reports from the data that the users are entering into an application where the data is ending up on a database in another location which is not a part of my firm. This is centralized so that the data entered in by other firms can be shared along with mine firm's data.
I will be needing a copy of the current data for my firm only, and the company that stores the shared data will be able to extract a copy of the database for just my firm's data.
The users within my firm will be running reports that I create for them ongoing so the data will have to be updated often. That way the reports I create will reflect the most current information.
This is similar to the DSS replication from OLTP databases but OLTP will be at another location and I will need to use the data from DSS. So the issue is getting the DSS database loaded regularly.
I know they can send a .bak file. Is there a way to replicate the data? Can this be done with SSIS?
thanks. Please let me know if there is anything else you need for I'm a developer and not much of a DBA.
Thanks!
DMR
July 12, 2011 at 8:18 pm
ruffindmc (7/12/2011)
Thank you for your reply.Both locations have SQL Server 2008 database. My location uses the BI tools SSRS, SSIS, SSAS.
Microsoft .NET framework.
What I need to do is create reports from the data that the users are entering into an application where the data is ending up on a database in another location which is not a part of my firm. This is centralized so that the data entered in by other firms can be shared along with mine firm's data.
I will be needing a copy of the current data for my firm only, and the company that stores the shared data will be able to extract a copy of the database for just my firm's data.
The users within my firm will be running reports that I create for them ongoing so the data will have to be updated often. That way the reports I create will reflect the most current information.
This is similar to the DSS replication from OLTP databases but OLTP will be at another location and I will need to use the data from DSS. So the issue is getting the DSS database loaded regularly.
I know they can send a .bak file. Is there a way to replicate the data? Can this be done with SSIS?
thanks. Please let me know if there is anything else you need for I'm a developer and not much of a DBA.
Thanks!
DMR
I am sorry - I have read this multiple times and I still don't understand what you are trying to accomplish. Your choices - as best I can figure out is:
1) Replication - this would require the host system to define the publications so you can subscribe to them and get the updates. Using transactional replication you would get near real-time data, however - since this other system is hosted off-site you will have a challenge getting that setup unless you have a dedicated connection to the other network.
2) SSIS - this would require the host system to generate update files that you could process through SSIS into your local system, or - direct access to the host system so you can extract the data directly. Downside of this approach is that it will not be real-time and would have to be setup to only get data that has been changed. If the databases are small enough - you could perform a full refresh every time, but that really is going to depend on how long it takes to extract the data, move it across the network to the local system and then load it.
I don't see any other options - unless you go with an automated restore of the backup of that database. But - that may take even longer to get the backup file across the network.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 13, 2011 at 8:22 am
Thank you for your information. It sounds like you understand what I need. I will need to talk with someone at the host location in order to figure out how I need to get the data. I do not think I will be able to have real-time updates either way.
Thanks again for your help.
DMR
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply