December 17, 2014 at 12:28 am
Hi All,
Due to our 3 month, year end change freeze, we are not allowed to create new stored procedures at the moment on a production database.
We have done all the development work for new features on the production site and would like to start running them as soon as possible.
However due to the change freeze till end January, the only way we can reliable run the scripts is by running the *.sql file using parameters through xp_cmdshell.
I have examined the execution plans and everything looks the same (development proc vs xp_cmdshell execution), we do however see a minor decrease in performance when using the xp_cmdshell method. We can live with that however for the course of the change freeze.
Are there any other down sides to using this method that we might have over looked and are the execution plans cached when we use the xp_cmdshell method ?
Here is a sample of how we would execute the script till February using XP_cmdshell
DECLARE @SQLCMD VARCHAR(8000)
----CLIENTA
SET @SQLCMD = 'sqlcmd -S SERVER\MSSQL2014DEV -d CLINETA_Database -i \\servername\scripts\script1.sql -v db="TargetDatabase" destination="OutputTable" schema="CUSTOM" source="SourceTable"'
EXECUTE xp_cmdshell @SQLCMD;
This is how the stored proc kicks off
EXEC dbo.CLIENTA_NewProc
@db = 'TargetDatabase',
@destination = 'OutputTable',
@schema = 'CUSTOM',
@source = 'SourceTable'
Thanks in advance
December 17, 2014 at 1:07 am
To be honest, I'd be speaking to whoever has asked for these changes and telling them that they cannot go to production because of the change freeze.
It is then down to them to argue with whoever polices the freeze if they want to bend the rules. If they get an official OK to that request, you can go ahead and do the job properly.
Otherwise, you are setting yourself up for a fall: if something in your procs causes production issues, who do you think will get into trouble?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 17, 2014 at 1:25 am
When 'freezing' new development i'd freeze XP_cmdshell first of all, i should say. 🙂
Obvious reasons why performance slightly degrades are
1.Every time you run new sqlcmd instance it creates new connection.
2.This utility uses ODBC to execute Transact-SQL batches instead of SqlClient.
Sqlcmd is an ordinary client application from SS point of view and all queries it submits to SS are treated as any other queries.
December 17, 2014 at 3:26 am
Thanks for the replies,
Phil Parker, the request comes from high up the food chain and business will never understand why for 3 months of year we can only do "theoretical" development work which will only come to fruition in February. We did have the conversation around the purpose of the system freeze, primarily because we run a skeleton staff compliment over the Christmas period, but their argument was "but its December, systems should be quiet, what better time to do development". So in their infinite wisdom being told to find a quick fix till February was a viable option and execute xp_cmdshell was the only solution we had.
The disconnect between business and any IT department ...
December 17, 2014 at 5:16 am
Phil's point still stands.
There's a production freeze, you ignored it, if / when this comes out you're right in the firing line for ignoring it. In most places I've ever worked, production freezes are there for a reason, ignoring them is a dismissable (and in some cases potentially a criminal) offence.
There is usually a protocol for exceptions, changes which are essential may be put in place, usually with authorisation from a C level bigwig or the board. Putting in kludges to get round a freeze could be career limiting.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
December 17, 2014 at 5:30 am
I have to agree with Phil and Andrew. I would adhere to corporate policy and let the requester engage in the fight to get their work released to production. You might not see the rationale for the freeze, but that doesn't mean it's invalid. Besides, if it came from "high up the food chain" then they have a good chance of getting it done. To Andrew's point, there's always a process to handle exceptions.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply