August 6, 2013 at 8:03 am
Hello all. I imagine that this is a common scenario and solutions are found all the time. However, before I start experimenting and over complicating the solution, I thought I would ask for advice.
Every day, I need to export data from a few derived tables from some internal SQL Server tables and import them into a matching set of SQL Server tables which our web site runs on. Our internal SQL Server is obviously behind our firewall and does not accept external traffic or requests (such as http).
We have a web hosting service which hosts our web site and provides us with a SQL Server database. Every day, I connect to both our internal DB and our external (web site) DB using SSMS. I then go through Import/Export Wizard and simply import the data FROM internal tables INTO the matching external (web site) tables.
This works OK but it presents a couple problems.
1) Web site data goes down for about 60 seconds. I can reduce that to about 10 seconds but the real problem is....
2) Requires a manual process. Someone actually has to do this.
So my question is, can I easily automate this? I feel that SSIS services would be the way to.
Anyone doing something similar?
Are there any good SSIS tutorials online?
Thanks!
August 6, 2013 at 8:14 am
If I understand your setup correctly you can communicate between the two SQL servers over the same network so I am assuming they are on the same domain?
If so you have a few options.
Create an SSIS Package with two data sources, one is for one server and the other for the other server. For security you can either use a windows trusted connection or a SQL Server Login.
When it comes to automated running you can run the SSIS package via a SQL Agent job, in this case the service account used for the SQL server Agent Service will need to have privileges to access both servers with the correct permissions, as when ran as a job it runs under the SQL Agent security context.
Alternatively if you are using SQL Logins this should not be an issue.
MCITP SQL 2005, MCSA SQL 2012
August 6, 2013 at 8:19 am
Thanks. No, they are not on the same network or domain. Internal vs offsite, 3rd party web hosting company.
August 6, 2013 at 8:41 am
There's many ways to do this but without seeing your actual setup, it's hard to say what's best.
Sounds like using SSIS is a good idea. If the 2 servers cannot communicate with each other, consider using SSIS on the source server to export the data to one or more files. You can use SSIS to FTP those files to the other server. You could then use SSIS on the other server to import the data.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 6, 2013 at 8:49 am
RedBirdOBX (8/6/2013)
Every day, I connect to both our internal DB and our external (web site) DB using SSMS. I then go through Import/Export Wizard and simply import the data FROM internal tables INTO the matching external (web site) tables.
This process is actually using SSIS. At the end of the wizard, you have the option to save the package. Do that and then import the saved package into a new SSIS project. Most of the SSIS work you need done will already be finished--although with very generic names.
This works OK but it presents a couple problems... but the real problem is....
2) Requires a manual process. Someone actually has to do this.
So my question is, can I easily automate this?
Yes. Once the package is built, you have various options for scheduling. See the references for more details.
Are there any good SSIS tutorials online?
Tutorial:
Stairways to SSIS by Andy Leonard[/url]
Many options about scheduling. Search 'Schedule SSIS package' for them. Here's one that talks about SQL Agent scheduling and references command line scheduling.
Blog post by Pinal Dave on scheduling SSIS packages in SQL Agent[/url]
August 6, 2013 at 9:00 am
Interesting!! Thanks. I will look into....
August 6, 2013 at 9:08 am
RedBirdOBX (8/6/2013)
Interesting!! Thanks. I will look into....
A couple of cautions. I wouldn't recommend just leaving the package as you find it from the wizard. Update names, use variables as needed to make the package more flexible, whatever is needed to make it the non-generic package you want for the job.
Pay attention to the wise advice from RTaylor2208. You'll need to attend to permissions in the connections. Also watch out for the package encryption level. The default uses 'EncryptSensitiveWithUserKey' and is built from your login. If you schedule under different credentials, you'll probably need to change from the default.
August 6, 2013 at 9:13 am
Will do. Will post back here later today with results.....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply