Wednesday, March 24, 2010

Getting Stubborn Chief Executive Officers to Listen: Speak Their Language



After consecrating several posts on workplace bullying, and hearing our great Steve Jones rant about Toshiba’s lack of action regarding their own products, I have a very recent anecdote that should turn the heads of the Canadian Broadcasting Corporation (known to locals as the CBC) within the province, and those who are responsible for funding to minorities from outside the province: the Right Honourable Minister James Moore for Official Languages, and CBC CEO Hubert LaCroix. I am taking this action publically since Quebec's CBC head did not reply to a message sent on March 13, 2010 regarding the following legal issue, on top of the ethics code violations.


Nancy Wood’s Cause Célèbre: Montreal Daybreak Listeners Revolt Against CBC Gall

Pia Marquard, the CBC’s head in Quebec, run out of the head office in Montreal, decided (or predecessor Robert Renaud left the fort in such a bad state she had to) at the end of February, 2010, to cut short Nancy Wood’s one year contract as host of the local radio morning show Daybreak – or to actually inform her well before the July deadline that this was going to happen. CBC's Internal HR policy is to give a few months warning, which would have been around May 2010, but this seems to way more than that. As should have been done when one signs a contract and honours it, unless it has been breached by Nancy (and it wasn’t, confirmed by Marquard’s replies), you let it run out and inform the person at the end that it will not be renewed, or with a little 'courtesy' warning. The contract was not respected by the head of the CBC in Quebec in this matter; this is the first part of the major reputational damage done to CBC Montreal by means of this lack of professionalism. Basically, Nancy was on a one year contract and had the rug pulled out from underneath her just over half-way through, citing ratings decline – despite the fact that very little advertising budget was allocated. However, word of mouth/e-messaging spread well about Nancy's forced departure, as over six hundred and forty fans have demonstrated on the facebook page, plus the five journalists who have written out in protest. <opinion> This is another example members of QC’s majority making decisions for the English speaking minority and minimising the damage.
Wake of this Damaging, Weakly-Founded Attack on Nancy’s Career: Our Host, from Our City

Although only weeks later we have come to understand that the lack of ratings scapegoat was leaked to the press somehow (typical of a new host within the first year of their tenure, hence BS scapegoat), to justify the change in host of Daybreak in reaction to the outcry, Madame Marquard initially responded to critics that for personal issues and the principle of secrecy with respect to our former morning host as an employee, she would not be providing the reasons behind her ousting. This is completely ironic, since the source of the weeks of anguish and embarrassment that Nancy as a person had to endure was caused by CBC Management’s ignorance with respect to how psychological harassment can affect an employee - and the many months more it will take for Nancy to recover (a workplace harassment specialist was referred to her BTW by yours truly, since I lived through a similar attack by the workplace goons at the Caisse de Défauts) from this disrespectful treatment. I ask Madame Marquard who is responsible for this insensitive action, if not her? And why are you acting as if it is Nancy’s fault that she had to take approximately a month off of sick leave due to management's actions? If you had settled in quite well with a new job, and were very happy (I witnessed this as an avid listener to Mrs Wood during the morning commute wouldn’t you like to wake up to her voice?) how would you react to someone breaking your rice bowl I ask? Did you think this mega blow to consumer loyalty would go unnoticed?

Many journalists pointed out (see references below) the lack of honesty regarding the her mistreatment, and CBC Quebec’s disengagement with its English-speaking audience (listeners bombarded CBC Management for their brain-fart), but none, as of yet, have noticed that Labour Standards Laws apply to this situation. Even if they are Provincial Laws, still doesn’t mean that you can break them in Federal Offices located in Quebec - this is a confederation, remember.


The Harassment Did Not Stop There, Believe It Or Not...

Progressive labour standards laws exist in Quebec for a reason, and have been in effect for over five years now due to rampant unprofessionalism within the workplace, which is especially noticeable in Montreal since it is seen as a final battleground for the separatists who wish to eradicate the Anglophone population from the cosmopolitan city by starving them from the workplace - I believe this has well and truly spilled over into Federal institutions within the province for several decades now.

