Connection String: MSCRM Data Connector Connection String

Note, that if the CRM SRS Data Connector is not installed on the SRS Server, please install it and verify it is properly configured before choosing the Data Source Type above.

If the Report Server login dialogue box shows up: "Type or enter a user name and password to access the data source: ", you are going to need to identify input the user's SystemUserID GUID and their Organization GUID.  To help us determine this, please run the following statement against your organization database:

SELECT SystemUserId,OrganizationId, FullName FROM SystemUserBase WHERE Domain Name = 'domain\userid'

For "Log In Name:", please copy the SystemUserId GUID from the query and paste it in the text box.

For "Password:", please copy the OrganizationId GUID from the query and paste it in the text box.

At this point if the reports do not run then transfer your case to the SQL Server Reporting Services team ( BIG THANKS TO THEM for their help! ) to continue troubleshooting. Or ...(other solution coming, just need to upload a few files for ya).

Troubleshooting CRM/SSRS should also include a look at the Event Viewer, although at times, the ‘Please contact Microsoft CRM Dynamics Team for support’ or Reporting Services error explains little - thus check your SRS log files on the Reporting server in the following folder:

Drive:\Program Files\Microsoft SQL Server\MSSQL.*\Reporting Services\LogFiles

If you have not read the Master of Phrenetic Phoughts’ article, please do so here. I enjoyed very much the illustration of Brunel employed in the beginning, a family favourite for the list of top Britons.

Phil gives us an interesting anecdote of person who was not so discreet about crashing a company car while trying to shrewdly schmooze his way up the corporate ladder.

I’d like to explain more eloquently why I am starting with this post in the most abrupt (can you tell my Mother is from Yorkshire) way, but cannot right now, so jumping right in…

The best policy to recover from a terrible set of circumstances (whether your own for or not), as has been said by many, is to be completely honest and use tact when responding to difficult questions.  When someone is averse to handling tough questions, and commences by blaming others within the first few strokes, subsequently their integrity is questionable. It is up to those who have visibility, often whether they like it or not - being from a different part of the country, holding a different accent, being tall and blonde(!) – to act as examples because their actions are easily replicated.  As certified database professionals early on in their career, and even experienced Most Valuable Professionals at a later stage, one should treat their coworkers with respect.

In front of your team, or for those of you who work with in today’s theoretically ‘flat’ environments (or Matrix organisations Vs Departmental), this is especially important.  Owning up to your mistakes is paramount, and when you notice something that you have done wrong before anyone else sees it, your chances of obtaining the strength of the circle of trust are greatest when you admit the fault in front of everyone and indicate the best way to avoid a reoccurrence of the gaffe.  Conversely, if you make a mistake and then an in-house competitor runs with it to blatantly defame you (whether or not with vexatious intent – subconsciously often), then one can distinguish right away who is not a team player.  That is unless the work environment has develop into so rotten it has become the norm because of complacent human resource departments, or in the worst case scenario, rewarded with promotion.  

When there is a situation when someone repetitively makes serious mistakes that drag the rest of the team down, then a written trail has to be left – and not a series of repetitive verbal assaults, which, in turn, indicate a distinct lack or professionalism – unless your goal is to have yelling matches commonplace!? A one off heated discussion is unavoidable at times and should be treated as such.

Where I disagree with Phil is the mysterious aspect of visibility. I do not see how it is mysterious – as long as you are always able to turn a usually negative situation into positive one and that it is projected throughout your relevant networks and writing, whilst maintaining a straight-forward attitude, even if it is idealistic, then it is not so mysterious.J

This morning, Haiti was shaken yet again by another aftershock of the recent devastating earthquake.  

There are some Februarys in Vancouver, while growing up, when I can remember wearing shorts and watching plants flowering, therefore with the latest news that the Vancouver Olympic Committee have just closed Cypress Mountain to regular skiers/boarders to preserve snow, it should not come as a surprise. Cypress is sometimes better known to locals by its old name Cypress Bowl (just see how it looks in the middle of this panoramic below to see why). Just this morning, that warmer weather rolled into Montreal too, meaning we are hovering around 0-2 degrees Celsius instead of the habitual -10/15.

This is just a temporary glitch before the Olympics arrive and I believe Plan B is sufficient - as thoroughly described here by the Globe and Mail, Canada's premier newspaper.  In a few weeks, a real cold snap could hit and since there is so much precipitation on what is often know as the Rain Coast (Seattle suffers from this too), snow could easily return in abundance. Another glitch just mentioned in the Canadian Media is the fact that Intrawest (owners of Whistler-Blackcomb resort ), after having defaulted on several loan payments - Tourism also hit hard by the Global Recession - is up for auction! Just as the Olympics has arrived to save the resort town, the owners of the main hills are going out of business...

A very common confusion for visitors to British Columbia, justifiably, is the fact that Vancouver is not on Vancouver Island.  Victoria is the capital of BC and is located on the Southern tip of Vancouver Island, while Vancouver is on what is known as the Lower Mainland (or more commonly being called Sea to Sky Country, after the famous Highway 99).  Railroad Baron and Canadian Pacific Railway President Sir William Cornellius Van Horne, late in the nineteen century, used his clout to convince Vancouver city councillors that Granville (one of the City’s main streets still), the city’s original name, would not be as much of an attraction marketing-wise to get the potential settlers out West on the train across the country if the already known Vancouver title wasn’t employed. Perhaps also, Van Horne’s Dutch ancestry motivated him, since the great English explorer Capitain George Vancouver’s grand parents were from Coevorden in Friesland, a city my Father and I visited whilst living in Brussels, Belgium - a place I can recommend all Vancouverites to visit.

For official information from the Vancouver Olympic Committee (VANOC) please see their site.

To enjoy a time lapse video of Vancouver's City Scape check out Linda Ganzini's great work.

Just this past week on Tuesday afternoon (a link to video of Parliament online, please forward to about half way through), the United Kingdom's Information Commissioner Christopher Graham spoke in the Wilson Room for the House of Commons Justice Committee.  He was flanked by Stephen McCartney, Head of Data Protection Promotion, from the Information Commissioner’s Office. As mentioned in a previous post just last month, the ICO has been lobbying extensively for fines to deter serious data breaches - and it gives the impression of working quite well, since on their front page, the ICO "expects its new power to issue monetary penalties to come into force on 6 April 2010, allowing the ICO to serve notices requiring organisations to pay up to £500,000 [$1M approx.] for serious breaches of the Data Protection Act." In other words, HUGE fines: finally some real deterrents to the list brokers/reseller industry.

In the Committee, it was being recommended that the European Union enforce data protection, but not to jump into it without a comprehensive approach, in other words, reculer pour mieux sauter as Mr Graham states; take a step back first, analyse the nuances/realities of how data is exploited, and then codify the law fittingly. The HOC Justice Committee met to avoid the patchwork of current legislation involved, as well as taking into consideration the fact that [Roman and Napoleonic Civil] Codified Law are very different from Common Law practiced in Great Britain. Civil versus Common Law have very different approaches to managing data too, the former being considered in Common Law countries as unnecessarily pragmatic – too literal, too much bureaucratic overhead, whilst Canada and the United States tend to take too much of a freely flowing data approach without thinking of privacy and security considerations from the onset. In fact, the European Commission went to the extreme as to sue Sweden for its lack of action regarding the E.U. Data Retention Directive.

The Face of Data Protection in the United Kingdom: Christopher Graham - see him at the Data Protection Officer Conference on March 3^rd, 2010

 As one can see in the video footage (forward to 26 minutes), Information Commissioner Graham is a man of candour as was his Grandfather Lance. Sir Lancelot Graham was known as idealistic, indefatigably hard-working and self-disciplined. He was not only a Governor of Sind (Pakistani province where Karachi is the capitol) before the partitioning of India, but also President of the Commonwealth Society.

It is clear action must be taken to discourage serious breaches of the Data Protection Act, but one of the first fundamental questions (referred to in the List of Data Protection Principles), as mentioned by Mr Stephen McCartney, Information Commissioner Graham’s experienced colleague, should be what is the data being used for? Ultimately the goal is to prevent carelessness (i.e. not encrypting, or at the least putting a password on a backup placed on easily readable media) with respect to the management of personal data within organisations, a space where the ICO has been very active lately, and even provides A Guide For Data Protection in Plain English on its site. 

In sum, in the European Union, there is a need to clarify laws that are related to the management of information, and it is great to see Information Commissioner Graham giving guidance openly. We have to treat Data as the precious resource it deserves in our information-based society, merely because its mismanagement can cost us all, and not just in the E.U. With respect to a certain pension fund data management disaster I witnessed firsthand, it led, in part, to the loss of billions of dollars.  

If you are asked to be a Database Cop, and you follow the code of ethics of your organisation, especially a public organisation (i.e. working by the book), erstwhile assuming that you are in a hostile environment, rampant with Workplace Bullying – when, or better yet, where is a DBA supposed to stop when protecting public data?  I am very passionate about our job as protector of the data - and for more on this subject, please read Brad McGehee’s DBA Code of Conduct because if you see multiple faults, and maybe four as I did, then you should do all you can to avoid a trainwreck for your respective organisation because just like on an Airplane, it could only takes two chains of error before the plane hits the ground. Brad lays out a clear path to follow. Also, pay special attention perhaps to my comment at the end which is in reference to the two government organizations who are working on my behalf (and provided with stacks of evidence also, being as municieux as a DBA should be) to take judicial action in opposition to our state pension plans’ violation of both Federal and Provincial laws, followed by Human Rights violations.  

