These are instructions on how to replace expired certificates which are used for database mirroring.

Here is the following error you will see in the sql error log.

Message
Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.'. [CLIENT: xxx.xxx.xxx.xxx]


First we need to get a bit of information for the endpoint name and logins which are associated with mirroring. The following query will show the endpoint name, the certificate name, as well as the years in which the cert is valid.

SELECT e.name [endpoint_name], c.name [cert_name], c.start_date, c.expiry_date
FROM sys.database_mirroring_endpoints e
INNER JOIN sys.certificates c
	ON e.certificate_id=c.certificate_id

We can also get a list of the logins which have permissions to the endpoint and their associated certs. The following query will return the login name, endpoint name, cert name, as well as the years in which the cert is valid.

SELECT
sp.name AS [login_name],
e.name AS [endpoint_name],
c.name AS [cert_name],
c.start_date,
c.expiry_date
FROM
sys.server_permissions AS prmssn
INNER JOIN sys.endpoints AS e
	ON e.endpoint_id = prmssn.major_id
	and prmssn.class = 105
INNER JOIN sys.server_principals AS sp
	ON sp.principal_id = prmssn.grantee_principal_id
INNER JOIN sys.database_principals AS dp
	ON sp.sid=dp.sid
INNER JOIN sys.certificates c
	ON dp.principal_id=c.principal_id
WHERE e.type=4

Now we can take the information learned in the first two queries and do the real work of replacing the certs.

First on the principal…

--create a new cert for the endpoint
USE master;
CREATE CERTIFICATE [principal_new_cert]
   WITH SUBJECT = 'mirroring cert',
	START_DATE='07/11/2010', --make sure this is a day prior to the current date
	EXPIRY_DATE='07/12/2020'; --make sure this is set out 10-20 years
GO

--backup the cert for the endpoint
BACKUP CERTIFICATE [principal_new_cert] TO FILE = 'c:\principal_new_cert.cer';
GO

--set mirroring to use the new cert
ALTER ENDPOINT DBMirrorEndPoint
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [principal_new_cert])
GO

--finally delete the old cert for the endpoint
DROP CERTIFICATE [old_principal_cert]
GO

Now copy the backup of the principal endpoint cert to the mirror server. Then on the mirror server continue on…

--On the mirror
--drop the old cert for the principal login
DROP CERTIFICATE [old_principal_cert]
GO

--create the new cert using the backup you made on the principal server
CREATE CERTIFICATE [principal_new_cert] AUTHORIZATION PrincipalServerUser
FROM FILE = 'c:\principal_new_cert.cer'
GO

--create a new cert for the endpoint
USE master;
CREATE CERTIFICATE [mirror_new_cert]
   WITH SUBJECT = 'mirroring cert',
	START_DATE='07/11/2010', --make sure this is a day prior to the current date
	EXPIRY_DATE='07/12/2020'; --make sure this is set out 10-20 years
GO

--backup the new cert for the endpoint
BACKUP CERTIFICATE [mirror_new_cert] TO FILE = 'c:\mirror_new_cert.cer';
GO

--set mirroring to use the new cert
ALTER ENDPOINT DBMirrorEndPoint
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [mirror_new_cert])
GO

--finally delete the old cert for the endpoint
DROP CERTIFICATE [old_mirror_cert]

You will now need to copy the mirror endpoint cert over to the principal. You can then finish everything up on the principal.

--drop the old cert for the mirror login
DROP CERTIFICATE [old_mirror_cert]
GO

--create the new cert using the backup you made on the mirror server
CREATE CERTIFICATE [mirror_new_cert] AUTHORIZATION MirrorServerUser
FROM FILE = 'c:\mirror_new_cert.cer'
GO

--finally resume the mirroring session for each database
ALTER DATABASE [mirrored_database_name] SET PARTNER RESUME

And there you have it. Your certs are replaced and hopefully you will never have to worry about replacing them again. …Although if you still have th server in 10 or 20 years you might have bigger problems .

Each month, for six months, Arnie Rowland will  be giving away a package of software, books, training, and development tools, valued at over $15,000, to an unemployed or underemployed developer that takes on a software project of his/her choice with a non-profit.

“The idea is to provide the recipient access to all of the tools needed to improve his/her skills, an opportunity to gain practical experience, the potential to earn a recommendation and/or referral –and to positively contribute to society as a form of ‘give-back’. No free lunch, just sweat equity –the kind that makes us all feel good for the effort.”