The next unwanted gesture taken under Marquard’s management, earlier this month, was to state on the CBC Daybreak website, and in responses to angry listeners who stepped into save the victim from corporate bullying, that Ms. Wood was simply a ‘temporary’ host – actually using the phrase that she was only an Interim host (see the boilerplate letter below). Witnesses of Nancy’s anointment to the post were quick to point out yet another incontestable fabrication of reality. Way to motivate your team to perform to the highest standards, eh? Sabotage their position, and then tell them that they were only there for the short term – and Quebec wonders why there is la crise de leadership ? The truth is right there in front of you ladies and gentlemen, only Losership dominates the Quebec workplace unless you are lucky enough to find a work environment that respects labour standards (and from personal experience, this is hard to find in Montreal) and their own internal ethics code.


Respect for the Rule of Law: What the Labour Standards Act States


Vexatious behaviour is humiliating, offensive or abusive for the person on the receiving end. It injures the person’s self-esteem and causes him anguish. It exceeds what a reasonable person considers appropriate within the context of his work.-- Nancy’s month off for sick leave in anguish over losing her chair on Daybreak, was that WANTED? Is it too hard to show integrity and honour a contract by waiting until July or June, instead of taking the loser route and robbing her of a well-earned position mi-chemin?

Affect the person's dignity or integrity-- Stating that she was an Interim host (lies, here's the original CBC page announcing her start at Daybreak) and not the actual host is a clear violation of Nancy's dignity. This will result in Nancy being isolated from her coworkers surely, and most importantly even if these was done in a private workplace it is illegal, but Marquard’s unprofessionalism went beyond the methods of acceptable managerial behaviour because she did make these legal mistakes publically and by doing so, clearly demonstrates that Pia was gives the impression of being intentionally vexatious.


Conclusion and the Way to Fix Up this Mess – Recover CBC Management’s Integrity

I wonder what more jambettes does CBC Managment have in store for the talented professionals working for the CBC in Montreal(?), since there do not seem to be any you would Not use on great persons such as Nancy. Furthermore, isolating Nancy from her colleagues, as is happening now, is a typical result of an environment where psychological harassment exists by the way – so we even have a smoking gun to demonstrate labour standards were not respected, in case someone was looking for one. I am not trying to some lofty writer attacking CBC Management, and if that is the perception, then forgive me, but professionals of high calibre such as Nancy deserve that the Rule of Law be followed by decision makers within a publically funded organisation. If lying to the public is how Madame Marquard wishes to continue playing the denial game, then the only real thing that will be ‘interim’ will be her tenure as head of the CBC in Quebec. Management is responsible for the actions that lead Nancy to take sick leave, and it is a clear cut case of shameful workplace bullying that is no longer acceptable for modern organisations running in Quebec.


Stepping in to Protect the Victim from Corporate Ignorance

My goal with this public post is to make it impartially clear to those higher up in her CBC hierarchy that CBC Management in Montreal is completely misinformed with respect to laws that protect those who are considered employees under the Labour Standards Act (fixed hours, subordination, and having to use CBC equipment) and violating of any law by CBC Staff, should not be taken lightly (not to mention Ethics Code abuses, which could be a whole post on its own regarding accountability), as it will lead to more inappropriate behaviour - just in the same way that Nancy Wood pointed out on Daybreak that ex-Premier Landry’s Xeno/Anglophobic comments regarding Sabia as head of the Caisse de dépôt et placement du Québec were completely inappropriate. Thus, I appeal to you Ministers and Directors to help us bring Quebec out of the dark ages of workplace standards.


If you do not believe it is enforced by the Working Standards Commission, think again, as they attack the largest pension fund manager in the province, and even the country, flanked by the Human Rights Commission here on my behalf.


Thank you most sincerely,


Hugo Shebbeare


Relevant References:


The Bring Back Nancy Wood Facebook Page – many outraged witnesses to the illegal psychological harassment commented there as the surreal events unfolded before their eyes aimed – it becomes obvious that Marquard and her co-horts were indirectly keen on denigrating Nancy’s own self-esteem and dignity. Ironically, there are more members in this FB group, than the actual Daybreak FB group itself, by over one hundred!

Fagstien’s Blog posts relating to Nancy Wood’s Mistreatment (he lists many, many more sources)



Upholding Information Rights by Christopher Graham, Information Commissioner’s Keynote Address to the Data Protection Officer Conference on March 3rd, 2010 held in Manchester.



The letter which indicates the violations of law by Marquard:

First, we'd like to thank you for your dedication to Daybreak - we're glad to know that it's part of your weekday morning. We also understand that because you're a regular Daybreak listener, you are concerned about our recent decision to change the show's host.

Nancy Wood joined the Daybreak team as the interim host in August 2009. It was not a permanent assignment and when she returns from her current absence she will begin a new assignment at the CBC.

We understand that some of Daybreak's listeners will not only miss Nancy as the show's host, but have also asked for more details regarding our decision. Our policy, like most organizations, is not to disclose personal and confidential information regarding our employees. We think this is an important principle of privacy and respect for all of our employees.

We sincerely thank Nancy for her continued commitment to the CBC and most recently, while she was in the Daybreak host's chair. We look forward to continuing to work with her when she returns.

Thank you for listening to Daybreak

Pia Marquard
Managing Director, Québec


Nancy has clearly said to us via personal e-mails and comments to close friends that she is still trying to recover from this disrespectful behaviour, so Pia is only covering her ass and trying to use the smoke and mirrors technique to avoid blame for workplace bullying actions against Nancy that continued after her position was taken from her.



CBC’s lack of clarity and outraged listener response [abridged from Steve Faguy's site]: http://blog.fagstein.com/2010/03/10/nancy-wood-debate/


      · In its non-response to the many listeners who are supporting Nancy Wood the CBC brass is acting as though the CBC is a privately owned medium. Actually, I would define the CBC as private in effect, regardless of its public funding.

Shloime Perel



Shloime: By LAW it's supposed to be the same with CBC. But that LAW has been IGNORED for decades! The Politico - & THEIR PALS - control everything. The interesting thing is, it's taken the Nancy Wood firing to have us Joe Public - take a deeper look into what the hell is REALLY going on with OUR Publicly Owned Broadcaster. And I tell you - at first I thought I'd found a 'can of worms'... turns out, it's a LANDFILL of worms. It's a tightly knit , 'You Can't Get In' 'Boys Club' & Gal friends... network: that's astonishing. Not to mention the incredible 'Salaries' they're paying themselves.




Come on, the real reason Pia Marquard dropped Nancy, all during a quick and dirty change in management, was most probably because a combination of multiple reasons (which they think we do not see with their boilerplate official responses).
1) Jealous of this woman's beauty [okay, not so important here].
2) Jealous of her intelligence (because the daybreak people currently are not up to her level, sorry guys)
3) Unwanted gestures from management that are TOTALLY insensitive (faceless, unaccountable bureaucratic chumps, LYING TO US THAT SHE WAS INTERIM)
4) This is a classic vexatious behaviour and attack on people who push the bar.
I would file a complaint with the Working Standards Commission Nancy, with a combination of the above reasons filed with dates and timings of the events related to the law:
http://www.cnt.gouv.qc.ca/en/in-case-of/psychological-harassment-at-work/index.html (most relevant and important)
http://www.cnt.gouv.qc.ca/en/in-case-of/dismissal-not-made-for-good-and-sufficient-cause/index.html
http://www.cnt.gouv.qc.ca/en/in-case-of/unjustified-change-of-status/index.html

You are protected by the Labour Standards Act Nancy, even if you have a contract - when you are subject to subordination, fixed hours, and have to use CBC's equipment and not your own to do your job.

I was attacked for being too good a worker at the Caisse de Défauts (before their colossal train-wreck in fall of 2008) for many of the above reasons, so don't give up the fight Nancy (and our great readers here in support should prove that). Funny that when I reached out of the provincial coquille we live in, Microsoft gave me an MVP award and the best technical Database think-tank in Silicon Fen (Cambridge, UK) has published my articles in both FR and EN. Second article, on Change management,.coming up soon on simple-talk.com

CBC management are being clearly unprofessional with respect to Nancy Wood.
Pia, reinstate her in the morning, or I (and I hope WE, as I assume many here agree) shall formally request your resignation, this is disgusting, stop playing games with OUR MONEY.
We want Montreal's BEST in the morning, not the sloppy seconds.




    • disillusioned cbcer says:


Are you for real?

Hugo Shebbeare says:


DC - are you for real (?) or do you always write without actually revealing your name. Click on my link or Google me, if you want to see who I am.

Pia Marquard and Rob Renaud are violating the Labour Standards Act, from the information I have read in support of this great woman we know as Nancy Wood. If you were given a permanent job, then you have the right to stability and not unwanted stress of harassment about petty ratings, etc. CBC Managment has to right to make decisions, but they first must respect the Law, and it seems, from all I have read so far, they most certainly are not. En plus, c'est du mensonge, ce 'interim' jeux de bouc-emmisaire.




Let's not go crazy here. Wood isn't being fired, and her position as host of Daybreak was a contract that isn't being renewed. It's a dick move, but I don't think it was violating any law.




Don't be naïve, the Labour Standards Act exists for a reason. It’s up to her to use it as protection from the corporate dogs.



Fagstein, Sorry I have to agree with Hugh. They are breaking labor laws. It cannot be denied that they've put Woods into an impossible position. She was fired from her current position, without warning or reason; She is Obviously very popular with the listeners. I mean, can you recall ANY Media personality - getting this type of support from Montrealers? I sure as hell can't.

And what kind of a nightmare - working environment do you think is ahead for her at CBC? Is she going to be - demoted - hidden away - have her life made as uncomfortable as possible (as if it could get more uncomfortable, sigh). You know how it goes. The environment they have placed her in right now - is a very clear violation.



What's with the "hot blonde" and "jealous of Nancy's beauty" comments? (Except for the overly airbrushed CBC photos). Ms Wood is in her 40s, she has the wrinkles many of us middle-aged people have. She has a kind, pleasant face, but angular, not "conventionally beautiful". She looks fine - as if it matters one way or the other on radio - but is certainly not a Barbie-Doll type.

I certainly preferred her to the pompous René-Homier Roy on Radio-Canada, but now I find myself listening to the Radio-Canada morning show despite him, simply because the CBC one is currently such a mess.

I'm heartened you, Gazette staffers and Marie-Claire Lortie are raising this. I doubt CBC are breaking any labour laws: "la précarisation" is more an more the lot of many if not most of us in communications, publishing and related cultural industries. It will take a groundswell of public support for Ms Wood to be reinstated. The format problem's aren't her fault.



I am sorry Maria, Fagstien and to any others who do not understand what psychological harassment is in the workplace.
Nancy has suffered a great deal of unwanted anguish over this - Management have to wait for the end, or near end of the contract - and not in the middle of it, before pulling the rug out from underneath her feet - it's disgustingly vexatious and insensitive. I was told just past half way through my year long contact 'ton mandat à changé' whilst only a week later was replaced with a subservient cog doing the same tasks.
I think you should read the links I have provided to the details above and below.

And if you still don't understand how the govt enforces this law read my post : http://www.sqlservercentral.com/blogs/hugo/archive/2010/02/15/first-public-hearing-against-quebec-s-pension-fund-manager-the-beginning-of-proof-multiple-laws-were-violated-before-sabia-took-over.aspx
While at the Caisse, every possible workplace bullying trick in the book was pulled, and in the end I even had racial comments to deal with...only a shrink brought me back from the brink of suicide. I think we all need to support Nancy because she is a human being with feelings, CBC Management give us the impression they think she's just a doormat. More than not cool, illegal according to the Working Standards Commission.



I do not understand why people are getting on Fagstein's case for doing what he always does.

I haven't listened to Daybreak with Finnerty or Wood as host very much, I think they both suck, but I'm totally intrigued and interested on why the move was made and the politics and ratings issues behind it. Keep up the good fight Fagstein!

I hope whatever they do with Daybreak, it involves blowing up that tired formula and starting from scratch. It's so frustrating that TEAM 990 is the best English morning radio option right now.



  • Caroline says:


Here's the response I received to my email:

-----------------

First, we’d like to [Blah, Blah, Blah, same old message - meaning we don't care to answer any specific concerns...]

Thank you for listening to Daybreak.

Best,

Pia Marquard
Managing Director, Quebec




Caroline: That's the same reply everyone got. I did get one other reply from Pia, yesterday.I posted it and my reply on http://www.bullbullet.com Note: Site is still rough, haven't had TIME to set it up the way I'd like. The 'can of worms' is keeping me very busy.
Didi


Hugo - FB photo

Hugo Shebbeare If CBC Manag. were professional about this situation, they would have waited until near the end (and shown some restraint with respect to time), and have given her a proper chance to finish the mandate. http://tinyurl.com/ykwfz5g It is ENTIRELY within the Working Standards Commission’s right to REINSTATE Nancy if she was subject to conditions which make her protected under the Labour Standards Act (fixed hours, subordination, using CBC equipment to do her job). http://tinyurl.com/yzd3v63
They can pull whatever scapegoat they wish, this is not going away. (UPDATE: Nancy was given a nice job with the reporting team at Enquête, the French-Language investigative journalism Program on Radio-Canada)

She has clear reasons for a complaint if she wishes, and CBC Managment can swallow a big piece of HUMBLE PIE for treating this great host like DIRT.

Friday, December 18, 2009

Congratulations to UK Information Commissioner Christopher Graham for Taking Companies to Task About Protecting Data Privacy



The New Information Commissioner for the United Kingdom is Mr. Christopher Graham, he's working from Wilmslow, near Manchester, with a team of hundreds to bring responsibility back to private companies' [mis]management of customer data - and he already took on T-Mobile for a customer sell off by employees debacle that, which of course, they tried to conceal.  He's out to enforce Parliament's Data Protection Act, and he's sending out warrants and private investigators as he goes! Bravo, the Database Cops are coming and they are armed with the DPAJ - too bad QC couldn't follow along and enforce Bill C-198 amongst government institutions.


Hats off to the UK government's efforts to protect data the way it should be and reinforce data stewardship, for more details please see: http://www.computerweekly.com/Articles/2009/11/27/239490/ICO-publishes-guide-to-quash-business-ignorance-on-data.htm

Here's an excerpt from the BBC article, which states that T-Mobile's employees were simply: 'Exploiting data'

The Ministry of Justice has been consulting on tougher penalties for illegal trade in personal information.

The Data Protection Act bans the selling on of data without prior permission from the customer and a fine of £5,000 can be imposed following a successful prosecution."

It is amazing to see such a brave man publically speaking out against things that simply have to be stopped - data protection must be taken more seriously by the business community (as well as the government itself sometimes).  Environments that do not adhere to Auditing Compliance, the principle of least privileges or the Segregation of duties are a clear target for the Information Commissioner to start, and I will let him know personally :)

Please note that I am in transition to a permanent job currently with Canadian printing Giant Transcontinental, and am training on Oracle 11g, Release 2, thus I shall resume more frequent blogging shortly. Final French version of the DRP for SQL Server was submitted last week to Simple Talk also.





Troubleshooting SQL Server Database Mirroring Problems with Best Practices: Requirements, and the 64-bit Benefit

As mentioned in previous disaster recovery posts and article, during my current mandate I have been tasked with what will ultimately be mirroring between redundant data centres. Mirroring has been chosen thanks to its easy setup and automatic failover option. The following is an overview of what should be taken care of to ensure a stable Mirroring setup.

Prerequisites for Mirroring are that you ensure that your database is optimised already, because mirroring an unoptimised database is just double the potential problems (in terms of file space).  In this way, expected disk space growth should be analysed thoroughly.   As for the build level, I am just waiting to apply SQL 2005 sp3, cu6(?) before running a mirrored set up in production. I figure having the same highest-available build level is the best way to start a mirroing infrastructure.

Initially there were rumours that there was a maximum value to the number of databases mirrored, but that turned out to only be applicable to 32-bit systems (which is a platform you probably woudn't wan to have heavily used databases on still anyway), which has a limit of ten. On 64-bit database systems there is no documented limit, therefore instances that hold many databases are without issue, apart from the typical I/O, network, and processor utilisation.  In 2008, there has been an improvment on the compression of the log before it is applied to the mirror, but after testing with three times the average traffic with SQL Stress and RedGate's Data Generator, e.g. pumping three million inserts across with a bunch of large selects on the worst tables, we had only a max wait time to apply the log of only 1.2 seconds as the exception, whilst the Database Mirroring Monitor reported mostly under 200ms.

Start with a general verification that the ports are open on the remote EndPoint by running from the Run dialog box/cmd line:
telnet RemoteServerName 5022

Verifying Port Availability - When you are configuring the network for a database mirroring session, make sure the database mirroring endpoint of each server instance is used by only the database mirroring process. If another process is listening on the port assigned to a database mirroring endpoint, the database mirroring processes of the other server instances cannot connect to the endpoint.

To display all the ports on which a Windows-based server is listening, use the netstat command-prompt utility. YOU can identify the listening ports and the processes that have those ports opened, follow these steps:
1.
       
Obtain the process ID of the respective instance of SQL Server, connect to that instance and use the following Transact-SQL statement:
SELECT SERVERPROPERTY('ProcessID')
2.
       
Match the process ID with the output of the following netstat command:
netstat -ano

If ever you arrive with an error like this: Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'.  [CLIENT: IPaddress...]
This is probably happening because your encryption is setup differently on the mirror/principal. The system table sys.database_mirroring_endpoints will show different algorithms- Mirror-encryption_algorithm_desc=RC4 and Principal- encryption_algorithm_desc=NONE
Therefore it is best to issue a:
Drop Endpoint Mirroring 

command on both mirroring parters (to start from scratch, beware, this blows away all mirroring on the endpoing), so that when you try and set up mirroring again, you are not stopped by a difference regarding encryption.  If DROP ENDPOINT is not possible, meaning you have other database Mirroring established already with another server, and then the other option would have been to run ALTER ENDPOINT on both instances For more information regarding Mirroring, check out the System Tables:select * from sys.database_mirroring_endpoints

SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, e.connection_auth_desc 
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t ON e.endpoint_id = t.endpoint_id
select
* from sys.endpoints
select
* from sys.database_mirroring where mirroring_state is not null

-- which ones are in the mirroring state

 --to see who has granted Mirroring and the grantee

SELECT EP.name, SP.STATE,  CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))  AS GRANTOR, SP.TYPE AS PERMISSION, CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) AS GRANTEE 
FROM sys.server_permissions SP , sys.endpoints EPWHERE SP.major_id = EP.endpoint_idORDER BY Permission,grantor, grantee;

  I shall be updating this further once I have had success in production. I hope this helps, if you are aiming on taking advantage of  this great functionality



Notes on a Migration from Oracle to SQL Server



I have to admit, when asked to perform a migration from Oracle to SQL Server over the past couple of months, I was a bit apprehensive.  After having passed an all-nighter to finish the project off recently, starting at 3pm, and finally heading home around 6:30am, there was an unsuccessful attempt by the ‘I don’t use a plan for my project’ person on the other end of the line to get me to turn around to fix a single column size because one report did not work (which of course, was not noticed in pre-production/test environment, and later was fixed without my intervention). Luck won at that stage, but it is not always that way as a DBA without doubt.


First of all, to anyone who has just worked two shifts in a row; do not attempt to call them back to fix a minor thing (respect?), especially a column size fix that at least two other people (in this case) can do, not just because I’m a windging, grumpy, even zombie-like DBA at 6am in the morning, but most importantly, the due to the high risk of messing something up in production.  There are physical limitations that we must respect of any employee or contractor performing their duties while manipulating a critical production database instance - sleep is not negotiable. This should be needless to say, but after repetitively being asked to forego this basic need, I am blogging it!  

Secondly, as with all migrations, one should constantly try and adhere to the keep it simple rule (K.I.S.S.) – this migration was no exception, so what we did from the very beginning was create insert scripts of all the data into the tables (not a huge database, in the tens of megabytes only), since the schema export was already done for us by a vendor (to which I only had to do minor tweaks to appreciatively).  Before actually going through each table insert script one by one to adjust the fully qualified table names, add Set Identity_Insert On/off statements, with a quick truncate before the begin tran/inserts/commit batch, I had scripted out, in a previous step, all the drop/create foreign key and constraints statements to bring all the data in quickly without per-table FK/Constraint drop/recreation.

Finally, I created a checklist with all the table row counts printed out for manual check-off as I made progress through the 50+ tables to load into SQL Server 2008.  It took all night, as mentioned before, so running in the background Youtube’s links to three long series on Franco-Norman history, around and just after the Invasion, permitted my non-technical historical enrichment to benefit at the same time J

Now, I can honestly say that I look forward to migrating more databases over from Oracle.

  

A DBA's Role with Respect to Improving Microsoft Dynamics CRM 4.0 Performance

1linerForward: here's a webinar from Microsoft regarding this subject, the following is a summary of what was required to improve CRM performance.  

Microsoft's Customer Relationship Management platform runs on SQL Server and falls under most of the typical performance improvement techniques (and should be regularly checked with tools like Activity Monitor, or Server Statistics and Performance), but a few things came up for the DBA supporting CRM to focus on. Please note, this work is performed closely with your local friendly CRM application server system administrator.


First, a major server configuration preference we noticed, that may be different from your typical parameters, was setting the maximum degree of parallelism to 1. Memory wise, CRM is pretty memory intensive, so a typical instance should have at least 12GB to 18GB physical RAM available if the application is [hopefully since it is quite good] widely used.



As far as the CRM system admin's help is concerned, there are two particular optimisations regarding the AsynchOperations table (and its two related tables) that need to be done hand-in-hand with the Microsoft-created database optimisation script below, they are: http://support.microsoft.com/kb/957871/EN-US/ and http://support.microsoft.com/kb/968755/


Before you run the script mentioned below in this post, co-ordinate with your System Admin to make sure they stop the Microsoft CRM Asynchronous Processing Service (run during maintenance windows) and take a database backup just before purging the typical hundreds of thousands of records, thus assuming this would be the first time performed, and in the future by regular maintenance job.



USE [master]
GO
ALTER DATABASE [ORGNAME_MSCRM] SET RECOVERY SIMPLE WITH NO_WAIT
GO
-- now run the cleanup of the AsyncOperationBase table  
-- reference http://support.microsoft.com/kb/968520
-- Stop the Microsoft CRM Asynchronous Processing Service while you run this script. 
use [ORGNAME_MSCRM]
go 
IF EXISTS (SELECT name from sys.indexes
                  WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
      begin tran
      insert into @DeletedAsyncRowsTable(AsyncOperationId)
      Select top (@DeleteRowCount) AsyncOperationId
      from AsyncOperationBase
      where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)     
 
      Select @rowCount = 0
      Select @rowCount = count(*) from @DeletedAsyncRowsTable
      select @continue = case when @rowCount <= 0 then 0 else 1 end     
 
        if (@continue = 1)
        begin
            delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
            where W.AsyncOperationId = d.AsyncOperationId
            
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
            where B.AsyncOperationId = d.AsyncOperationId
 
            delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
            where A.AsyncOperationId = d.AsyncOperationId            
 
            delete @DeletedAsyncRowsTable
      end
 
      commit
end

--Drop the Index on AsyncOperationBase

DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
--- after testing this being run in pre-prod, it took 17 hours

-- Rebuild Indexes & Update Statistics on AsyncOperationBase Table 
ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
GO 
-- Rebuild Indexes & Update Statistics on WorkflowLogBase Table 
ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
-- final optimisation, although done automatically usually
UPDATE STATISTICS [dbo].[AsyncOperationBase] WITH FULLSCAN
UPDATE STATISTICS [dbo].[DuplicateRecordBase] WITH FULLSCAN
UPDATE STATISTICS [dbo].[BulkDeleteOperationBase] WITH FULLSCAN
UPDATE STATISTICS [dbo].[WorkflowCompletedScopeBase] WITH FULLSCAN
UPDATE STATISTICS [dbo].[WorkflowLogBase] WITH FULLSCAN
UPDATE STATISTICS [dbo].[WorkflowWaitSubscriptionBase] WITH FULLSCAN

-- after everything is finished
USE [master]
GO
ALTER DATABASE [ORGNAME_MSCRM] SET RECOVERY Full WITH NO_WAIT
GO



 Thousand Islands National Park, Canada


For more information regarding a DBA's role in CRM optimisation:
Microsoft Dynamics CRM 4.0 Performance Toolkit from the Team Blog:  http://blogs.msdn.com/crm/archive/2008/02/29/microsoft-dynamicstm-crm-4-0-performance-toolkit.aspx

The performance toolkit itself - http://www.codeplex.com/crmperftoolkit