Thursday, May 5, 2016

The server network address “TCP://SQLServer:5022” can not be reached or does not exist.

Thanks to Pinal Dave:
Fix/WorkAround/Solution: Try all the suggestions one by one.
Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).
Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).
Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 5023”.
Suggestion 4: Make sure your firewall is turned off.
Suggestion 5: Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. You can start end point by executing an ALTER ENDPOINT statement.
Suggestion 6: Try the following command as one of the last options.
GRANT CONNECT ON ENDPOINT::Mirroring TO ALL
Suggestion 7: Delete the end points and recreate them.


Wednesday, March 9, 2016

SP4 on SQL Server 2005 Fails_your account Information could not be verified



SP4 on SQL Server 2005 Fails 



  1. On the active node, use failover cluster manager to pause all passive nodes
  2. On the active node, run and complete the SQL 2005 SP4 installation
  3. On the active node, use failover cluster manager to resume all previously paused nodes
  4. On all passive nodes, run and complete the SQL 2005 SP4 installation using the /passive command line switch
  5. Reboot all node
This will ensue that any necessary system databases are updated as well, as opposed to only the binaries. This can be verified using the sql code below:
SELECT SERVERPROPERTY('productversion') as [SQL Version],
SERVERPROPERTY('productlevel') as [level],
SERVERPROPERTY('edition') as edition,
SERVERPROPERTY('resourceversion') as [Resource Version]



Wednesday, February 3, 2016

Change SQL Server Collation @ Instance Level

To change the collation at the instance level we need to follow below steps:

1. Go to Setup.exe path, this will be normally as follows:
     C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\

2. Take the SQL Services Offline.

3.  Go to command prompt and choose above location:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= (INSTANCE NAME) /SQLSYSADMINACCOUNTS= (SVC ACC) /SAPWD=(SAPWD) /SQLCOLLATION=SQL_Latin1_General_CP850_BIN 

Instance name:

To verify the instance name  use below query
select SERVERPROPERTY('instancename')

SVC Acc:

Use service account or any domain account which has full permissions on email.

SAPwd:

If its a mixed authentication mode then we need to use SA pwd to complete the rebuild.

4. Check the summary log once its completed for verification, we can verify under instance properties also.

Imp Note: Please take system db backups to restore once we complete rebuild. Else we will loose all SQL Server jobs and Logins.