If you or someone you know is interested, the specifics are here.

Tonight is the July meeting for the West Michigan SQL Server Users Group (WMSSUG).  Details are below, including information on our speaker this month, none other than Robert B. Davis aka @SQLSoldier!

The meeting is being presented virtually so feel free to attend even if you aren’t a member.

Robert is a Sr. Production DBA and Operations Engineer at Microsoft and has worked with SQL Server for 10+ years. He is a certified Master in SQL Server 2008. Robert is a frequent blogger at SQL Server Central. He recently served as guest professor at SQL University on the topics of certification (Degree Seeker Week) and Advanced Troubleshooting and hosted July’s T-SQL Tuesday. Robert will be presenting at this year’s SQL PASS Summit on the topic of Automating SQL Buildouts with Hyper-V and SQL Server 2008 R2. Robert is known on Twitter as @SQLSoldier.

 Advanced Tuning: Unconventional Solutions to Everyday Problems

 Performance problems can sometimes be daunting to track down especially when they occur in the middle of the night when you’re not there to check it out immediately. I’ll discuss some unconventional techniques for detecting performance problems and capturing performance data automatically. Demo will include automatically starting a custom SQL trace in response to a performance event such as a CPU utilization spike.

 July West Michigan SQL Server Users Group Meeting – Kalamazoo Michigan, 7/27/2010

Our next meeting will be held on Tuesday July 27, 2010 at our new WMSSUG venue: Secant Technologies.  The facilities were great and they welcomed us back after our last meeting.  I’d like to thank Jennifer McGeath, Holly Wickland, Dick Wolthuis, and everyone else involved with arranging and approving space for us at Secant Technologies for our Kalamazoo meetings.  Secant Technologies is located at 6395 Technology Avenue, Suite A, Kalamazoo, MI 49009.  They are extremely easy to get to off of I-94 and 9^th Street so hopefully we’ll get a good turnout from our Grand Rapids-area members this month as well.

The Attendee Link for the Meeting is below if you are unable to join us:

 Join the meeting.
Audio Information
Computer Audio
To use computer audio, you need speakers and microphone, or a headset.
First Time Users:
To save time before the meeting, check your system to make sure it is ready to use Microsoft Office Live Meeting.
Notes

Troubleshooting
Unable to join the meeting? Follow these steps:

1. Copy this address and paste it into your web browser:
   https://www.livemeeting.com/cc/mvp/join
2. Copy and paste the required information:
   Meeting ID: 4N93DB
   Entry Code: j.#w7\F6W
   Location: https://www.livemeeting.com/cc/mvp

If you still cannot enter the meeting, contact support

Our agenda is as follows:

6:00-6:30p – Mixer with pizza and soda sponsored by PASS and Secant Technologies.

6:30-6:45p – User Group Business:

• Update on Website Changes/Migration from Dot Net Nuke to WordPress
• Future PASS events, Sponsor Contests, Training News
• Leadership news

 6:45-7:45p – Presentation & Q/A

7:45-8:00p – Prizes and Closing

Hopefully we’ll see you at Secant Technologies on 7/27/2010 at 6:00p!  If you’re coming please be sure to shoot us an email letting us know!  This will be a virtual presentation, but we’re hoping you’ll join us in person just the same.

I ran across an installation issue with SQL Server 2008 on a Windows Server 2008 server the other day that baffled me a little bit.  I was installing an additional instance of SQL Server 2008 on a server that already had a SQL Server 2008 instance and right before the installation completed, it died with the error:  “A MOF Syntax error occurred.”  Further investigation into the Setup Bootstrap logs gave this detail:

An error occurred while processing item 1 defined on lines 14 – 16 in file D:\Program Files\Microsoft SQL Server\MSSQL10.TMS_MODELING\MSSQL\Binn\etwcls.mof.transformed:

2010-05-18 13:41:02 Slp: Compiler returned error 0×800700a4Error Number: 0×800700a4, Facility: Win32

2010-05-18 13:41:02 Slp: Description: No more threads can be created in the system.

2010-05-18 13:41:02 Slp:

2010-05-18 13:41:02 Slp: Sco: Compile operation for mof file D:\Program Files\Microsoft SQL Server\MSSQL10.TMS_MODELING\MSSQL\Binn\etwcls.mof.transformed failed. Exit code 3

2010-05-18 13:41:02 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigNonRC and scenario ConfigNonRC.

Much investigation on the internet turned up a lot of people that have been having this issue, but very few answers.  After many installs and uninstalls, I finally tried the following, which seemed to work:

• I ran the setup.exe as an administrator (right click on setup.exe and click “Run as administrator) even though I am a local administrator on the box.
• I installed SQL Server using the Network Service account instead of the normal domain service account.
• The installation succeeded and I just went into Configuration Manager and changed the service account to the domain account after the installation.

Like so many of you out there in Internetland, I’m fairly new to Powershell, which is fairly new to the world. Recently I wanted a simple way to script out database objects, and a couple of you were kind enough to share your solutions with me.

Sean Likes Powershell

I also worked with Sean, who likes very much to talk about Powershell (I recommend you start with What Makes Powershell Awesome?, my personal favorite blog on PS). Here is the current list of his Powershell videos:

• PowerShell Basics
• Scripting DB Objects in PowerShell
• Scripting DB Objects in Powershell (Advanced Topics)
• Working with PSDrive in PowerShell
• Powershell with –NoExit
• Change SQL Server Job Owner
• Grant DB Permissions
• Grant Schema Permissions in Powershell
• Building a Powershell BCP Routine
• Powershell Profiles
• Beginning Powershell for DBAs 1
• Beginning Powershell for DBAs 2
• Beginning Powershell for DBAs 4
• Beginning Powershell for DBAs 3
• Replace Text in File
• Find DB Files with Powershell
• Pass Params to Scripts (Powershell)
• Index Scripting Problems
• Change DB Compatibility Level in Powershell
• Get a Specific Method in Powershell
• Add User to Windows Group with Powershell
• Build .Net Objects in Powershell
• Start Agent Jobs in Powershell
• Start Agent Jobs in Powershell: Part 2
• Manage DB Files in Powershell

Yes, I am bragging on him. You should listen to your friend Sean, he’s a cool dude.

I Like Powershell

Anyway, the Scripting DB Objects in PowerShell video was almost what I needed, but not quite. Sean goes over how to script out objects to a single file; I’m scripting objects to check into source control, so I want one object per file. Last night we finally got around to sitting down so he could show me the code.

dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}

That’s it. That’s everything you need to script out every single table in a database to an individual file. I <3 Sean, and I <3 Powershell. At the risk of stealing his thunder, let me `splain a little.

We All Like Powershell!

There are SMO solutions, but there’s a definite efficiency, elegance, and convenience to having everything you need in a single line of code.

dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}

Start SQLPS from within SSMS 2008 (just right-click your database of choice in the Object Explorer, and “Start Powershell”). The SQL Server Powershell will open, with the path to your chosen database already loaded. Type cd Tables and hit Enter to navigate to the Tables “folder”. Now, to break down the Powershell command:

1. dir
   dir gets a listing of all the tables in that database
2. dir |
   the pipe (|) pipes that list to the next part of the command
3. dir | %{}
   %{} is the shorthand version of a FOR loop in PS*. So this FOR loop is FOR each table piped from dir
4. dir | %{$Table = $_.Name}
   $Table is a variable. We set it = the current table name passed in to the loop, which is signified by $_.Name. Note that $_ always means “the current one”, in this case “the current table”.
5. dir | %{$Table = $_.Name; $_.script()}
   Within the loop, after we set $Table = the current table name, we make the command to script out that current ($_) table with $_.script()
6. dir | %{$Table = $_.Name; $_.script() | out-file c:\$Table.txt}
   We pipe the output of that scripting function (|) to an out-file command, with the path that we want to use. If we wanted to, we could use a single file andu use -append; but I want one file per table, so we use a filepath plus $Table.txt (here, c:\$Table.txt)

That’s all there is to it. It seems like gibberish at first, but that’s why they call it “code”, right?

Let me take a moment here to throw a little love in Allen White’s direction (blog, @sqlrunr on Twitter). Allen also likes to talk about Powershell and SQL, and is also a really cool dude, so he’s yet another resource to check out on the path to enlightenment.

Happy days,

Jen McCown

http://www.MidnightDBA.com

* Sean says, “Don’t forget your curlies!”

The SQL Server community never ceases to amaze me.  The number of people that are willing to take time out from their jobs and families to volunteer is especially impressive.

I’ve had the good  fortune to be able to volunteer for the Program Committe this year.  My job is to pull together special projects and whatever other slave work Allen thinks up for me.   I’ve had a number of volunteers that have put great work into our current project.  This project has multiple steps and has required a ton of coordination between the volunteers – but it is all coming together.   It’s something that’s been needed for awhile  and now it’s going to be a reality.   I’d name names, but I know that I’d forget someone.   So thank you to everyone that’s helped out.

A big (virtual) cake for all of you!

It’s not just me, though.  Tim’s in the process of re-starting the Performance VC.  He had mentioned the need for volunteers through our blog, Twitter and Blythe Morrow(Blog/Twitter) put out a call for volunteers on the PASS blog.  He’s been overwhelmed at the number of people that have asked to help out.

For all of you that volunteer for PASS – kudos to you!  For those of you that are thinking of volunteering, but haven’t yet,  get ahold of Tim or me or go here for additional volunteer opportunities.

Last Friday I was one of the lucky few to attend the completely free SQLBits VI! This was my first time attending SQLBits and for me the event turned out to be an incredibly valuable experience. The caliber of speakers on show was high and the content covered technically delicious, a real treat. For those that missed out, here’s my highlights from SQLBits VI.

Monitoring and Tuning Parallel Query Execution – Ramesh Meyyappan

I entered this presentation assuming that I had the content to be covered pretty much locked down and that this would be a good way to ease into the day. It turns out I was in for a surprise and this was possibly the most valuable session of the day for me.

You know every once in a while you have one of those rare light bulb moments, like when a jumble of information and concepts just click into place all of a sudden to make complete and perfect sense. Well this session was rammed full of those typically elusive moments.

Ramesh delivered an excellent presentation that was packed with detailed T-SQL examples that he demoed effortlessly to the audience. The tempo of proceedings was fast, which I certainly appreciated because it meant I got as much out of the session as possible but this may not have been so well received by other attendees.

I won’t try to begin covering the detailed concepts that Ramesh presented here because I could not do them the same justice. I got a lot out of this presentation and have already been able to put the knowledge into practice in order to deliver performance improvements. I am seriously considering enrolling on the training courses offered by this presenter.

“A Perfect start to the day, great work Ramesh!”

Inside the SQL Server Query Optimizer – Connor Cunningham

The Query Optimizer is certainly one the more complex aspects of the SQL Server database engine and so who better to present the topic than the man who built it!

Connor had great charisma, commanding the stage with his presentation style, bringing to life what at times, if we’re being honest here, can be a bit of a dull subject.

Easing through the presentation Connor, Principal Software Architect at Microsoft, whisked the audience through an overview of the Query Optimizer which I’m sure a lot of DBA’s are already familiar with but what you don’t get from a text book is the story behind the theory. The insights into why a decision was made or why a feature was designed to behave a certain way really brings the content to life.

Connor clearly knows his stuff and I’m looking forward to getting stuck into his Chapters on the Query Optimizer in the awesome publication Microsoft SQL Server 2008 Internals.

“Expert delivery with a colorful and engaging style.”

Performance Monitoring and Tuning Tips – Ramesh Meyyappan

The third session of the day that I attended was again delivered by Ramesh who provided yet another knowledge packed presentation.

There were some great questions from the audience and from these discussions it became apparent to me that there is quite a bit of confusion in the SQL community as to when to use multiple data files for a database, with particular confusion surrounding why multiple data files can be beneficial to the tempdb database.

To set the record straight and dispel this myth, refer to Paul Randal‘s excellent blog post A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core. Administrators who design and manage how the tempdb database is configured in SQL Server need to read this!

“More of the same, that’s awesome by the way!”

Denormalization – Having your cake and eating it – Mark Whitehorn & Yasmeen Ahmed

This was a theory based session on when it is appropriate to use Denormalization. The outcome somewhat predictably of course being it depends

Normalization V Denormalization proved to be a hot topic for debate as I’m sure you can imagine. There were some great contributions from the audience who were passionately putting forward their experiences and thoughts on the subject, making for a lively and interactive session. A few “contributors” though would have been better off keeping their thoughts to themselves as their behavior was well, how shall we say, not doing them any favors.

“A solid, well delivered presentation with an engaging and lively debate.”

High Availability in the Real World – Tom Pullen

Tom took the audience through an overview of SQL Server availability technologies delivered using his no-nonsense style of presentation. Technologies covered included Clustering, Mirroring and Log Shipping.

Already familiar with each of these technologies, the most valuable part of the session for me was the insights into real world scenarios that Tom shared with us. We also appear to share the same philosophy of keeping high availability solutions simple and manageable.

“DBA bread and butter, no nonsense experience from an expert.”

There’s more to SQLBits than just SQL Server!

As if awesome presentations, free swag and stacks of great prizes alone are not enough there’s still so much more to the SQLBits experience. For one it is a brilliant opportunity to meet some of the great people from the SQL Server community. During the event I was fortunate to meet with and share DBA stories from the trenches with SQL tweeps Adrian Hills (@AdaTheDev) and Vivekanand Serou (@Vivekserou).

I can imagine that it takes a great deal of work to put on and organise a quality event like SQLBits. I want to say big thank you to everyone that contributed to making the day such a great success!

Rumor has it that videos of the sessions will be available to download in about 4-6 weeks, so I will keep you posted.

A valuable experience on so many levels, I thoroughly enjoyed my first SQLBits experience. If you are serious about your professional development and improving your SQL Server skills then be sure not to miss the next event. See you there!

Yes, it is already time for T-SQL Tuesday again (well one week away, time for the invite). I have been thoroughly enjoying T-SQL Tuesday (especially the posts ones from Brad Schulz) but the fact that this is #004 means time is flying too fast lately.

You Don’t Know What T-SQL Tuesday Is Yet?

Adam Machanic (Also on twitter as @AdamMachanic) had a great idea 4 months back – Invite new and existing SQL Server bloggers to post about the same topic on the same day. The results have been excellent – diverse skill sets and data related job roles all posting from differing perspectives on the same issue.

Since this is still early in the game, a quick list of the post roundups from the first 3 topics:

• #001 Adam Machanic started this off with a topic of Date/Time tricks
• #002 Adam then wanted to hear about your Puzzling Situations
• #003 Rob Farley got romantic on us and asked about Relationships

IO, IO, It’s Off To Disk We Go!

IO is on my mind lately. It could be some recent “discussions” with a SAN administrator, clients with disk performance issues or helping developers with some queries that are doing lots and lots of needless reads. It could be that I just changed my son’s diaper and it was heavy on the Output side (time to start potty training, I think…)

Actually, as a DBA, IO is often on my mind. So that is what this month’s theme is: IO.

Like last month’s theme, you could treat this topic in a few different ways. Perhaps some best practices that you have implemented for disk allocation. A professional development topic on working better with your storage administrators? A case study with a vendor or type of storage system? A developer writing about better managing reads in your queries? You could brag about your latest experiment with SSDs? Maybe a walk down memory lane of storage performance even. How about writing a beginners guide to setting up optimal storage? Have some really busy SQL Servers running on a virtual? How is your IO configured?

Well, you get the idea, the post has to have something to do with IO but it doesn’t have to be about T-SQL necessarily.

There’s always rules…

Once again, please note the time is in UTC. I also echo earlier encouragements to feel free to write your post ahead of time and schedule it. The rules are around when the post publishes, not when it is written.These rules are the same as the previous couple of T-SQL Tuesday’s but I’ll recap:

1. The Post must go live between 00:00:00 UTC on Tuesday the 9th of March and 00:00:00 UTC on Wednesday the 10th. If it isn’t, it can’t be included in the round up post.
2. Your post must link back to this post here (Trackback or Comment)
3. It is your responsibility to verify the trackback or comment appears (My commenting system has a difficult time with trackbacks – If I can’t resolve it by next week I will disable it and use standard WP comments but please still verify and add your own comment with a link back to your T-SQL Tuesday post and it will be included in the roundup.

That’s it. Hopefully those aren’t too difficult to follow Remember

Twitter

Not a rule but a great idea. A lot of the folks who read and participate are on twitter. Follow the hashtag #TSQL2sDay and when your post goes live, tweet a link to it with that tag.

Want To Host?

All you have to do is participate in at least two events and let Adam Machanic know. You can tweet him or leave a comment on his blog, all described in his first T-SQL Tuesday Invitation.

If you need any more clarification, leave a comment. I will respond in the comments or update this post if necessary. Have fun thinking of a topic and I look forward to reading the posts!

If you have your own go-to album/song/playlist, let’s have it.  I’m always looking for new mood setting music.  Rock on, my friends.