Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What would be the major concerns for running WCF services and xp_cmdshell on SQL Server? Expand / Collapse
Author
Message
Posted Friday, October 5, 2012 12:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 98, Visits: 1,190
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
Post #1369246
Posted Friday, October 5, 2012 1:04 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:34 PM
Points: 565, Visits: 458
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.
Post #1369281
Posted Friday, October 5, 2012 1:21 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 98, Visits: 1,190
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
Post #1369287
Posted Monday, October 8, 2012 2:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1370055
Posted Tuesday, October 9, 2012 9:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 98, Visits: 1,190
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
Post #1370445
Posted Tuesday, October 9, 2012 10:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
You're very welcome. Happy to assist!

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1370486
Posted Friday, November 2, 2012 7:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 98, Visits: 1,190
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
Post #1380372
Posted Friday, November 2, 2012 6:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1380651
Posted Monday, November 5, 2012 11:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1381217
Posted Saturday, November 10, 2012 7:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1383372
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse