Blank space between startup parameters

I was trying to figure out why my startup trace flags don't work. While it is mentioned in this Microsoft TechNet article about using semi-colons to separate one startup parameter from another, nothing was mentioned about the leading blank space in between defining the parameters. SQL Server MVP Erland Sommarskog recommended removing the leading blank spaces in between the trace flags and parameters like the one in the example below

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf;-T1204;-T1222;-T845;-T1262;-T2330

The best way to check if your trace flags are actually enabled during startup is to run the DBCC TRACESTATUS command or, as Erland suggested, looking at your SQL Server error log and note whether the trace flags are aligned with the other SQL Server parameters like the one below. Notice how aligned the startup parameters are (you can see that the - symbol are all aligned). A mere leading blank space would definitely cause them not to work.

2009-11-05 10:49:36.010 Server Registry startup parameters:
2009-11-05 10:49:36.010 Server -dC:\Program Files\Microsoft SQL Server\MSSQL\DATA\master.mdf
2009-11-05 10:49:36.010 Server -eC:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG
2009-11-05 10:49:36.010 Server -lC:\Program Files\Microsoft SQL Server\MSSQL\DATA\mastlog.ldf
2009-11-05 10:49:36.010 Server -T1204
2009-11-05 10:49:36.010 Server -T1222
2009-11-05 10:49:36.010 Server -T845
2009-11-05 10:49:36.010 Server -T1262
2009-11-05 10:49:36.010 Server -T2330

NT AUTHORITY\NETWORK SERVICE account in SharePoint Content Database

I have had the opportunity to assist a WIndows SBS MVP migrating Windows SharePoint Services 3.0 databases from SQL Server 2005 to SQL Server 2008 in a Windows SBS 2008 environment. He was following the steps outlined in this Microsoft TechNet article to move the content databases and configure them from inside SharePoint Central Administration when he hit a wall and couldn't move forward. When he was at the point where he is already pointing to the new content database, SharePoint started throwing an error. My first instinct was that it has something to do with permissions but I need to capture some activity before even doing anything. Apparently, SQL Profiler is not installed. I opted to change the Authentication mode from Windows to Mixed mode just so I can use a SQL Server login with the SharePoint configuration. After creating a SQL Server login for SharePoint to use, everything was back to normal. While I wasn't comfortable with using Mixed Mode authentication for SharePoint, I dug deeper to see what Windows account was being used thru the Application Pool from within IIS. It appears to be NT AUTHORITY\NETWORK SYSTEM - the default account when you install Windows SharePoint Services 3.0 in the same server as the SQL Server (this is a common setup with Windows SBS). What I did was to add the NT AUTHORITY\NETWORK SYSTEM account in SQL Server using the CREATE LOGIN command (you won't be able to do this in Management Studio as the account is not exposed) and, then, assign the db_owner role in the content database.

I went back to the SharePoint Central Administration after that to switch the configuration of the content database from using a SQL Server login to using Windows authentication. That did the trick.

Note that if you are moving your SharePoint databases from SQL Server 2005 to SQL Server 2008, whether within Windows SBS or a full blown Windows Server system, make sure you check the logins as they need to be moved as well for the SharePoint application to work. You can even use the transfer SQL Server logins script from Microsoft to do this

SQL Server Deep Dives book now available

I have had an opportunity to be a part of a wonderful project that took almost a year in the making.

SQL Server MVP Deep Dives is not your ordinary SQL Server book as it brings together the world's most highly-regarded SQL Server experts to create a masterful collection of tips, techniques, and experience-driven best practices for SQL Server development and administration. These SQL Server MVPs—53 in all—each selected a topic of great interest to them, and in this unique book, they share their knowledge and passion with you.

SQL Server MVP Deep Dives is organized into five parts: Design and Architecture, Development, Administration, Performance Tuning and Optimization, and Business Intelligence. Within each part, you'll find a collection of brilliantly concise and focused chapters that take on key topics like mobile data strategies, Dynamic Management Views, or query performance. The range of subjects covered is comprehensive, from database design tips to data profiling strategies for BI.

The authors of this book have generously donated 100% of their royalties to support War Child International. War Child International is a network of independent organizations, working across the world to help children affected by war. War Child was founded upon a fundamental goal: to advance the cause of peace through investing hope in the lives of children caught up in the horrors of war. War Child works in many different conflict areas around the world, helping hundreds of thousands of children every year. Visit www.warchild.org for more information.

If you are attending the PASS Community Summit 2009 this year at Seattle, WA, you can get your copy there. And since most of the authors are SQL Server MVPs, you will have a chance to have your book signed during the conference. I believe that there is also a book launching activity that will occur during the summit so watch out for that

Learn SQL Server thru videos

I'm passionate about learning and helping people develop their full potential thru continuous personal and professional development. So everytime I chance upon something that would be of help to somebody else, I pass it along

MSDev.com has tons of resources to help IT professionals and developers learn more about technology. Check out this link for SQL Server-related resources