One of the bloggers I follow is Brent Ozar. I tell you I’m a fan. I like the way he writes his stuff.

Anyway, he blogged about The Problem With SQL Server Training Today, and The Answer to SQL Server Training Problems a week or so ago. And this is the answer – SQLServerPedia  – SQL Server Knowledge and Advice Straight from the Experts.

So in between giving out candies to cute little Indiana Joneses, pirates of the Caribbean, vampires and dead cheerleaders, I’ve been soaking in SQLServerPedia (geeky ‘no?). So far, so good. I’ll be a frequent flyer.

PS – and if you’re a DBA, you’ll find great laughs in this post : Somebody Created a Halloween Monster #SQLputdowns

Problem:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘SQLVMSQL02’. (Replication.Utilities)

Scenario:

1. We configured database mirroring on 2 SQL Server instances.

2. We changed the computer name from SQLVM to SQL2K5.

3. We tried launching the Database Mirroring monitor, but we got the following error:

SQL Server replication requires the actual server name to make a connection 

to the server. Connections through a server alias, IP address, or any other 

alternate name are not supported. Specify the actual server name, 'SQLVMSQL02'. 

(Replication.Utilities)

If you just look at the error, it sounds a bit misleading because we don’t have replication configured in either of the servers.

Resolution:

-- 1. drop old server/instance name

EXEC sp_dropserver [sqlvmsql01] 

GO

-- 2. add newserver/instance name

-- local meaning we're adding the local machine

EXEC sp_addserver [mysql2k5sql01], local 

GO

-- 3. Restart SQL Server service

I previously posted about a SQL Server CheatSheet from DotNet4All that I thought was useful and comprehensive. If you already know what you need to do, but just forgot the syntax because you haven’t done it in eons, then  a well organized, concise, complete cheat sheet can save the day.

Here are a few other ones:

General SQL Server
DotNet4All

SQLAuthority
Scribd T-SQL

SQL Server Shortcuts
SQLArticles (really good, exhaustive list!)
SQLAuthority SSMS Shortcuts

Connection Strings
ConnectionStrings.com

Regex (especially when you’re working with SQLCLR/Regex)
.NET Regex Cheat Sheet
AddedBytes
(AddedBytes used to be known as ILoveJackDaniels, but that name has changed. Dave has a *lot* of useful cheat sheets in his site.)

SQL Injection
Ferruh Mavituna
ha.ckers

PowerShell
MSDN Blogs (PDF)
MSDN Blogs (MS Word)

SQL Server 2008
SQLAuthority

SQL Server DBA Interview Questions
SQLAuthority Interview Questions – Complete List

Problem:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."

Scenario:

Sometimes you need to have "exclusive" access to the database, let’s say,

• when you want to force a restore, or
• when you want to alter the database and an exclusive lock is required

To get this exclusive access, you may need to set the database to SINGLE_USER mode:

USE [master]

GO

ALTER DATABASE [DBName]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

However, what if you accidentally lock yourself out of the database?

What if for some reason there is some other connection or user that is using the database, you will most probably get this error :

"Msg 5064, Level 16, State 1, Line 1

Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed."

How do you get yourself back in?

Resolution:

1. Determine which connection is actually locking you out

-- look for the connection that uses your db and note the spid

EXEC sp_who

2. Kill the connection

KILL <spid>

3. Change the database back to MULTI_USER

ALTER DATABASE [DBName]

SET MULTI_USER;

GO

That should do it.

It’s funny. I wonder which database they are using

http://thedailywtf.com/Articles/A-Low(null)-Diet.aspx

1. Create your Visual Studio Project

2. Add the following references in your project

• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.SmoEnum
• Microsoft.SqlServer.SqlEnum

3. Include the following using statement:

using Microsoft.SqlServer.Management.Smo;

4. Add your server object. Normally, you would use the following format in your Server object:

String serverName  = "JUBILEE";

Server srv         = new Server(serverName);

Database db        = srv.Databases["AdventureWorks"];

txtServerInfo.Text = srv.Information.VersionString +

                     System.Environment.NewLine;

However, if you have an instance name, you have to specify the SERVERINSTANCE name in your Server object. In C# code:

Server srv         = new Server(serverName);

Database db        = srv.Databases["AdventureWorks"];

txtServerInfo.Text = srv.Information.VersionString +

                     System.Environment.NewLine;

Here is a partial list of valid XSD data types for SQL Server 2005:

Note that in SQL Server 2005, the timezone needs to be included in the date or dateTime element or attribute value, for example:

<Authors>

    <Author AuthorID="1" FirstName="John" LastName="Doe" 

            IsIndependent="true" 

            DateJoined="1992-05-01Z" DateTimeJoined="1992-05-01T00:00:00Z"/>

</Authors>

Note the existence of the letter Z at the end of the date and datetime attributes.

Microsoft also provided a list of schemas in http://schemas.microsoft.com/sqlserver/. It includes the following AdventureWorks sample schemas:

• AdventureWorks Customer Contact Information Schema
• AdventureWorks Customer Contact Record Schema
• AdventureWorks Customer Contact Type Schema
• AdventureWorks Consumer Demographics Survey Schema
• AdventureWorks Product Catalog Schema
• AdventureWorks Manufacturing Instructions Document Schema
• AdventureWorks Product Warranty and Maintenance Information Schema
• AdventureWorks Standard Resume Schema
• AdventureWorks Retail Stores Demographics Survey Schema

Brian Knight has posted a lot of good videos on JumpStartTV, including the following clustering videos:

• Windows and SQL Server 2005 Clustering Architecture  (16:14)
  http://www.jumpstarttv.com/windows-and-sql-server-2005-clustering-architecture_31.aspx
• Clustering Windows 2003 R2  (13:26)
  http://www.jumpstarttv.com/clustering-windows-2003-r2_32.aspx
• Clustering SQL Server 2005  (16:21)
  http://www.jumpstarttv.com/clustering-sql-server-2005_33.aspx

Plus one video on how to hack (read: SQL Injection), and prevent hacking, SQL Server:

• Hacking SQL Server   (55:59)
  http://www.jumpstarttv.com/hacking-sql-server_76.aspx

These are good videos, some of them almost an hour long like the Hacking video, most in the 5-15 minute duration. What’s also good about the site is  on the sidebar, the “pre-requisite” videos are mentioned.

JumpStartTV also has videos on Administration, High Availability, Performance Tuning, SMO, Data Warehousing and SSIS (Brian Knight made a whole bunch on SSIS), to name a few.

The videos are free, but JumpStartTV requires registration.