Microsoft has just showed their appreciation this week by passing on the Most Valuable Professional Award for SQL Server. I have to admit that I thought it just slipped by since I didn't hear from my gracious guide, Simran Chaudry, MVP Lead for Canada, until I finally got around to catching up with my e-mail of three days today. I have needed some downtime after travelling three times within the past month or so - first of the three trips was to Cambridge as you can see below. There was lot of family to catch up with there, plus with the Simple Talk editors at a certain famous Dim Sum place on Regent Street.

Next up...The  face of data protection in the United Kingdom: Chris Graham, and his recent Parliamentary Committee Hearing.

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.

For further reading: Security Breaches by Sector and How Data was Disclosed courtesy of the Information Commissioner's Office. 

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 EP
WHERE SP.major_id = EP.endpoint_id
ORDER 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

References and Recommendations from MSDN: http://msdn.microsoft.com/en-us/library/ms366349(SQL.90,printer).aspx

A great step by step script-based tutorial: http://www.mssqltips.com/tip.asp?tip=1705

 Dell’s guide to Mirroring

I am very impressed with Michel David’s work, and openly endorse much of his great piece of work at MontrealCityState.ca – the following is personal take on his propositions, or simple re-iteration, and if I have more to add, or issue with the specific point (which I shall throw in anecdotes to make a point, or give specific technical experience for persuasion when necessary).  The main difference I would like to see as a result of this proposal is that we look at this separation of Montreal from the rest of Quebec as a Logical one, in addition to the obvious Physical separation that exists. If Quebeckers want a large city that is more Quebec City-like in mentality, then they would be happier in Laval(?), but we must respect Montreal’s multi-cultural constitution and allow it to flourish without the constant negative judgementalism from the rest of Quebec, which frets over the city’s diversity and anguishes over losing the French language (as mentioned before, still here after 400 years, so why are discriminatory laws from the 70s still existing – as if many politicians are still stuck in the seventies, and forgetting to concentrate key issues such as education). As immigrants arrive, they are more likely to be favourable to the Lingua Franca, which some in the government view as an attack on the French Language, or an attack on the [broken] Quebec Model, but this passion is misplaced in the Montreal context due to a combination of many of the following reasons.

Negative Consequences of Separatist Movements and their Repetitive Attempts to de-Anglicise Montreal Completely

I am not going to justify and give lengthily discussion on the exodus of English-speaking Canadians (hundreds of thousands of Anglo-Quebeckers), that is covered quite effectively by a series of YouTube videos (many individuals interviewed, for those in QC spread lies and think that Anglos who left did it out of their own volition ).  Early last Spring, about the same time as Michel David was composing this work, I spoke out in a letter to Government Institutions in Quebec, especially ex-Premier Bernard Landry, about workplace discrimination by race/ethnicity and psychological harassment.

I am an Anglo-Quebecker, who even married in Ayer’s Cliff, just eight kilometres down the road from where Great Grandfather Robert Austin was married in the Eastern Townships (Coaticook). Although born in Vancouver, and went to bilingual school in the beginning,  I came back East in `98 to return to some centuries-old roots in Quebec, although only really becoming fluent in French in Brussels, Belgium during the four years at Vesalius College, VUB, between `94-8.  Initially, my 'boot camp' in French was at the Université Libre de Bruxelles in the Institut Phonétique, but the real fluency came mostly by hanging out with the Motquin family here below in Freyr, Rock Climbing by the side of the great Meuse River that runs through much of North Western Europe:

Climbing with Serge, Ben and Lilliane really helped learning the language quick, although it puts a heavy hint on the vocabulary related to climbing first: La voie, le relais, s'assurer, le baudrier, le noeud huit, les chaussons...etc.  All this say that I feel rather linked to this land since it has the best of Europe and Canada at the same time for me personally - we can use three languages a day here fluently, for me that`s a privilege.

Now back to MontreaCityState.ca - I agree very much with the statements regarding small business and start ups on page seven, although costs are very low, it is obvious what creativity and innovation with respect to business is not helped much by the government, since we have widespread mafia-like corruption running through the officeplace or blatant unprofessionalism, and if it is not that, we are subject (coming from a profitable 6 years + small biz myself) to this because even in the best conditions are expected to work on steam, meaning absolutely bank overdraught for up to six months at a time – even when you are on a government project.

This is fundamentally flawed, since now that it has come to a crisis (greatest financial hiccup for a century), all those who forced others to run on fumes have had their respective revenue sources be forcefully placed on hold for what it seems like about six months - imagine not being paid in full for six months, and somehow surviving with only twenty percent of your earnings given to you at a time? This is the pressure small businesses have to endure - because veut veut pas the attitude is that someone else will pay for your problems (see the points behind the great work Illusion Tranquille). This goes back to another point which is inconturnable here in Quebec:  sometimes you come across those whose mentality is founded upon a notion that they are entitled to make decisions for you (la raison pour laquelle je dit que le gouvernement nous traite d’esclaves assez souvent, et quand on retire nos droits, ce n’est plus une société libre, pensez au francophones qui veulent mettre les élèves en école en anglais laissez faire la Loi 104 SVP), as if it’s okay to violate your rights and unapologetically at that (and people wonder why there are such shameful outmigration levels, even deplored by guests at the Sunday-night institution Tout le Monde en Parle hosted by the great Guy A. LePage).  My most serious anecdote involves a [now thrown out] Senior Vice President at the Caisse de Défauts, who not only hinted that minorities did not have the right to work for the Government (are you surprised Landry ranted on about Anglos working for the CDP(?)still people?), but harassed many persons until they left and even had the audacity to say loudly from their open offices how easy it is to kick out the English. And people wonder why there are ‘Angry’phones? When will these ‘which side are you on’ divisions ever end? What possible way of foreward thinking would lead anyone to envisage that you can get away with such socially unacceptable behaviour?

Horror Stories Aside, Lessons Learned – Montreal’s double-whammy Neverdum Post-Mortem

How can we apply these principles and arguments brought forth by Mr. David’s great work? Think of the current situation and what brought it about. Currently, a re-elected Mayor for the third time who has been trying to stand up to the inherent massive corruption system at the risk of his life, although turning a blind eye to is for long.  The circumstances are so ominous that even The Economist took notice to comment about Graft, that has established itself as, in one instance, a network of 14 companies for city construction contracts (this system, in actual fact, a mafia organization chiefly, was openly exposed by a famous whistleblower, our brave Mr. Beaudry). More persons have to step forward to put an end to this, and by writing I hope to contribute.

Diversity has to be allowed in Montreal to prosper as it was before the referendums and separatists ruined outside investment, especially after the second referendum in 1995 (see Montreal reborn) without the projection of the modèle Québécois onto the City of Montreal (and many bilingual regions, in the Greater Montreal Area).

When Michel David mentions governance, he is hitting the mark, and I agree with him clearly about the Negative Interdependence Quebec has on provinces surrounding it, resulting from the political instability due to the failed threat of separation, Anglo Exodus, Separatist Propaganda (thanks Daniel Laprès for your note) and business’ HQs departure to Toronto.  A common way to really create wealth here (and one of the only ways it seems) is by Real Estate, thus, no wonder the real booming market we are seeing in Montreal now that the interest rates have stayed low. This is another counter-current to the economic downturn, the reason that we are seeing construction and renovation (Federal govt.s Action Plan induced also), since many people who are able to sell (since we have a minimum 25% mortgage down payment requirement by Canadian banks, and applauded by economists as a reason our banking system is strong still, quite different from the huge Foreclosure Crisis in the U.S.A).

How Not to Promote Growth - Promoting the Chamber of Commerce as Unilingual

To be honest, despite loving to live in French most of the time, and even Blogging in French, the Chamber of Commerce has really turned me off with its repetitive message that it wants a French Only Business environment in Montreal (hence the reasons behind the negative growth of the networks here, promoting division instead of the actual bilingual workplace, which was even supported by the Premier), meaning that obviously we have to be ignored because we speak another language (wtphrack?) – of course, this is a worst case scenario, but I could provide several anecdotes.  Is this not yet another sociological propaganda message to not create a pro-diversity board of trade, but a francophone only one?  Could they be more blatant with having the Premier okay the message too?  Does Charest (with his pro-nationalist-goggles on) even realise what this means to the large percentage of businesses that operate in English due to their out of province ties, or the fact that they might be run by 20% of the island population who are native English Speakers, or the other 30-35% of 'allophones' who use English?  Does this not mean that we have business networks, that control the flow of money, when it trickles down from the government or their big untendered contracts (where minorities are represented only in pathetic numbers, less than a percentage point say twenty percent Anglophone population?), and minorities are cut off from the source of this wealth, as if to accept the majority's sense of entitlement?  David states that ‘…to protect the French language and culture and to nurture a Québécois set of values’ from the top of the government down means that ‘for Montreal, however, a modern, dynamic city, this defensive and past-oriented strategy has been toxic: Montreal needs a very different forward-looking, proactive strategy.’  I have mentioned this stuck in the sixties/seventies mentality thoroughly in a post to Government Institutions last Spring, and I am glad to see someone like this man has independently come to much of the same conclusions!

Time to Patch up Some Wounds and Establish Equality Amongst the Island's Inhabitants

For this whole strategy to work, might I suggest that there be reconciliation with the Anglophone community who has been the scapegoat for much of the post Quiet Revolution, now (Grand Illusion) illusion tranquille era.  Many, such as myself, are uneasily happy with declaration of the nation we all knew existed in our multi-nation state, because of the exodus from the province and the exclusion of French Canadians from the boundaries of the nation they are supposed to be a part of too, but have caused a clear internal rift since the pro-independence movement now in decline thanks to acceptance of nationhood.   Many splinter extremist groups have resulted, which attempt to attract attention to their lost sovereigntist cause by screaming ever louder and louder (hence the term Bluenecks, QC’s Redneck equivalent , described thoroughly here), even to the point of threatening the Family of the head of State touring the Country (HRH Prince Charles was harassed by the Réseau de Résistance Québécois and Anti-Royalist League while trying to give thanks to Canadians who served in Afganistan), or bringing back sloppy seconds from the PQ songbook or demonising anything English (even education in EN) that has any open character flaw (as if nobody can be perfect, except for those from the their own side of history…a cheap tactic to get the Anglophobes to follow easily and their obvious disdain). In France, one only has to see LePenn’s speeches and attitude to understand this strain of xenophobia, even Sarkozy was harassed by persons who share this mentality clanniste before he won the presidency.

This mans' comment regarding the Bill 104 contraversy summarizes the mistreatment of the Anglophone community very well:
Damien
November 13, 2009 - 2:23 PM

The Way Forward, Break Montreal’s Immobile State

As mentioned before I shall always remain optimistic and straight forward as Mr. David, because maintaining a cheerful disposition, despite being under immense financial pressure due to the highest level of taxation in North America, and multiple attacks on the welfare of my family due to exposure by contract (with a dose of minority mistreatment, which typical during an economic downturn anywhere).  His goals are the same as mine, since we both love this city despite its mismanagement, we need to brake away from the status quo by clearly pointing out the blockage (immobilisme) that comes from the culture of français avant tout entitlement currently instilled, to a proactive, change-driven entrepreneurial culture, that creates autonomous individuals instead of those dependent on the state for hand-outs (for projects, etc.). Why doesn’t the Caisse de Défauts take an example from success and hire street smart entrepreneurs instead of complacent, incompetent permanents that reinforce state dependence.  It would result in, as Michel David states on page twelve, in the wide-spread bottom-up individual or enterprise initiative, by allowing the money to flow to persons who are creating the jobs (small business in general creates eighty percent of jobs).

Also, one of the incremental strategy examples would be the positive reinforcement of the mandate of the Working Standards Commission, since this major improvement in the workplace will be a great benefit to diversity.  Minorities would not have to live in fear of unwanted repetitive harassment by the 'Bluenecks' in the office, as I have sustained multiple times - unfortunately, due to the recession, workplace tensions have risen significantly...

Montreal is Distinct from the Rest of the Province - Don’t Crimp It’s Style!

A defensive stance for the physical island of Montreal should not be taken as is done for the rest of QC, trying to survive in a Anglo sea of contiguous states.  The Island has always been shared territory, but the nationalist / franco-separatist zealots (although their numbers are very few, as we noticed from the less than one hundred that showed up to protest in front of Brent Tyler’s Office over the fight against Bill 104) are trying to re-write history continuously as if to justify the ethnic cleansing of Anglos from the Island (gladly returning, a revived community with population levels of just under 800,000), to the point of convincing multi-nationals that English isn’t worth posting anymore in retail establishments (even if multi-national companies!).  This is quite disgusting considering Canada is a bilingual country.  These wrongs have to be reversed, thus my awareness and standing up to the sources of this immobile bullydom.

Another aspect Michel focuses on is the proverb ‘less liberty, less wealth,’ Tasha Kheirriddin pointed out Les Affaires in August last year. One thing is for sure, the anti-English repressive laws here, often only of a psychological nature aimed at forcing a homogenous society (simple anecdote, Louis Harel’s attack on what she called Ghettos on the Island of Montreal), are enough to turn investment away from Quebec not to mention huge CDPQ losses (47BN and counting).  In this way, if the top of the heap cannot even get it right, despite 5/7 things done perfectly well, not willing to admit fault ('denial', not only a river in Egypt) for obvious things to those who have survived the ridiculous mismanaged and vicious government environments.  Who would willingly go to set up a business in a place where they risk harassment and being fined for being different from the provincial mould? Why does the main supporter behind Quebec Inc., the Caisse de Depot, give such preferential treatment to one ethnicity as opposed to all Quebeckers?  I’m sorry, but who made up all these rules (ah, right only majority’s elite), and convince the majority to follow this like fools.  Does this mean stealing opportunities from those who do not fit the homogenous mould of the rest of Quebec is justified? It’s not to say that all business that has been helped by the CDP isn’t good, but the cumulative effect of restrictions based on ethnicity, favouritism to pro-sovereigntists (the CDPQ being a bastion set up by Parizeau himself), high taxes significantly contributes, the widespread mafia-like construction cartel, and lack of internal investment into the province means we have an immobile economy (Mr David describes this on page 12/3 Immobilisme again).  Honestly, it feels like we are living in a quasi bullydom sometimes after have been thrust upon several persons of various degrees – where schoolyard politics are the norm in the government and in a few private companies, with the tools that support their control.

Solutions, Solutions, Solutions

The solutions to this blockus are bilingualism, forced minority quotas by the Working Standards Commission (Commission des normes du travail) and lower taxes on the island (similar to what was done to promote people to move back onto the island at the start of the millennium), and the promotion of diversity, by perhaps rekindling of the Vivre Ensemble / Living Together, pro-diversity campaigns of the past by the government (thinking of the posters still up in Serrano's resto in the Mile-End of Montreal, on St-Zotique Street).

The new proposed Model for Montreal, is to, firstly, respect the city’s history (meaning a return of the great Scottish, Irish and English history ALSO, the other three corners of the flag! please), as it was a great city and can become once again once we reach out to the rest of the world.  We have to minimise the influence of what has left us in this immobile state (see above), and in turn support wealth creation, entrepreneurialism, and international orientation (instead of only inward looking, as in the modèle Québécois – which can work for many, but should not be treated as the only route to success).   Celui qui cri le plus fort (the one screaming the loudest) is not the way to set examples, it’s the way to establish Losership, this must be dropped to focus on the best ideas instead (Quebec’s equivalent of the Metro Vs. Retro struggle).

I would support his proposal about uniting Montreal’s various politically, but the lack of trust in the city is at an all time high with another scandal exposed monthly. When diversity is essentially enforced in the government workplace from the top down, then maybe we can talk about this suggestion, until then, the demerged cities will not be keen on allowing another forced merger. Those who have been labelled as Allophones or Anglophones must be filtered from the infamous provincial modèle québécois – unless the ethnocentric Ville de Montréal accept bilingualism and the right to a minority veto to ensure wake is not damaging to the point of ethnic cleansing, as we have lived through in the past, because without actions in this direction, the decline of the city and the province shall continue.

The past couple of days we have had some fun with a Team Building exercise by means of designing our Pumpkin for an internal competition between teams.  Unfortunately we did not win anything, nor even make the top three, but we certainly did have some fun – and watching this Japanese toy bunny's LED light change colors has made for a few wicked photos.  Enjoy :)

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.

Here I go again, trying to finish up the translation. Thank you very much to local MVP Eric Moreau for his help fixing my mistakes so far :)Here's a complete final draught en français.

Configuration de l'instance SQL Serveur

Nos serveurs exploitent la version 64-bit de moteur de la base de données SQL Serveur 2005, avec la deuxième mise à jour applicative majeure appliquée (Service Pack 2) et la troisième mise à jour cumulatif aussi appliqué même si en ce moment SP3 et CU5 sont disponibles, nous avons réussi avec ses versions antérieures en production. Le type de collation est configuré à Latin1_General_CI_AS ou bien, SQL_Latin1_General_CP1_CI_AS – dans les deux cas, nous suggérons porter attention aux accents (AS=Accent Sensitive).  Bien sûr, si on refait cet environnement de relève maintenant, on profitera de Service Pack 3 avec la cinquième mise à jour accumulé installé sur nos instances en production – ce qui est fortement recommandé surement, est de mettre les derniers correctifs en production suite à son roulement avec succès en développement et test/pre-prod.

Les renseignements détaillés à propos de chacun des serveurs (SQL1 & SQL2) de la base de données inclus dans ce plan de relève est disponible dans un fichier aide compilé sur disque dur locale:  par exemple D:\DRP\NomduServeur.chm (i.e. soit très facile à trouver)

Détails critiques à propos des bases de données usagers

1. Liste des bases de données

BD1

BD2

…
À noter: nous allons pas toucher les bases de données systèmes (master, msdb, model, ou temp), qui sont sauvegardées sur une base régulière et seront copiés par Robocopy, mais pas restaurés sur le serveur de relève directement.

2. Plan de maintien de la base de données et restitution automatique
   En générale,
   our database restore plan will reflect exactly the backup schedule and wait for backups to finish by querying the metadata from the production server. The restore jobs will check to see if the days’ full backup has completed (or daily diff.) using the backupset.backup_finish_date  column.  Once we see that Full backup has been completed on the production server, we copy the backupfile over to the hot standby server. In the second step of the job, we continue to execute the code from the appropriate usp_DB_restore combined with the metadata extraction from the system tables
3. Plan de maintien de la base de données en production
   
   

   Nom du processus d’automatisés	Description de processus	fréq.	heure cédulé
   SauvgardeComplet_BD1	sauvegarde BD complet1	Hebdo	dimanche 6h
   SauvgardeComplet_BD2	sauvegarde BD complet2	Hebdo	dimanche 6h30
   …	…	…	…

   4.   Processus automatique sur le serveur de relève
   
   
   

   Nom du processus d’automatisés	Description de processus	fréq.	heure cédulé
   SauvgardeComplet_BD1	sauvegarde BD complet1	W	dimanche 6h
   SauvgardeComplet_BD1	sauvegarde BD complet2	W	dimanche 6h30
   …	…	…	…

    

   Procédures et code critiques pour le bon fonctionnement de ce plan de relève

   Le suivant est toute le code nécessaire pour vous puissiez profiter de ce plan de relève de SQL1 vers SQL2.

4. usp_DB_Restore_Master

5. Sauvegardes des bases de donnés systèmes

   Sur le serveur de relève lui-même les sauvegardes de MSDB, DBA_tools (ou se situe ce code de relève), qui sont critiques pour ce plan se retrouve ici : \\ServeurDeReleve:\DossierReleveSauvegardes\Complet

   Il devrait y avoir toujours un dossier de sauvegarde local facultatif pour les bases de données systèmes, tel que sur votre serveur de pré-production/rodage finale.  Ceci dit, il est fortement recommandé de placer sur votre serveur de rodage ces B.D. systèmes ici :

   \\ServeurDeRodage:\DossierReleveSauvegardes\Complet

   L’exemple suivant a été rodé sur un serveur de test, et est présente sur le serveur de restauration. La procédure stockée usp_DB_restoreX reçoit 6 paramètres.  Afin de s’aligner avec les métadonnées de journal de sauvegardes, nous allons s’accorder avec le nom de la base de données par date et les paramètres appropriés, ensuite nous poussons ces paramètres à la procédure stockée usp_DB_restoreX approprié.  Ces procédure centralisés sont divisés en deux types: ceux qui reçois le paramètres reliés aux métadonnées simple, et ceux prêt pour les BDs qui exploite multiples fichiers de données ou journaux de transactions. Employez toutes les procédures dépendantes pour faire la vraie restitution : il faut comprendre que usp_DB_RestoreX est dépendante sur usp_KillConnections qui aide le processus de restitution en fermant par force les connexions a la base de données (mais attention aux usagers systems SPID<50).

e.g.

Le procedure stockée en question, usp_DB_restore_norecovery, est le meme que usp_DB_restore, et il est destiné pour les bases de données qui vont rester en mode norecovery (éxpliqué ci-haut déjà).

Veuillez lire l'historique (Activity History) de Red Gate SQL Backup en ce qui concerne les rapports de sauvegardes, puisque le scope de ce document n'est seulement profonde qu'au niveau de la processus de restitution.  Malgré le fait que les renseignments reliés au sauvegardes sont retirés des métadonnées afin d'être mise dans les scripts automatisés à l'interieur des processus (SQL Agent Jobs), nous allons pas créer (à cette étape, au moins) des rapports customisés.

Résumée du plan de relève

Est-ce que ce plan en cas de désastre, cette méthodologie, vas vraiment réduire l'intervention manuelle lors d'une catastrophe ?  Est-ce qu'on peut l'améliorer ? Oui, sûrement, il y a toujours de quoi à réviser.  S'il vous plait, laissez vos commentaires ici-bas, et l'on fera ensemble. Ce qui est important, c'est comprendre que cette méthode n'est pas la solution pour tous vos environnements.  Avant que vous faites une copie/collé de ce code ci-haute rassemblé pour vos opérations, je vous recommande chaudement de lire la grille de Choix Haut Disponibilité ici-bas afin de éclairé votre voie en ce qui concerne vos besoins individuelles.  Naturellement, pour faire le choix de la voie approprié, il faut une profonde analyse d'attentes de vos clients.

Choix au niveau de la Haute Disponibilité

Personne veut être mis dans une situation de désastre sans la préparation nécessaire.

Quand on m'a demandé de préparer un plan de relève pour la plus grande gestionnaire de fonds institutionnels au Canada, qui gère des fonds provenant principalement de régimes de retraite et d'assurance publics et privés, je l'ai pris au sérieux – donc l'abondance des détails dans ce document.  Nous avons faits nos propres essaies, afin de simulé un désastre durant une fin de semaine, et cela sans problème (dieu merci).  Ici j'essaie de partager exactement comment faire un plan porque lors le désastre imprévu arrive dans votre environnement, il n'est pas un désastre en soi.

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

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

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

La liste des fichiers restaurés devrait se trouver dans le répertoire suivant :

\\ServeurDeBD\Disque$\DirSauvegardesProd\DBlog\

Dès que la restauration a été complétée, nous devrions avoir un nettoyage automatique des vieux fichiers de sauvegarde grâce à un script PowerShell ou un fichier de commandes batch. Ce nettoyage devrait être fait au moins une fois par semaine en utilisant les plans de maintenance de SQL Server.

Afin de s'assurer un processus de restauration en douceur, nous devrions lire les paramètres de la restauration directement dans les tables méta données dans la base de données MSDB, tel que BackupHistory, BackupSet, BackupFile or BackupLog – à moins qu'une table BackupLog ne soit déjà explicitement créée régulièrement dans la base de données DBA_Tools (où les autres objets ci-dessous sont situés).  Ceci est toujours nécessaire en raison du besoin des paramètres essentiels disponibles (nom de la sauvegarde, et localisation sur disque dur) pour rouler ces procédures stockées avec succès.

Comment la restauration automatique des sauvegardes compressées peut-être bénéfique  pour votre environnement de production. Idéalement, il faudrait conserver deux sauvegardes complètes, la première sur votre serveur de Rodage/Test et la deuxième sur serveur de relève.  L'existence de cette copie de production en relève permet d’effectuer des vérifications utiles et/ou des tâches intensives mais à éviter en production, comme par exemple une commande console DBCC CheckDB qui vérifie l'intégrité de la base de données en question.

Lors de la création des étapes dans les processus automatisées, j'écris assez souvent les paramètres manuellement durant le rodage et je les laisse tel que – mais il est certain que la meilleure façon est de retirer les paramètres directement des tables méta données du système décrit ci-haut, car si on déplace les fichiers MDF, NDF ou LDF, il se peut très bien qu'on oublie de mettre à jour les étapes (en Anglais on l'appelle le futureproofing du script). 

Configuration Équipement et de Serveurs Production et Relève (SQL1 & SQL2)

Voici une configuration typique pour les serveurs pour un projet de relève. Dans le cas qui nous intéresse, je ne me souviens pas exactement de toutes les spécifications des systèmes, mais cela ne veut pas dire on ne devrait pas les enregistrer. Veuillez donc mettre à jour ces tables de spécifications selon vos propres équipements utilisés.

SQL1 (instance en production)

SQL2 (serveur de relève)

Here is Part 3 of the translation for the French version of the Disaster Recovery for SQL Server Databases - comments welcome.  Donc, cela veut dire corrigez-moi SVP, surtout quand il y a des phrases mal formées.

Considérations à prendre par l'analyste de la base de données (DBA) concernant le niveau de recouvrement

Le modèle de recouvrement FULL [recovery] est recommandé pour vos bases de données les plus critiques, et qui requiert une attestation d’audit.

Dans le cas d'un désastre, le plus récent fichier du journal des transactions (Transaction Log File) ou de sauvegarde différentielle est prêt à être appliqué sur la base de données en relève qui reste en mode de recouvrement NORECOVERY, et voilà vous êtes prêts à revenir à vos opérations normales, avec un temps de panne minimal.

2^e partie – Les étapes à suivre lors d’un désastre en production

1. 1.     Contactez le DBA du support en première ligne (insérez numéro de téléphone/pagette/mobile) ou le DBA secondaire à (insérez numéro).

   2.     Suite à la panne du serveur en production principal (SQL1), le serveur de relève (SQL2) devient le serveur principal de base de données.  Avisez toutes les personnes impliquées de ce changement par courriel (départements internes ainsi que les clients externes si nécessaire). Ces personnes devraient déjà y être préparées.

   3.     Dès que le serveur de relève (SQL2) est prêt à accepter les connexions, et que SQL1 (ancien serveur principal) est bel et bien mort, changez tous les liens applicatifs vers SQL2.  Si vos applications ont un fichier de configuration pour effectuer ce genre de changements vers le serveur SQL2, il est recommandé de tester cette configuration, par exemple pendant une fin de semaine, avant de terminer l'implantation de ce plan.

   4.     Désactivez vos processus automatisés de restitution des sauvegardes sur SQL2.

   5.     Désactivez vos processus automatisés de sauvegardes sur SQL1 (si possible).

   6.     Activez tous les processus automatisés de maintien et de sauvegarde (essential Jobs) sur le serveur de relève qui héberge les bases de données principaux.

 Veuillez notez que la restauration du fichier de journal vers un moment spécifique entre les sauvegardes complètes n'est pas possible si votre mode de recouvrement est Simple. Pour revenir à n'importe quel moment dans le passé grâce aux écritures sur dans le fichier du journal des transactions, le mode de recouvrement doit être Full Recovery. Heureusement, le mode par défaut est Full Recovery.  Lorsque l'espace de disque dur devient serré et qu’il faut réduire l'utilisation de fichiers journaux, le mode minimal pour revenir vers un moment spécifique est le mode Bulk-Logged.