Wednesday, August 22, 2012

SQL Server 2012 Database mirroring error (Microsoft SQL Server, Error: 1418)


While setting up mirroring between 2 servers I came across following error
The server network address TCP://<servername>.<domain>.com:<portnumber> can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

After setting up database mirroring security configuration I started mirroring between 2 servers and immediately I got this error. The error however does not tell you much about the actual problem. I tried changing firewall setting, checked that the service account is correct on both primary and mirror. I also tried ping from either machines and it worked fine.

But after checking the BOL I found the issue. Before setting up mirroring you have full backup database on primary and restore it on mirror as “WITH NORECOVERY”.

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence. In this case, the restore sequence can restore other backups and roll them forward.

After restoring with NORECOVERY I did a log backup on primary and restored it on mirror. Once done mirroring worked as expected.

Please note that in SQL Server 2008 & R2 mirrored copy is not readable. In SQL Server 2012 with help of Always On availability groups you can make mirrored copy readable.

Tuesday, August 21, 2012

SQL Server 2012 Failover cluster error (Windows Event ID 1119)


Recently I installed SQL Server 2012 RTM on a Windows failover cluster. After installation when I tried to fail over SQL server resources from N1 to N2 I got following error
Cluster network name resource 'SQL Network Name (MyServer)' failed to register DNS name 'MyServer.' over adapter 'Local Area Connection*' for the following reason:  'DNS server failure.'

I found the event ID and resolution on technet

But it was nothing to do with the solution mentioned on technet article.

My problem was SQL server service account was not part of the Local Administrators group. (Which I should have done before installing…dumb!!)
So here is how you add SQL service account to local
1)      Go to Start à Administrative Tools à Server Manager
2)      Expand Configuration
3)      Expand Local Users and Groups
4)      Select Groups
5)      Double click on Administrators
6)      Click on Add button
7)      Enter SQL Service account (Account under which SQL services are running)
8)      Click OK
After doing this I was able to failover SQL Server to passive node

Friday, August 17, 2012

Microsoft Security Bulletin MS12-060 – Critical


On Aug 14th 2012 Microsoft released security bulletin which includes an update for SQL Server. Please read the details in below link

As stated updates included in KB2598041 which was part of security bulletin MS12-027 (http://www.microsoft.com/en-us/download/details.aspx?id=29372) are replaced with KB2687441 (http://support.microsoft.com/kb/2687441)

So important question is do you need to install this security update?

If you look under the replacement update KB2687441 then the it applies only to the host machines which have Microsoft Office installed on it. Specifically
2007 Microsoft Office Suite Service Pack 3
2007 Microsoft Office Suite Service Pack 2
Usually it is not a standard practice to install MS Office on database servers so most likely you do not need this security update.
In case you do have Microsoft Office installed on one of you database servers and have one of the following versions of SQL Server installed then you need to install this security update
Microsoft SQL Server 2000 Service Pack 4
Microsoft SQL Server 2000 Analysis Services Service Pack 4
Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 4
Microsoft SQL Server 2005 for 32-bit Systems Service Pack 4
Microsoft SQL Server 2005 for x64-based Systems Service Pack 4
Microsoft SQL Server 2005 for Itanium-based Systems Service Pack 4
Microsoft SQL Server 2008 for 32-bit Systems Service Pack 2
Microsoft SQL Server 2008 for 32-bit Systems Service Pack 3
Microsoft SQL Server 2008 for x64-based Systems Service Pack 2
Microsoft SQL Server 2008 for x64-based Systems Service Pack 3
Microsoft SQL Server 2008 for Itanium-based Systems Service Pack 2
Microsoft SQL Server 2008 for Itanium-based Systems Service Pack 3
Microsoft SQL Server 2008 R2 for 32-bit Systems
Microsoft SQL Server 2008 R2 for 32-bit Systems Service Pack 1
Microsoft SQL Server 2008 R2 for 32-bit Systems Service Pack 2
Microsoft SQL Server 2008 R2 for x64-based Systems
Microsoft SQL Server 2008 R2 for x64-based Systems Service Pack 1
Microsoft SQL Server 2008 R2 for x64-based Systems Service Pack 2
Microsoft SQL Server 2008 R2 for Itanium-based Systems
Microsoft SQL Server 2008 R2 for Itanium-based Systems Service Pack 1
Microsoft SQL Server 2008 R2 for Itanium-based Systems Service Pack 2