March 11, 2015 at 8:37 am
I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:
We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.
Here is what I want to do:
I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.
Can anyone help me get started here by detailing the steps I need to take?
March 11, 2015 at 9:25 am
1) you can create a linked server of destination server on the source , write a insert query put it in a SQL job and schedule it
OR
2) Create an SSIS package to copy the rows from source to destination , embed it in a SQL job and schedule it.
March 20, 2015 at 10:14 pm
Some questions about your requirements...
- Are you needing to completely replace the local sales data (i.e. delete all local rows then populate all rows from hosted database) or do historical rows remain unchanged and so you only need to bring down the new rows from the hosted database since the last data transfer?
- How many sales are we talking each day, and in total all-time?
- How large is the hosted database backup? I am thinking you might be able to just copy a database backup from the hosted environment and restore it to your local Express instance. From there you can bring the data you need into your local database using a stored procedure that makes a cross-database call into the restored version of your hosted database.
You have two main problems to solve:
1. What will you do to get the data form the hosting environment to your local environment? You have tons of options here, some sunder and I have mentioned. The best choice will depend on your requirements. See my questions above.
2. How will you schedule the getting of the data? Given that you are using Express Edition locally you will not have access to SQL Server Agent, the built-in job scheduler that ships with SQL Server in Editions higher than Express. Does your hosting company allow you to setup SQL Server Agent jobs on the instance where your databases reside and can your hosted environment talk to your local instance? If the answer to either question is no then your job is a little more difficult than if you had access to SQL Server Agent, however by no means impossible. Hopefully you have ready access to the Windows Task Scheduler on your local server and could setup a Task that executes on a schedule to get the data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply