The Database Hive for SQL Server DBAs

sql server optimisation, sql server 2008 database management, sql discussions, help, clusters, backups, expansion, usefull tools, mcitp certification, server management, meilleures pratiques sql server, best practices.

Tuesday, June 15, 2010

Two Ways to Get System and Server Information on SQL Server from Management Studio / Enterprise Manager

 


To collect information for your SQL Server Infrastructure, there are two ways I can recommend. 
The first, as mentioned from Technet recently, is to execute the following parameter details on any SQL Server installation (I tested back to 2000), by run the following command.


exec xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory", "ProcessorCount"


-- result set is a table, with a row for each parameter


 


The second, and my preference as best pratice for gathering essential server information in a single row with more details, is the following, including the Collation, Clustering, Service Pack Level (product level):


select serverproperty('MachineName') MachineName


,serverproperty('ServerName') ServerInstanceName


,replace(cast(serverproperty('Edition')as varchar),'Edition','') EditionInstalled


,serverproperty('productVersion') ProductBuildLevel


,serverproperty('productLevel') SPLevel


,serverproperty('Collation') Collation_Type


,serverproperty('IsClustered') [IsClustered?]


,convert(varchar,getdate(),102) QueryDate,


case


when  exists (select * from msdb.dbo.backupset where name like 'data protector%') then 'HPDPused'


else 'NotOnDRP' -- where you would replace the


--data protector string with your third party backup solution


end


 -- thanks to my highly organised DBA buddy Pollus Brodeur, for introducing me to ServerProperty command several years ago


 


To run either of these queries across multiple servers in SSMS 2008 (assuming that you have more than one), under Registered Servers, right click on Local Server Groups, and select New Query.


 


References:  See all the recent Technet SQL Server Tips


 


It has been a long walk up for SQL Server, but I feel that we're almost at the summit with this version.


 

Labels:

Free E-Book on SQL Server 2008 R2

 


Free E-Book on SQL Server 2008 R2


Ross Mistry and Stacia Misner have done a wonderful job on this free e-book


http://blogs.msdn.com/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx




You can download the full ebook in XPS format here and in PDF format here.


 


 


 


The book contains 10 chapters and 216 pages:


 


PART I   Database Administration


 


CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements 3


CHAPTER 2   Multi-Server Administration 21


CHAPTER 3   Data-Tier Applications 41


CHAPTER 4   High Availability and Virtualization Enhancements 63


CHAPTER 5   Consolidation and Monitoring 85


 


PART II   Business Intelligence Development


 


CHAPTER 6   Scalable Data Warehousing 109


CHAPTER 7   Master Data Services 125


CHAPTER 8   Complex Event Processing with StreamInsight 145


CHAPTER 9   Reporting Services Enhancements 165


CHAPTER 10   Self-Service Analysis with PowerPivot 189


 


Embark on SQL 2008 R2 with the Free E-Book


 

Labels:

Latest Simple-Talk.com Article Published on Deployment Management

After several months of reflection/rewrites with colleagues (because orignally I forgot the Rollback plan - oops), and reworking the bilingual templates, the great editors at Simple-Talk.com have pushed the latest work to the front page back in April:


 Deployment Management is Worth IT: These Templates Should Make You Believe It Too


by Hugo Shebbeare | 14 April 2010 |

Even experienced managers of IT operations can stumble over the most critical part- the documentation. If only there were useful examples around! We commissioned Hugo Shebbeare to explain what is required of such documentation, and why. He has also provided the... Read more...

 

Enjoy the FREE TEMPLATES for Change/Risk/Deployment Management, and, as always, look forward to your comments :)

Labels:

HP Data Protector - SQL Server 2008 Supported, but Compatibility Pack 2005 Required

 


Recently, we found out that HP Data Protector had an update, and I took for granted that this update would make the product more compatible with SQL Server 2008 - but it still doesn't support SQL Server 2008 without installing the Backwards Compatibility Pack from SQL Server 2005
Not entirely such a pain, but just be aware, HPDP fans, that this warning message 'SQL DMO Component is not available which will cause MS SQL Integration to fail' really means to say you should download the
SQL Server 2005 Backwards Compatibility Pack. If you read the fine print of their Support Manual, you`ll notice on page twelve, point seven, that the SQL DMO download is required.


SQL-DMO warning during Data Protector Install


If you haven't installed the Backwards Compatibilty Pack first, then when using HP Data Protector with SQL Server 2008, you will see this error (HR 0x80040154] Class not registered): 


Class not registered Error


Or notice this error in your Debug.txt (set this in properties) in your Sysroot\Program Files\Omniback folder:


25/03/2010 11:27:48 AM  OB2BAR_Main.796.2948 ["integ/sql_7/sql7_dmo.cpp /main/hsl_dp61/hsl_hpit2_2/1":288] A.06.11 b243
[SQL7_Login] Failed creating object instance. Err code: -2147221164, REGDB_E_CLASSNOTREG
Function trace (currentLevel=3):
 Level  3: SQL7_Login()
 Level  2: SQL7_CheckConfig()
 Level  1: SQL7_Config()
 Level  0: main()


When Installing the SQL Server 2005 Backwards compatibility Setup, you only need the SQL DMO object components for HP Data Protector to work correctly with SQL Server 2008.


Labels: ,

Developing Your DBA Skills Further Thanks to Experience with Multiple Relational Database Management Systems

 


Any DBA who really understands databases and SQL standards can make out with other RDBMSs quite well, and if they have not tried yet, I recommend it for the reasons I shall discuss below.


Specialists maintain, with reason, that you cannot be a Master/Mistress of multiple database management systems.  Fine, if you have decided that for your future, that there is not the possibility of being proficient in another system, then you have closed up your options before even trying perhaps(?).

Just in the same way as knowing multiple languages can help you know your own language better, I believe the same goes for database management systems.  This competition between vendors of varying DBMSs allows for great features to be passed from one database engine to the other and benefits us, the DBAs, greatly. The benefits of taking the deep step into another RDBMS can further enlighten your approach to resolving problems or creating unique solutions that embrace a resolution no matter what source they come from.  


 


After several months working back with Ellison's baby (trained on Oracle 8 in 1998), Oracle 11g and its solid architecture are impressive.  I especially appreciate the recovery approach Oracle takes, explicitly the redo/unlog log files: relying on the SCN (system change number, defined during a checkpoint) for bombproof recovery. The real issue with learning multiple backup systems running on MSSQL is that you could lose your backup recovery chain (unable to match LSN numbers) with mixed Tape/Disk backups, whereas with Oracle’s use of the SCN, this is not an issue.  The hardest part initially, was learning a whole new set of Acronyms related to Oracle's architecture for example: SMON, PMON, DBWn, RECO, ARCn et al.


Oracle Developer 2.1 is a pretty cool management tool also with easy result set export to XLS/CSV/etc., although I prefer SQL 2008's Management Studio. But also not to be ignored, is the excellent web-based Enterprise Manager from Oracle, which can take a bit of getting used to – with respect to finding your way around – but is very rich once you are familiar with how to take advantage of it.

Seasoned Oracle DBAs I enjoy working with have mentioned the lack of spooling in Oracle's developer (UPDATE, actualy spool does work fine in O.Dev 2.1), and after going back to using SQL+ for a while they have a very good point.  Oracle Developer and SSMS always have the code / results split up (unless in SQL DMO?), which doesn't always make it easy to associate the error with the section of code you are working on (unless you click on the error in SSMS report pane that is).  Setting the Spool on and off after working on production provides pretty bomber evidence for auditing operations on your production databases, or results during testing and development.


 


In a future post I will discuss some fun I have had with MySQL – and unfortunate tendency I have found also, that MySQL developers (who have worked very hard to build their system, and are naturally defensive) will ask the DBA for help in desperation, and as soon as the access is sorted, it only takes a short time before the real fixes become obvious and the developers’ knee-jerk reaction becomes to circle around their team in defence instead of accepting DBA intervention (we are there to help (!), and ultimately improve the bottom line for the company, or success of the respective project).  Anyway, that is par for the course often enough for DBAs, as mentioned to an extreme in my last post.  


 Do not be afraid to cross the bridge to the other side, or the other Database Management System.


 


PS enjoying a little work with PostgreSQL also recently, converting a schema to SQL Server and Oracle.


Really enjoying the tool SQuirrel SQL Universal SQL Client Version 3.1

Labels:

First Public Hearing Against Quebec's Pension Fund Manager - The Beginning of Proof that Multiple Laws Were Violated (Before Sabia took over at least)


As mentioned in a previous post, between the months of January and September 2008
I was hired to be a Database Cop for the
Caisse de Défauts (Master Sabia coined this phrase in a TVA interview btw, not me!) and clean up a department where developers were giving the impression of being cowboys with some pretty mission-critical financial data. Here are the notes from the first meeting last week at the Workplace Relations Commission (CRT: Commission des relations du travail, located in Montreal, 35 Port-Royal East, 2nd Floor).



Why I am doing this? Public Interest and to its right to know what the infamous Commission Parliamentaire hid from Quebeckers
I believe some significant personal sacrifice is necessary to maintain Canadian Unity whilst I attempt to impartially describe this horrible situation our Pension Fund manager has been putting me through over the past fiveteen months (their victims are many, even some friends have mentioned psychological harassment until suicide after working there), but first, the disclaimer. Most 
importantly this post is a warning to Database Administrators in general who have been in hostile environments, this is the nightmare that we have to walk into frequently enough when only Developers have controlled an environment (Brad McGehee discusses this partially in his DBA Manifesto), or if there is only one 'token' subordinated/muzzeled DBA in-house to maintain compliance with legislation.

Disclaimer: Veuillez prendre en note que je ne suis pas francophobe, je blogue en français et je partage, vers un côté de la famille, les mêmes racines que la majorité ici. Je suis né à Vancouver, et ma soeur Monique et moi aussi, nous sommes allés à l'école bilingue.  Je veux que nous améliorons notre société au Québec; non pas seulement pour faire des accommodations raisonnables aux nouvelles arrivées dans la province, mais pour rendre du 'respect' à la minorité anglophone qui vit ici depuis des siècles. Le mal traitement dont j’ai vécu à la Caisse de Dépôt est un cauchemar absolu, mais ce n’est pas isolé à cette organisation gouvernementale exclusivement, et existent dans certaines sociétés privés au Québec. Je resterai toujours positif, parce que je suis un Champion pour le Canada, and Quebec is included in my country. Mais, il faut que ces extrémistes ultranationalistes soient mis en règle afin de laisser le Québec se progresser, car à mon avis, c’est leur faute que la province est en déclin tranquille (à cause de deux referendums, et maintenant $47 Milliards perdus à la CDPQ). J'emploi mon droit constitutionnelle de s'exprimer - to clearly counter sovereigntist bullies who have repetitively told me that my constitutional rights in Quebec do not exist.


 


First image: Interview dates, time, place from an e-mail I sent to the Working Standard Commission complaint validators in April 2009, about five months after I submitted the original complaint to them on December 8th, 2008. The complaint made via this government body responsible for Workplace standards was initially rejected by the Caisse, but eventually led to a mediation date five months later in July 2009 that was cancelled at the last minute by their lawyers flip-flopping on whether I was subject to conditions as a permanent employee, which means complying with the Labour Standards Act, or if I was only a lowly consultant that they can treat with disrespect repetitively (as if harassment by workplace bullies is okay?).   I even wrote to their new CEO, Michael Sabia, with a personal hand delivered note in protest for their bureaucratic juggernaught response of pulling out of scheduled mediation with the Working Standards Commission, but as usual, there was no response. It is clear to me now after this long that my rights to them are not important, so I shall make this publically clear in writing. Je vais mettre ces 'maîtres' à genoux avec ma plume s'ils vont pas faire valoir mes droits !


This is he beginning of the timeline starting January 10, 2008




Now for a bit of Background - What is this organisation?
The Caisse de Dépôt et Placement du Québec - short name in English, although seldom used, is CDP Capital, and the long name Quebec Deposit and Investment Fund. This forty-three year old Quebec-based über-bank is the largest institutionalised fund manager within Canada and is entrusted with our pension monies as well as investing in businesses that create provincial wealth.  As you can read from the Wikipedia link, they lost about 26% of our collective wealth during the 2008 fiscal year. This is a collossal amount of retirement investment to lose.  Nobody ended up charged, but the executive board was litterally cleaned out in April 2009 after two consecutive 'interim' leadership failures, and the extent of mis-management was revealed (see Diane Francis' Train Wreck articles) -  only a few members of the executive board were left including Operations Director Ghislain Parent. Did the SQ even look into where these bandits à cravates placed all their unwarranted bonuses, they were giving themselves even in 2008 for leaving while the 'perfect storm' was happening!?  Sabia, the new head of this institution, has recently restructured the activities completely and abolished about 55 positions - must have been because the ship had lost its way a little don't you think? I still maintain my opinion that the Gomery Commission regarding the Federal Sponsorship Scandal, which saw us wasting years of taxpayer time and money over a couple of million dollars lost is the greatest hypocrisy in modern Canadian history because the Caisse lost almost five thousand times this amount, whilst the provincial media has only spent a few months on this subject in comparison. Moving on...


Second Image - the Job Offer itself. I met with the Senior Vice President of the department January 18th, 2008. Priorities were to deal with optimisation, backups, and alleviating production support from my former DBA colleague. The VP clearly indicated that this was for a permanent position, and I agreed that if I made it past the first six months, we'd switch over officially.  I have e-mails to confirm this agreement, and e-mails are contractual according to educaloi.qc.ca. I even had a confirmation meeting with the internal Human Resources Department representative (and actually a neighbour) in March, 2008, to ensure their intentions.


 This the Job offer - notice no mention of contract? Poste indicates permanent job.



Please note that what public details you see here are correspondent with discussions at the CRT – I have no intention of tarnishing the Judge’s decision making process.  However, still outraged as I am at this continued denial of my rights, I wish to seek public aid in pressuring the organisation, which holds over two hundred billion dollars of our provincial investment in its hands (including Real Estate Assets), to remediate this situation immediately. This is a David Vs Goliath situation, and is evident by the fact that four lawyers showed up to in defense of the latter! And to help readers to understand the extent of denial I have witnessed during this whole complaint process, even Legal Counsel at the Caisse de Dépôt itself (Mélanie Julien), told the Working Standards Commission's validator that the man above, whose Business Card clearly states he is a Senior VP, was not a Vice President in the first place. Sort of like her attitude during our November 2008 meeting, stating to me that I have bascially no rights, not even Human Rights, since I was on contract still and they can cut the cord whenever they felt like it, no matter what the circumstance (Audit failure, racial comments, firing the whistleblower, asked to bring the department back into control etc).  

Just another anecdote of twisting the truth - pointing out that only I waited till the last minute to file a complaint: one thing my opposition in the public hearing, just last week, forgot to mention was that I tried to make an internal complaint to the CDPQ within the 90 day limit for filing with the Commission des normes du travail (Working Standards Commission) after the forced departure on September 9th, 2008 and even had a meeting on November 12th, 2008 with the Complaints Director herself and a junior lawyer. The end result was no action whatsoever on their part (what Daniel Laprès describes as Quebec's Laïcité stricte?).  All I got while leaving the meeting was a verbal sorry, but a warning that the corporation will do all it can to protect itself - nice job, victim intimidation (!), en plus.
I know I mentioned bureaucratic warfare before - and I thoroughly believe now after living through this debacle, that this white collar crime has contributed to hundreds of thousands of native English speaking Quebeckers to leave the province since the beginning of the 1970s (known as the Anglo Exodus). This trend has only reversed within the past years with the return/influx of many Anglo-Quebeckers, in addition to the question of sovereignty returning to a hypothetical situation (after two referendums defeated) and due to a clear lack of legitmacy for such a retrograde cause.



I was warned beforehand that It's the Caisse - Discrimination is acceptable there when against Anglos. 


I was told by a senior consultant friend before starting there that within six to eight months senior managers would find a way to force me out, as is done on a regular basis to Anglophones who work there – even confirmed to me by several security guards who work for the organisation (that wish to remain anonymous!) that it is practically modus operandi at the Caisse to do so. This is a clear violation of the Charter of Rights and Freedoms and my Human Rights - hence my Quebec Human Rights Commission Complaint File # C1694-09Les allégations contenues dans la plainte ont été considérées suffisantes pour que la Commission y donne suite à la poursuite d'une demande d'enquête (qui sera refusé, je prédis, tel que celui de la Commission des normes du travail en août/septembre 2009).  I still cannot believe this is happening in my own country – it is shameful that the Quebec Government does little to stop this itself (by not enforcing any sort of minority quotas, or playing a grinch-like denial game) and is often abetted by typical Canadian complacency, which exists not only in QC, or an assumption that to treat English speaking Quebeckers this way is acceptable. One could describe this as reverse discrimination at the provincial level, against what French speaking Canadains receive in the rest of Canada.  Discrimination seems to be (proof in many posts already on this subject) okay in my own province and as a victim of it, I shall fight to the death so that my rights are recognised, whether the provincial majority (in denial) likes it or not. It is a scenario that repeats itself over and over again anywhere on the planet, and not doing anything about it only makes it worse for all minorities in Quebec.  On the other hand, it is unfortunate that the Globe and Mail concerns itself with being anti-Quebec - to sell more newspapers perhaps, by not clarifying the fact that Miss Naema Ahmed was asking for unreasonable conditions with respect to men in her classroom (Julius Grey, the famous Human Rights Lawyer, has taken QC's side on this one with good reason) is sloppy. I applaud Patrick Lagacé for his réplique counter to the Mop and Pail's misunderstanding of the situation in Quebec completely. 


A cross-cut of the Human Rights Commission telling Sabia that it will investigate.


But, hey, don`t listen to me with respect to the English speaking provincial minority, just listen to TVA.Canoe.com's analysts point out that Sabia is an Anglophone from Ontario (although his Grandfather arrived in QC originally with pennies) and the fact that it created a huge contraversy in the QC FR-speaking media provides clear proof to the underlying message that 'He's not one of us' - and thus, should not be on the list for the Caisse's top job (remember your detestable knee-jerk reaction André Pratte?).  He even complaints about Sabia's level of French, and note that Jean-Philippe Décarie propagates and idea from the 60s that Anglophones who work in Montreal (ne faîtes pas l'effort) do not want/or make the effort to learn French, nor know it well - this is nothing more than prejudice since 90% (10 years ago when I arrived from Brussels, a cosmopolitian/trilingual city in itself, it was around 80%) of Anglophones around Montreal speak and work in French. But due to this prejudice we are literally shut-out of the provincial government job opportunities, whether they are for the Federal or Provincial level here. What bothers me personally, is that during out first hearing the oppositions' principal Lawyer Maître l'H[mister unhappy] stated to the Commissaire (Judge) with a 'oh, not another' tone that this case was about `discrimination parce ce qu'il est Anglophone'. If this lawyer, who entitles himself to take advantage of a provincial minority and downplay the discrimination we live by means of the our local government (which we pay taxes too, just like everyone else buddy!), he's already tripping over himself. Had said such a thing be said in the United States about a minority during a public hearing it would make Front Page news.  Here, as I stated above, it is considered normal.  I hope the Judge recognises this and puts a stop to it on my behalf of all Anglophones, and all minorities for that matter, in the province.


 


On being labelled an Anglophone - Je déteste cette `labelling' employé au Québec - Je SUIS BILINGUE, je suis allé à L'école bilingue durant mon enfance à Vancouver. I'm part of both founding nations of this state, à quand la fin de cette chanson 'which side are you on boy' (Pete Seeger) - I want to be ABOVE this shite, as our Border Reivers (Anglo/Scotch Clans) ancestors where for centuries, on the Graham side, of the family http://en.wikipedia.org/wiki/Border_Reivers - you would not be surprised to know that Pierre ELLIOT Trudeau was also descended from these Clans, thus his success as one our greatest Prime Ministers. I explained this to Justin Trudeau, his son, but I do not believe he got/gets it [yet].


It would seem that the Quebec Government and Media are completely oblivious to the fact that Racism has its costs – the victim pays dearly.  It is now fifteen months later and I have only just managed to recover my company’s finances (and during a recession too, imagine) while I went through another round of psychological harassment and defamation by the one sovereigntist bully developer controlling production in the office at my next mandate (bad news comes in threes?). The issue here is not that so many Quebeckers hold these views against the English-Speaking minority, it is simply that the one Blueneck in a group of ten QC Francophones will go out of their way behind the scenes to make someone's life miserable, while profiting from the complacency/passivism of the group.

Image Three: the call to testify last week. The recruiter is involved in this case and it is unfortunate, because they were perfectly professional during these whole events and I would still recommend them to anyone, despite the filibustering has-been lawyer they have on board.  This image below provides the case number also (CM-2009-4697).




Where does this go from here?


Next public testimonies are scheduled for May 10th, 12th and June 11th, 2010 to establish that I was indeed under the conditions of an employee and not a consultant at the Caisse de Défauts. This means months of waiting again in this bureaucratic nightmare, but it is absolutely necessary - asking my company to get involved means I need representation, and prior to this step in the direction of the CDP, Intellabase was profitable for seven years. I find this completely ironic also that an organisation that is founded upon the principle of contributing to the economic development of Quebec (but from my experience they are in part responsible for Quebec's blight) is responsible for trying to destroy my business.  My goal is to clearly point out how their goal is to only keep the majority in control of resources and prevent the advancement of minorities – and is clearly Racist as typical of any overly passionate and Nationalistic government organisations can become without being reminded of their folly - I would even go as far to describe their mindset as inebriated from illusion tranquille.  There is much jurisprudence with respect to cases like this (clear jurisprudence for employee conditions i.e. subordination, equipment, fixed hours), so I shall be very ready with my legal councel by the time we are in the next public hearing.  Only next fall, we will get into the details of the psychological harassment itself in court, if a settlement is not reached in the meantime.


It was somewhat amusing to watch lawyers wandering in and out of the room – obviously they keep trying to negotiate a settlement, but the Caisse de Défauts is not known for eating much humble pie, as experienced journalist Konrad Yakabuski noticed while covering this organisation. Thankfully new CEO Michael Sabia has played the role Charest was expecting him to do and clean out the organisation at the top (as Accountant CEOs do, and as he did at Via Rail and Bell in the past), and new Finance Minister Raymond Bachand has confirmed that Risk is managed appropriately now. Regarding the department I was serving, it has been swept under the control of the I.T. department after my departure and the Senior Consultant who forced me out was also eventually dismissed - so, yes, there is good Karma and some retribution already. And I was rather happy to thanks to the great editors at Simple-Talk.com, based in Silicon Fen, to have published the Disaster Recovery Plan (aussi en français) a generic version of what I did for the Caisse itself while there: viewership has reached over twelve thousand for this open-source DRP.  


History of Government Dishonesty Repeats Itself

It is interesting to note that on Tuesday February 9th, 2010 the cases listed for public audience were incorrectly named, as if to conceal that someone was pursuing the Caisse? What bothers me the most is that journalists who regularly take aim at the CDPQ in French (mainly from La Presse), or English (Montreal Gazette, National Post and Globe and Mail) have not shown up to the public hearings, despite multiple reminders I have sent. I really don't understand this crazy complacent ear-plugging occurring, but hey call me crazy, I am suing through two government bodies (not at the same time, their is procedure, double-recourse simultaneously is not possible), the largest institutionalised pension manager in the land. They perhaps cannot believe it happened or is happening, thus publishing myself.  This must not happen anymore to Quebec's minorities and I will expose as much of the truth as is revealed in the public hearings (The Caisse manages all of our Pension Money, they must be held accountable), as opposed to how my forebearer did: Major-General Frederick Barton Maurice, who took on publically, via letters to the [London] Times and Daily Chronicle, U.K. Prime-Minister Lloyd George in 1918 for exaggerating troop numbers in Parliament concerning General Haig's fighting strength on the Western Front. This is known in British History as the Maurice Affair/Maurice Papers. It was noted, after studying situations my ancestors were placed under, that taking on the government itself should not be done without another government faction involved, or better yet, two of them. See, I'm a typical DBA, I always have a backup :)

Fear Plays A Big Factor in Quebec


Many persons, of diverse backgrounds, during this same time period in 2008, have suffered from this type of harassment (details to come later publically and during the next meeting in May), but they gave up right away due to the size of this institution - even though several have remained unemployed ever since (15 months). Since I have witnessed too much to stand by and do nothing about this ethnic cleansing - defined as 'the planned deliberate removal from a specific territory (govt workplace in this case), persons of a particular ethnic group, by force or intimidation, in order to render that area ethnically homogenous,' and there is significant legal backing, I shall litterally drop the gloves on the CDPQ until they cede - following a hold-fast/siege strategy until justice is obtained. And for those who think that I am just attacking Quebec because the bashing has been a little too frequent lately, I am simply following a family tradition on the other side of the family at questioning government over its tendency to tyranny.  Doctor John, as affectionately called by seniors in the Shebbeare family, was highly critical of Colonial Magistrates and wrote out during the American Revolution in the same way.  He was also extremely critical of His Majesty King George III (the mad one), but ended up as a pamphleteer for the same King he was attacking, around the time an another ancestor became Prime Minister.


Parting words for this hopefully enlightening post:  I love my job as a DBA, and nobody is going to break multiple laws in front of me without a fight – when you know you’ve got at least four, and arguably six technical reasons behind a fight, then it is rather motivating to know beforehand that you can be very cogent in the near future. I have prepared all these related posts over the past year, of which many of them were SSC front-pagers (if you read between the lines, the extent of what I witnessed made it very, very easy to thoroughly document) - here they are again if you missed them beforehand:



This is just another part of the series, next one will follow once the jurisprudence is settled for the conditions that place my case under the protection of the Labour Standards Act. Thank you very much for reading.


P.S. The details of this case have been documented by a trained historian, and now I can sincerely thank all the professors at Vesalius College, Vrije Universiteit Brussel, for the thousands of pages we had to write during the International Affairs and History degree between 1994-98.


P.P.S. BTW for those of you who just watched the Vancouver 2010 Olympic Opening Ceremonies, and heard complaints about the lack of French, please understand that Vancouver is FIVE AND HALF hours by plane from Quebec, and there are very few bilinguals to learn the beautiful language with, as I noticed while growing up. From reading up in La Presse recently (at least pointing out that our first Canadian Olympic Gold won at home was by the Great Alexandre Bilodeau) it seems as if the journalists were hoping for bilingual levels the equivalent of Ottawa, on the border of the province: they seem to have a complete lack of the importance of geography regarding this issue. Whatever the case, the QC media can try and rain on Vancouver's great Olympics all it wants, they have New Yorker's approval and that is far more impartial.   
_______



This post is dedicated in memory of the great Anglo-Quebecker patriot William Heward Grafftey - a Champion for the Canadian social values.

Labels: , ,

Troubleshooting and Optimising SQL 2008 Reporting Services Configuration for Microsoft Dynamics CRM 4.0

 



Recently we have been struggling with configuration and installation of SQL Server Reporting Services 2008 when there was a co-existing 2005 Reporting Services instance. The following attempts to describe how to make sure your reports work between Microsoft Dynamics CRM and SQL Server Reporting Services.  This is a follow up to my previous post on optimising performance for CRM applications.


 


The obvious major change between the Reporting Services versions: IIS was required in SQL Server Reporting Services (SSRS) 2005, but no longer since SSRS has its own built-in Web Server to run reports on port 80. However, if you wish to have SSRS 2005 and 2008 co-exist on the same server (for perhaps masochistic reasons?), one has to decide which of the two Reporting Services versions will have to use a port OTHER than port 80.


 


Prerequisites for the data connector install
Configure your Data source so that the Report Servers Computer_name$ has DBO rights on the OrganisationName_MSCRM, then test that Reports actually work in Report Manager.
Make sure the NetworkServices account runs Reporting Services.


 



Before installing the Microsoft Dynamics CRM Connector, you most likely run into the Error:


Unable to validate SQL Server Reporting Services Report Server installation.  Please check that it is correctly installed on the local machine.
This will involve editing your install-config.xml file:


Make sure to modify this file to the name of your specific instance, on the following line:


<reportserverurl>http://ServerName/reportserver$SSRS08instanceName</reportserverurl>--> 


(where the Instance name should be written after the dollar sign) 


Run the following from command line afterwards:


SetupSrsDataConnector.exe /CONFIG "D:\Downloads\SrsDataConnector\install-config.xml"


You may have to uninstall and reinstall the Data Connector if you had the ‘Unable to validate…’ error.


If you require support for your configuration, please beware that Microsoft’s Customer Relationship Management team, or Microsoft Dynamics Team, vis-à-vis the Reporting Services Support do not overlap responsibilities  most likely if you report server responds http://ReportingServicesServerName/Reports


If you are able to see the above link, but form the CRM side when you look at a Report and you receive: ‘Reporting error


The Report cannot be displayed; check the Microsoft Dynamics CRM Community for solutions’ then you should try what I mention in this post before contact CRM support.



The Data Connector Optimisation


Data Source configuration should be done to get the most out of Reporting Services running with CRM Dynamics. To do so:


Click on the organisation, then right-hand side, on the toolbar, click Show Details, scroll down to the bottom and click on MS Data Source, and set up your Connection type as below.


Verify that Connection Type is set to "Microsoft CRM Data Extension" and Connect using: is set to "Credentials supplied by the user running the report"


and finally, click Apply.


 


 


 


 


Connection String: MSCRM Data Connector Connection String


Click on OrgName_MSCRM, On the right-hand side toolbar, click Show Details. Click 4.0 then click on MSCRM_DataSource


 


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.


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


 


 

Labels: ,