Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What would be the major concerns for running WCF services and xp_cmdshell on SQL Server?


What would be the major concerns for running WCF services and xp_cmdshell on SQL Server?

Author
Message
arussell_10
arussell_10
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 1321
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
TheGreenShepherd
TheGreenShepherd
Mr or Mrs. 500
Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)Mr or Mrs. 500 (597 reputation)

Group: General Forum Members
Points: 597 Visits: 583
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.
arussell_10
arussell_10
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 1321
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
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
arussell_10
arussell_10
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 1321
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
You're very welcome. Happy to assist!

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
arussell_10
arussell_10
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 1321
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44956 Visits: 39862
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:

Loading and Running a Remote Package Programmatically


+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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8231 Visits: 14368
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44956 Visits: 39862
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. :-D

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search