March 21, 2014 at 4:21 am
I have a project that was thrust upon me for integrating an internal SQL Server database with a web portal. I am very new to SQL Server 2012, and so some of this is a sanity check, and some of this is a strategy check for opinions and input.
So there is currently a SQL Server 2005 internal box that is using DTS to sync data once a night to a web portal with SQL Server 2000. The current database is about 80-85 GB in size. Most of this was done by a third party company, there is no documentation, and the box is currently in production. The portal is mostly ASP.NET and the whole thing is on a cold fusion server. They hired a web designer that is migrating the current code base over, and is integrating it with Umbraco. I did not have any input for this part. I spun up a cloud server with Server 2012 and SQL Server 2012. Followed articles to get Umbraco working properly with IIS 8 and SQL Server 2012. The current SQL 2005 box is on the list for replacement this year about August. It will be a Server 2012 and SQL 2012 box. I have a backup DC/Backup server slated for purchase in April which I plan to have just as backup DC/DNS, print server, and then have the file server mirror over to and SQL Server backup to, and then that gets both a local backup and a secure online backup run from there. This is a smaller company and these purchases are going to stretch the budget to the max. To make things more interesting, they want to do a limited launch of the new portal in stages over the course of this year with an initial go live date of April 5th.
1) I was initially thinking about having SQL Server 2012 Express on the backup server with a live copy of the data as a just in case. I am assuming this is not crazy. I was thinking I could also use this to run all of the SQL backup's each night, but also use it as the point for the new portal server to synchronize the necessary tables with using SSIS. I was looking at the chart for SQL Server 2012 and it looked like the Express Edition had limited SSIS capabilities.
http://msdn.microsoft.com/en-us/library/cc645993.aspx
2) The other reason I was thinking this way is that since they want to go live with the portal, and keep the old portal up, I was worried about trying to turn on SSIS on the SQL 2005 box along side of the DTS stuff that is running. I am afraid I am going to break the setup. It looked like the original vendor tried to use SSIS. It is not clear if they did something with SSIS to make the DTS work. I am not sure how to backup and restore what they did if I break it.
3) Am I over thinking this? I picked up the Microsoft SQL Server 2012 Books including the Integration Services book. I have watched a few videos online, and it does not seem to be too bad. It is just straight table copies from one server to the next.
4) For security purposes, I am trying to keep the portal server as isolated from the internal servers. I was thinking about using a VPN tunnel to do the transfer, but then it looked like I might be able to use FTP to push the data as part of SSIS. I want to limit the ports that are open to the outside. Any thoughts on this?
Thanks.
March 21, 2014 at 5:25 am
1) Express only has the import export wizard, but you cannot edit SSIS packages or run them directly.
2) Maybe they use the DTS task inside SSIS to run the DTS packages?
3) Straight table copies are really easy with SSIS.
4) FTP is possible with SSIS, but the built-in component is a bit limited.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 21, 2014 at 5:50 am
Thanks for the reply.
1) So you are saying I would need the Standard edition. Would the web edition work? I am just thinking of saving them some money since the SQL Server 2012 licensing has gotten so crazy expensive.
2) Yeah - it was not really clear. In fact, it looked incomplete and possibly abandoned. That is where the confusion/concern comes in, and the idea of just copying it to a second server for now and then doing the sync. If I break the DTS, the director of marketing will be up my butt. I was waiting for someone to chime in and say - they are completely unrelated, or just do X to back the DTS, it will restore cleanly if all fails.
3) I felt the same way. But you know how that is until you have done it once to completion. I am going to setup (2) test boxes this weekend to do a dry run and test of the updating.
4) I gathered that the FTP was not the preferred method. My concern is that if the web box gets compromised that it might leave a window back to the internal infrastructure. If the web server gets hacked, I can delete it and restore from image and backup in minutes since it is a cloud server. If they are able to get form the cloud server to the main system, it is a much bigger mess. Anyone have any thoughts on a best practices? I was thinking of doing a pudh from the internal server, but that is where it appeared I needed the FTP, or might have done something wrong. I could see both servers, but I could not get data to transfer. Then I started looking at alternative methods like VPN. Anyone with real world experience with this?
March 21, 2014 at 5:53 am
Regarding point 1: web will not suffice, you need at least standard for SSIS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply