This afternoon I had the pleasure of attending Kimberly Tripp's pre-conference session on Taming the SQL Server 2005 Tools. I don't think there are too many better ways to get acquainted with SQL Server 2K5 then 5 hours with Kimberly, she is amazingly familiar with 2K5, and not afraid to be enthusiastic about it, which if you have heard her on dnr (show1, show2), is quite obvious (some of the best DNR's ever).

The session started off discussing installation/upgrade, and some thoughts on setting up a secure server.

• ALWAYS set an SA pwd.. even if you are going to use strictly windows auth. You never know when you may need a backdoor in, and even though 2K5 will generate a random strong SA password for you if you don't specify one, good luck guessing it when you need to get into the server after the domain or your user accounts go belly up.
• SQL Server password policys filter down from the domain.. very cool
• She suggests running SQL Server in a low privilege local windows account. Hadn't thought about that, but certainly makes sense.
• NEVER use the local system account for your SQL Services on a domain controller. The account has access to alot more then whats needed for SQL. Of course this is only if you MUST put it on the domain controller in the first place.
• Upgrading may cause some pathing issues.. If you upgrade your databases, and they are in the default program files folders, your sql 2k5 files may be in mssql.1, while your data is ..\mssql\data. Not the end of the world, but ugly, and difficult to remember 6 months down the line. I'm sure your data files aren't in program files anyway
• We went through detaching and attaching a few databases. Remember, if you detach from 2k, and attach to 2k5, there's no going back. Make a copy, or do it from a backup. Also, upgrading your databases doesn't set the compatibility to 9, which is nice for your old software, but don't forget to keep upgrading on your mind.

Then we started to dig into the tools a bit. Kimberly is very excited about sqlcmd.exe, and rightfully so.

• sqlcmd.exe replaced osql.exe. It adds parameters (killer), initialization scripts (think constructor), and multiple connection handling ("go" those batches though!). Params can come in from the cmd line, environment variables, or good ole inline code. Management studio has full support for sqlcmd, also, giving syntax highlighting for the sqlcmd specific syntax, and the ability to run the scripts right in the IDE. sqlcmd.exe is very cool..
• The SQL Configuration Manager replaces a number of screens, including the server properties dialog from Enterprise manager, the client network utility, while also allowing for service management. Seems like a nice way to combine the management, but nothing earth shattering here. Oh, and the April CTP has a bug where it eats memory, but this has been fixed in the next CTP (rumor has it, we will see it very soon.)
• SQL Server 2K5 is now more Windows 2K3 like, borrowing the off by default concept. This means SQL Server will ship secure, and only you can unsecure it (minus any yet to be found vulns). To manage this, SQL Server Surface Area Configuration tool provides a means to view, and change these "off by default" settings. In other words, your xp_cmdshell code will fail, until you go in and remind the server, yeah, I really do want that.
• The management studio is a night and day change. If you are a vs.net developer, expect your SQL Server experience to feel more at home. If you are not, though, expect to get used to a whole new IDE, thats nothing like the past. Projects, solutions, and source control built right in, this isn't your Daddy's enterprise manager/query analyzer. You really just need to play with it to see. I've been using it for a while now as my primary way to manage SQL Server, and although there are still some issues, I see that this tool will make SQL Server much easier to work with. Of course, I spend most of my life in vs.net anyway, so anything that gets it closer is cool with me.
• Want just query analyzer? Check out the management app that comes with SQL Express.. It's free, and light, just like the old Query Analyzer.

She covered alot more then this too. Here are a few more nuggets I picked up..

• Fast file initialization will help speed up many formally slower operations, such as restores, growing filesets. This is turned off by default, but is a service setting. You can turn it on by adding your service user to the Perform Value Maintence Tasks item in the local security settings. In Kimberly's tests, she was able to take a restore that took 12 hours down to 6.
• The dedicated administrator connection allows an admin to connect through some reserved resources, no matter what the system is up to. By default, this is only allowed from localhost, but can be changed in the (you guessed it) Surface Area Configuration tool.
• Detaching a 2k5 database also brings catalogs. It's been a while since I've worked with a full text catalog, but I'd imagine this would be a lifesaver if you need it.

Now I just feel like I'm rambling. It's late, I'm tired... Teched day 0 done... More to come...