October 5, 2012 at 12:11 pm
All,
The development group at my new company is using a WCF service running on the SQL Server to call a stored procedure that executes xp_cmdshell to call an SSIS package. They are doing this in order to handle control flow to know when they can execute their next package. I am not sure why they didn't just create a master package to control the flow of all the packages, but they didn't. Now I'm looking for any issues that might arise for the SQL Server as a result of running the WCF service on the SQL Server and calling xp_cmdshell numerous times to execute loads of millions of records at a time. For example, could this cause SQL Server to crash, cause performance issues, etc..? (I'm aware of the security concerns with xp_cmdshell and I typically turn it off.) However, I'm not sure I have a choice in this deployment so I'm trying to do my best to be prepared for potential issues. I have been researching it myself but am not finding anything. Thanks in advance for your help.
Thanks,
arussell_10
October 5, 2012 at 1:04 pm
I'm not super familiar with the performance impact of WCF in this sort of a setup, but I can tell you this - SQL Server, at a basic level, requires 3 resources: Disks, Memory, and CPU.
From what you've told me, it definitely seems like evaluations need to be done to establish whether or not merging these two environments is feasible. You need to get a baseline for your SQL Server's current average requirements for Disk, Memory, and CPU, along with peaks for each. You would then need to get a similar baseline for your WCF's resource requirements in a stand-alone environment. Ideally, all of this should be taking place in a test environment. Until you give us some actual values to work with, it would be difficult to say whether or not this would be feasible.
Things that you should specifically be concerned about -
Disk I/O. Is the WCF application going to be contending for similar disk resources? Will it be reading/writing from the same volumes that your databases live on? Don't forget about TempDB. If so, you can pretty much guarantee that your SQL Server's performance will suffer.
Memory. If these two services MUST exist on the same server, you will need to make sure that SQL Server's memory configuration is changed from the default, and that you establish an appropriate upper limit for its needs. Otherwise, it's possible that the between SQL Server and WCF, the server will start suffering massive memory faults. Again, this is where it would be very helpful to get a baseline for how much memory your WCF service will consume. If you know that your server has 16GB, your SQL Server regularly allocates 12GB, and your WCF service needs 5GB, you're going to be running into issues.
CPU. This would probably be harder to truly get a grasp on, and it's not my area of expertise...but, if you know that your SQL Server is regularly impacting CPU time and your WCF service has a sizeable impact on a test server (ideally with a comparably-equipped machine, specifically processor type and core quantity), it's quite likely that the combination of the two will result in your server's CPU being pegged.
If any one of these three are impacted, you can figure to see problems with your SQL Server's performance. If two or more are impacted, it's possible that your instance could be completely unresponsive. There's a reason why database servers are typically standalone machines.
October 5, 2012 at 1:21 pm
I'm not super familiar with the performance impact of WCF in this sort of a setup, but I can tell you this - SQL Server, at a basic level, requires 3 resources: Disks, Memory, and CPU.......
TheGreenShepherd,
Thank you for your response. I'll try and gather this information and reply with what I find out.
arussell_10
October 8, 2012 at 2:47 pm
arussell_10 (10/5/2012)
All,The development group at my new company is using a WCF service running on the SQL Server to call a stored procedure that executes xp_cmdshell to call an SSIS package. They are doing this in order to handle control flow to know when they can execute their next package. I am not sure why they didn't just create a master package to control the flow of all the packages, but they didn't. Now I'm looking for any issues that might arise for the SQL Server as a result of running the WCF service on the SQL Server and calling xp_cmdshell numerous times to execute loads of millions of records at a time. For example, could this cause SQL Server to crash, cause performance issues, etc..? (I'm aware of the security concerns with xp_cmdshell and I typically turn it off.) However, I'm not sure I have a choice in this deployment so I'm trying to do my best to be prepared for potential issues. I have been researching it myself but am not finding anything. Thanks in advance for your help.
Thanks,
arussell_10
Be careful using xp_cmdshell in this way. You are right to have concerns about security, but aside from that, one thing you give up is the ability to cancel an executing SSIS Package via the connection to SQL Server. With Packages processing large swaths of data that could be a major concern.
The scenario goes like this:
1. Execute stored procedure.
2. Stored procedure executes xp_cmdshell.
3. xp_cmdshell calls DTExec.exe.
4. OS process is spawned.
5. Caller decides processing it taking too long and cancels request. *
6. Database sessions ends and stored procedure exits/returns control to caller.
7. DTExec.exe process spawned by xp_cmdshell is still running on the OS.
* Or the process times out. Watch out for this from .NET. The default ADO.NET CommandTimeout is 30 seconds.
To recover from this scenario you would need to log into the OS and kill the OS process running DTExec.exe. If you have multiple clients spawning DTExec.exe via xp_cmdshell you'll have a fun time trying to decide which one to kill. Have you ever seen the end of The Abyss?
I would strongly urge you to recommend to your app design team that they go a different route.
Personally, I would not involve the database in the execution of an SSIS Package via xp_cmdshell. Is upgrading an option? In SQL 2012 you can execute SSIS natively within T-SQL.
Here is how you could safely implement remote execution of SSIS with SQL 2008:
Loading and Running a Remote Package Programmatically
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 9, 2012 at 9:12 am
opc.three,
That is exactly what I'm looking for. Thank you so much. Almost forgot to answer your question, unfortunately upgrading is not an option right now.
TheGreenShepherd,
The main developer is on vacation. I'm trying to find someone else who can test this process.
Thank you,
arussell_10
October 9, 2012 at 10:48 am
You're very welcome. Happy to assist!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 2, 2012 at 7:44 am
TheGreenShepherd,
Ok, I promised an update on testing once I had one. Well, this one is not great, but here it is. This is an existing process so the development team said it didn't need to be tested since it's just a simple re-write. I still pushed for testing, but lost. I'm keeping my fingers crossed and backups running. Hopefully, no issues. I'm also pushing for this process to be re-written. Thanks to everyone for the help.
arussell_10
November 2, 2012 at 6:26 pm
opc.three (10/8/2012)
arussell_10 (10/5/2012)
All,The development group at my new company is using a WCF service running on the SQL Server to call a stored procedure that executes xp_cmdshell to call an SSIS package. They are doing this in order to handle control flow to know when they can execute their next package. I am not sure why they didn't just create a master package to control the flow of all the packages, but they didn't. Now I'm looking for any issues that might arise for the SQL Server as a result of running the WCF service on the SQL Server and calling xp_cmdshell numerous times to execute loads of millions of records at a time. For example, could this cause SQL Server to crash, cause performance issues, etc..? (I'm aware of the security concerns with xp_cmdshell and I typically turn it off.) However, I'm not sure I have a choice in this deployment so I'm trying to do my best to be prepared for potential issues. I have been researching it myself but am not finding anything. Thanks in advance for your help.
Thanks,
arussell_10
Be careful using xp_cmdshell in this way. You are right to have concerns about security, but aside from that, one thing you give up is the ability to cancel an executing SSIS Package via the connection to SQL Server. With Packages processing large swaths of data that could be a major concern.
The scenario goes like this:
1. Execute stored procedure.
2. Stored procedure executes xp_cmdshell.
3. xp_cmdshell calls DTExec.exe.
4. OS process is spawned.
5. Caller decides processing it taking too long and cancels request. *
6. Database sessions ends and stored procedure exits/returns control to caller.
7. DTExec.exe process spawned by xp_cmdshell is still running on the OS.
* Or the process times out. Watch out for this from .NET. The default ADO.NET CommandTimeout is 30 seconds.
To recover from this scenario you would need to log into the OS and kill the OS process running DTExec.exe. If you have multiple clients spawning DTExec.exe via xp_cmdshell you'll have a fun time trying to decide which one to kill. Have you ever seen the end of The Abyss?
I would strongly urge you to recommend to your app design team that they go a different route.
Personally, I would not involve the database in the execution of an SSIS Package via xp_cmdshell. Is upgrading an option? In SQL 2012 you can execute SSIS natively within T-SQL.
Here is how you could safely implement remote execution of SSIS with SQL 2008:
+1000 Orlando. Very bad use of xp_CmdShell in this case. And, if someone kills the spid that makes the call, it can't be killed unless you kill the spid that xp_CmdShell used first.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2012 at 11:17 am
Jeff Moden (11/2/2012)
+1000 Orlando. Very bad use of xp_CmdShell in this case. And, if someone kills the spid that makes the call, it can't be killed unless you kill the spid that xp_CmdShell used first.
It is great to have learned that your embrace of xp_cmdshell has limits Jeff. Our disagreement over its use in general is nuanced if nothing else 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 10, 2012 at 7:58 am
opc.three (11/5/2012)
Jeff Moden (11/2/2012)
+1000 Orlando. Very bad use of xp_CmdShell in this case. And, if someone kills the spid that makes the call, it can't be killed unless you kill the spid that xp_CmdShell used first.It is great to have learned that your embrace of xp_cmdshell has limits Jeff. Our disagreement over its use in general is nuanced if nothing else 😉
Everything has its limits... even such disagreements. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply