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).

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 :)

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 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.

Part 2 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.

1^e partie – La restitution automatique des fichiers de sauvegarde vers un serveur de relève

La méthodologie de relève utilisée est de se servir d'une instance en mode standby, dorénavant appelée SQL2, qui est déjà installée, stable et une copie exacte de la configuration en production.  Le serveur de relève, SQL2, devrait déjà avoir les bases de données opérationnelles critiques restaurées au complet en mode de recouvrement norecovery.

Le mode de recouvrement de SQL Server norecovery conserve la base de données stable et prête à appliquer constamment les changements récents venant du processus automatisé de sauvegarde. Cela implique qu'il est nécessaire d'appliqué uniquement  la plus récente copie de sauvegarde différentielle ou du journal de transactions (log file) afin de rendre la base de données disponible à vos utilisateurs et vos applications.

L'Implantation du serveur de relève

Suite à l'installation de SQL Server sur l'instance de relève, vous devez premièrement vous assurez que Robocopy existe dans le dossier sysroot\Windows\system32. Deuxièmement, assurez-vous que le logiciel SQL Backup de Red Gate soit correctement installé et connecté au serveur de relève en cliquant sur le petit bouton gris à coté du serveur dans la liste des serveurs à gauche de la fenêtre du logiciel.  Cliquer sur ce petit bouton installera et configurera automatiquement SQL Backup si cela n'est pas déjà fait.

Image 1 – le système automatisé de configuration de SQL Backup

Ensuite, en ce qui concerne les procédures stockées qui exploitent Robocopy (nous allons placer ces procédures dans une base de donnée qui s'appel DBA_outils), nous devons permettre la configuration avancée xp_cmdshell à exécuté :

-- pour permettre les changements et les configurations avancées.

EXEC sp_configure 'show advanced options', 1

GO           

RECONFIGURE

GO

-- permettre l'exécution de xp_cmdshell

EXEC sp_configure 'xp_cmdshell', 1

GO

-- Mettre à jour la configuration actuelle            

RECONFIGURE

GO

Robocopy est le meilleur outil intégré par Windows à utilisé, car Xcopy disparaîtra sous peu, confirmé par le fait que depuis Windows Server 2003, Robocopy est l'outil de copie recommandé.  Selon ce que je comprends de ces gestes, Xcopy ne serai plus disponible dans les prochaines versions du système d'exploitation Windows.

• usp_DB_Restore_Master or usp_DB_Restore_Master_Multi
• usp_DB_Restore
• usp_DB_Restore_NoRecovery
• usp_DB_Restore_differential
• usp_DB_Restore_Log

There are about 45 paragraphs that I hope to progressively and quickly translate for the French version of the Disaster Recovery for SQL Server Databases, which I have already begun, but need help as I go. Basically, I hope to receive some collaborative help by means of comments as it is going forward since I am doing this as charity, no different than my work in English thus far either, for the francophone community (which I have a foot in and common roots, but cannot match a native with respect to writing). Here we go J

titre: Plan de relève en case de désastre pour les bases de données sur SQL Serveur

La haute disponibilité est une mesure le temps que cela prendra pour revenir à vos opérations régulières suite à un incident imprévu qui cause un désastre.  De la planification stratégique et de la documentation complète sont requises afin de surmonter ce défit. Si votre plan de relève échoue, cet incident pourrait devenir une catastrophe pour votre entreprise. Hugo Shebbeare discute de tous ces détails avec un exemple typique fournissant aussi tout le code et toute la documentation nécessaire pour éviter que cela arrive à vos opérations respectives.

Dans cet article, je vais vous expliquer les enjeux techniques pour l'implantation d'un plan de relève en cas de désastre (PRD) pour vos systèmes de production. Le but de ce plan est de vous fournir du code générique ainsi qu’un gabarit de documentation afin que vous puissiez créer votre propre stratégie de plan de relève.

Je vais décrire les étapes à suivre après qu’un désastre soit arrivé dans vos environnements de  production, et noter les démarches tout au long du processus.  Ces étapes sont basées sur un plan de relève que j'ai dû créer récemment (disponible pour téléchargement afin que vous puissiez le personnaliser pour vos systèmes). De plus, ces étapes sont décrites dans le style d'un document d'affaires tactique. Je vous concède que ce sujet peut être considéré légèrement ardu et/ou inintéressant, mais je vous promets qu'il vaut la peine d’être lu.

La première partie décrit les étapes de base nécessaires afin de préparer un serveur de relève (instance en mode standby chaud ou bien, 'hot standby'). La deuxième partie fournira un plan de relève annoté, incluant les étapes à prendre lors d'un désastre ainsi que de l'information pour les administrateurs chanceux qui auront la responsabilité d’exécuter le plan de relève afin de se sortir du dit désastre. 

As mentioned by Phil Factor during the previous post’s comments regarding an example disaster recovery template/plan, and to keep in line with providing DBAs with an easier way to process through our typical stack of tasks when supporting application upgrades or development, here is a COBIT-inspired Change Management / Deployment / Risk Management Template.  Admittedly, it is quite long, thus I’ve made a quick and dirty version here, because one ex-colleague joked to me that it was like a NASA level type of documentation, but for publically regulated environments the former template is completely necessary for compliance.

These types of documents have made it way, way easier to handle all the checklists required to place code into production. Here’s an exemplar analogy: place yourself in the seat of a Pilot for a huge airliner, loaded with people - just think of this as your checklist before taking off the plane. Knowing that you've documented all steps indicates clearly (i.e. your behind is covered) that you have taken every step in due diligence to avoid possible issues, and that all risks have been taken into consideration.   Because when things go wrong, and you’ve just crashed a production server. it is much easier to simply run for the documented rollback; meaning you’ve got an easy path out of trouble, with the minimal affect on critical systems.  On the whole, the CM/RM document is hopefully self-explanatory and optimised with the use of checkboxes to allow for quick repetitive bureaucratic work. What's more, I have left it filled with a dummy case for ease of comprehension.  

Honestly, I can see that some might not be so keen to use such long templates, but after a while they become automatic and allow you to be more productive.  During a seven month stint on contract, I was able to produce up to 35 change management docs thanks to a very efficient IT team and having independent dedicated Change and Risk Managers (which is ideal, because you want Fresh Eyes to approve the work, not your buddy next to you wanting to get X update/change pushed through quick). That was a positive situation for the client, because the tendency to work too much takes over, and next thing you know your partner is calling you a ‘room-mate’: no wonder that specific organisation is known to work their resources ‘off the clock.‘

Again, looking forward to your comments, and possible ways to improve these templates.

Well, it finally happened, the complete disaster recovery article was reviewed, reviewed again, rewritten, revisited…you get the picture…and published.  Originally, this was my reactionary attempt to better SQL Server Replication (which I had lived through nightmares with prior) by means of a what I consider to be the most reliable storage replication, now it's onto Mirroring in the current mandate to see how it compares with the latter.

Thank you so very much to Chris Massey and Andrew Clarke for their great help, and to Brad McGehee for pushing me in the SSC first, and ultimately, Simple-Talk.com direction.  

The full English version is finished et une traduction en français est déjà en cours (disponible d'ici la fin septembre/début octobre).

Enjoy, and looking forward to your comments!

http://www.simple-talk.com/sql/backup-and-recovery/disaster-recovery-for-sql-server-databases-/ ( and currently front page of Simple-Talk.com )

Don't be caught without a DRP, it could mean your companys' operations end up washed up on the rocks. 

Pictured here is one of the first panoramas (albeit only two shots taken with the old '71 Pentax Spotmatic 2) I've ever taken at 15 years, at South Coogee Cliffs, Sydney - early morning 1990, while working as Moving Jockey (Aussies say Removals ) for Austwide Removals, Gary Buter's old company. Gary was my host in Melbourne at the time, allowing me to share his great flat in St. Kilda while attending Elwood High School.

Recently, we began the install of a new instance on a VMware Host and ran into an issue with a problematic outdated ISO file. Therefore, those of you embarking on new SQL 2008 installs, please avoid a potential headache, as personally exposed to (and like these guys lived through also), by simply downloading the latest ISO media/image. The following is simply an anecdote of frustration.

Our headaches began upon mounting the [outdated] SQL 2008 ISO on a Windows Server 2008 host.  Once we selected the CD/DVD drive, the pop-up/dialogue box error occurs after invoking the Setup.exe:

Microsoft .Net framework 3.5 install has failed

SQL Server 2008 setup requires .Net framework 3.5 to be installed 

I proceeded to verify directly under programs and features what version of .Net Framework was installed - sure enough 3.5, and even SP1 was the current level installed, therefore I tried a couple of more things (not knowing that simply downloading the new media was the solution):  repaired the 3.5 SP1 installation, then removed it after seeing the same error above upon invoking Setup.exe...  I even went as far as grabbing the non SP1 Dot Net Framework via download to see if that would enable the install to proceed; it didn't make a difference.

You can use this Dot Net Framework Verifier if you are in doubt as to whether your .Net version is okay.

 In the Spring of 2008 Idera launched its SQL admin toolset at a super low deal of around two hundred and fifty bucks, so they lured me in rather easily at that price.  And considering a few years prior how much I loved handling a 25+ server environment with the help of Idera’s Diagnostic Manager, without question, I was very keen to have an all in one SQL Server Data Platform management tool that filled most of the gaps left by SS Management Studio.  I personally have used the following from the toolset to perform common DBA tasks and help developers: Connection String Generator, Quick Reindex, Index Analyzer, Database Mover, Multi Query (like SQL Multi Script), Space Analyzer, Server Statistics and User Clone – there are a total of twenty four tools within the set. Here is an image of them all:

To start, the Server Configuration tool allows you to retrieve directly from SSMS the Registered Servers list, thus saving the time of importing your instance list.  For a simple configuration check all you have to do is select the specific server from the SQL Server(s):  list and click Get Configuration.  For those having to manage an extensive list of servers, you simply choose Server Group and click the Comparison tab, and finally Get Configuration again (will take slightly longer since you are querying across multiple instances).  The horizontal Comparison of all instance configurations has options to highlight the obvious differences, with Show Differences, between the configurations and even to apply changes by means of Fixing Differences. Finally, I would suggest using the Snapshot option when comparing settings before and after patching multiple servers or making changes - meaning that you can open an older XML file snapshot (the format for Snapshots) to compare also with current configuration.

I also enjoy the use of Patch Analyzer to keep on top of the build levels for the respective SQL Server instances across my clients' infrastructure, as well as Server Statistics to dig into Performance Counters which help significantly to analyse the buffer cache hit ratio.

If you visit Idera's site, I'm sure you can find a trail download if you are interested in this useful set of tools.

As far as operating systems go, I can be pretty conservative with respect to what operating system I chose to have at home as a network server, currently a second generation ML 150 server by Hewlett Packard with dual Xeon 3Ghz (not dual core, but dual socket, with hyperthreading) and sadly only 1GB of RAM right now, but soon to change.  My x64 install on this hardware has performed well over the past almost four years, as well as the hardware, so I've stalled long enough the upgrade process. Plus there is also the motivation, after living with Windows Server 2008 on the laptop (still happy with the albiet small Samsung 128GB SSD), to have operating system uniformity, which is much easier for those family evening support calls ( :) ).

And no, in case you are wondering, why don’t I just move the x64 install onto VM…well, that would be a great if VMware actually recognised Adaptec HostRAID (which has been very reliable for everything else, mind you) - but sadly it doesn't, since it's not a full-on RAID card, but a HostRAID only. 

To prepare for the new setup of Windows 2008 server standard on this critical machine, I’ve picked up four new hard drives.  Two Seagate Cheetah 73GB 15krpm SCSIs for the operating system, swap, and program files with two SAS 2TB Seagate Barracudas for all personal and business data space, plus much will be used for keeping some serious backup space for the Dell XPS M1530 and phone (Nokia E75 - loving the MicroSD space, use video frequently, and a bomb-proof mobile OS, although a little dated).   We always end up hoarding so much data from traveling (especially photos and videos from the Canon G9 - e.g. just got back from a day trip to Chicago see photo below), thus it is not very hard to imagine where it all ends up getting used!

Now I cannot wait to get the whole Server 2008 install finished, just hope all the 64-bit drivers work out :) Updates to follow as the hiccups arrive! 

1 -- timeout errors on the SCSI device, despite connecting the drives from the termination end first, when using the SCSI hostRaid setup utilities. On boot up both Sata HostRaid and SCSI HostRaid are in the sequence, but two SCSI disks timing out so far...later fixed by placing a jumper on the second 73GB SCSI to ensure different IDs and disabling parity (RAID 1 to be used on both HostRaids).

2 - install working in x64 Windows 2008, and even the sound card is good, however Adaptec Sata HostRaid 8130 has no driver for Windows 2008 x64 although it did for x64 on Windows Server 2003