Robert's SQL Blog

Summer is a few days away, although in Holland the best days are already behind us. When spring comes we clean our houses, get stuff organized and loose these extra Christmas pounds so we can enter the summer with brand new energy. All of the above apply to me but this year I will add “Start a new job” to the list.

As per July 1st I will be joining Microsoft as a Premier Field Engineer. I’m really excited about this job and can’t wait to get started. Homebase will be the nice office at Schiphol in the Netherlands and from there I will be assisting Premier customers with their SQL related questions and problems. Of course, after the proper training.

Will I keep blogging here? Time will tell, but if it’s not on this blog it will be on another. I love getting into SQL Server and share my experience with others!

In the past I have attended DevDayhs several times. A nice local event with great sessions about development. Either with .NET, Wp7, Blend, Lego or anything. This year I went in as a speaker. Which was fun to. I had a great time doing the session on SQL Server and PowerShell. As promised I have a link here to the scripts I used and the Powerpoint.

Add-Sql Script from BooksOnline
SqlServer Script with SMO functions like backup, create DB
Powerpoint slides

If you missed DevDays or want to view some of the sessions you missed check them out at Channel 9, http://channel9.msdn.com/Events/DevDays/DevDays-2011-Netherlands

The sessions are a mix of Dutch and English. All non-dutch speakers are English of course, but some of the dutch speakers did an English session as well.

If you have questions feel free to contact me through this site or twitter.

I was preparing a post on building reports with Report Builder against the SCOM data warehouse and why you would not use the reports in SCOM itself. But then I got distracted by a post on the Microsoft Common Engineering Criteria (CEC). While the home page, (http://www.microsoft.com/cec/en/us/default.aspx was promising "The goal of the CEC program is to reduce the overall total cost of ownership (TCO) through improved integration, manageability, security, reliability, and other critical infrastructure attributes that are expected by our customers." I was still a bit skeptic.

On the same page there is a scorecard link showing how each server product scores on the CEC. This image shows a part of the scorecard for SQL Server 2008

So for all the server products a scorecard is listed. There is however something odd going on. E.g. SQL Server 2005 and SQL Server 2008 are on the list but SharePoint 2007 is not. SharePoint 2010 is on the list but this isn’t generally available yet. Same goes for Exchange, 2007 and 2010 are both in the list. Apparently SharePoint 2010 supports hot-add CPU and SQL does not. I was not aware of that. Also SharePoint complies with the Data Protection compliancy. If you read the description of this compliancy it says "Restores the application and operating system, including quick recovery". So if I wanted to restore a site collection, or a list within a site collection, or a document within a list, this would all be possible not to mention quick. Well, some would be of course,and depending on your infrastructure it would even be quick. This brings whole new insights to SharePoint for me. According to these scorecards SharePoint 2010 scores great,which I believe it does. But it would have been fair to put SharePoint 2007 on it as well. Just to see the improvement that was made with the next version.

But…. Something is missing. I don’t see a compliancy "Follows best practices on SQL Server (or any other Microsoft product for that matter)". In my daily work I deal with a lot of Microsoft server products and I’ve been a DBA for quite some time now. I have some developer skills and I know about some of the best practices. I will always make sure my application works with the least privilege principle. And when I need to give permissions to objects I grant these permissions to a database role and map a login to the role.

So, SharePoint you ask. It works great with SQL, does it not?

• According to the manual some SharePoint (2007) account needs to be DBCREATOR and SECURITYADMIN. No, it does not. There is even documentation on this but it’s separate from the main installation documentation.
• If you pre-create the databases and make an AD-group with your SharePoint admins DB_OWNER, installation fails. Has to do with the script which creates some objects specific in the DBO schema and some in no schema. Which in turn means that SQL will create a schema for the user (because you made an AD-group DB_OWNER and not a single user) and create the objects in that schema. And then SharePoint cannot find the objects because there not in the DBO schema. But in all fairness, the documentation states clearly to give the Setup User account DB_OWNER permissions and not the AD group the Setup User account is in. So, that’s one on me trying to be smarter than SharePoint.
• If you are en experienced DBA you might consider creating separate file groups. Not with SharePoint. Some SharePoint databases will explicitly create objects in the PRIMARY file group.

But you cannot just blame SharePoint alone. Have you ever worked with SCOM 2007, or tried to install Reporting Services for SCOM integrated with SharePoint. I love to see Microsoft products work well together. But the best I’ve come across is OCS 2007 R2, which is a big improvement on OCS 2007. This Microsoft product uses another Microsoft product, SQL Server. Famous for scalability, consolidation etc. etc. And this product (OCS) needs to be installed on a SQL Server with no other databases on it. Just alone on its own instance. And that’s new too. There was a time you could not place a second instance on the same machine. I bet you cannot name the databases yourself either and the service account needs to be in the local administrators group and on SQL it needs the SYSADMIN, SECURITYADMIN and DBCREATOR server roles. Great.

It is completely beyond me why it seems impossible to follow some best practices Microsoft puts on its own site to be followed by its own products. And wait, SharePoint 2010 is way cooler because there are index tuning and maintenance timer jobs in SharePoint. So, behold DBA man, I’m SharePoint man and I will take care of those indexes for you.

Enough for now. There is good stuff to, don’t let that be misunderstood. And SQL is a complex product as is developing a good database application as big as SharePoint. My next post will be about reporting in SCOM and why not use the built-in Reports. That’s just me trying to change the Microsoft world one bit at a time.

On our SharePoint farm we we’re faced with the error:

An exception occurred while executing the alternate url synchronizer job.  Reason: http://portal is already routed to the Default zone of another application. Remove that mapping or use a different URL.  Techinal Support Details: System.InvalidOperationException: http://portal is already routed to the Default zone of another application. Remove that mapping or use a different URL.     at Microsoft.SharePoint.Administration.SPAlternateUrlCollection.Add(SPAlternateUrl alternateUrl, Boolean fUpdate, Boolean throwIfExists)     at Microsoft.SharePoint.Administration.SPAlternateUrlCollection.Add(SPAlternateUrl alternateUrl)     at Microsoft.Office.Server.Administration.AlternateUrlSynchronizerJob.Execute(Guid targetInstanceId)

In this case there is a child farm, say FARMB, that uses the Shared Service Provider from a parent farm. Let’s call that FARMA.

When you go to the central administration page on the parent farm to the Alternate Access Mappings you would see all your webapplications listed there. The same should be for the child farm. In our case this was the case. So clearly the url (http://portal) already existed on both farms. To find out the root cause of the error we fell back to the configuration database.

Connect to you’re configuration database on your parent farm and execute the following query:

SELECT * FROM Objects WHERE ClassId = ‘9920F486-2FF4-4D10-9532-E01979826585’;

The resultset will show you all the alternate access mappings on the parent farm. Do the same on the configuration database for the child farm. The columns to watch are the Id and Name column. In the name column you should recognize your webapplications. On the child farm you should see the same names but suffixed with a guid. For the webapplications that are the same on both farms, except the Central Administration, the Id column should show the same values.

If this is not the case, as in ours, you can delete the obsolete Id’s with the undocumented stsadm –o deleteconfigurationobject -id ‘valuefromidcolumn’. No fear, the Url Synchronizer timer job will recreate it and with the correct id.

Wait a few moments for the timer job to kick in and recreate the correct lines. The reason for this appeared to be a create-delete-recreate the Shared Service Provider mapping on the child farm. Something like an orphaned site.