SQL Cluster Failure

Yesterday one of our developer informed me that they are not able to connect to SQL Server. When i checked it in cluster manager, the services found failed and i could see the below error in event log.

  

Reason: SQL Service account password got changed and its not updated against the service.

Solution: Updated the password against the services and brought the resource online.

3203 - Restore Error SQL Server 2008

One of my friend got the below error while restoring a DB. When we verify the file, it gave us valid file, but the restore was not working using GUI. 

Resolution
Restore the DB with MOVE command using TSQL.

Assign explicit permission to User Defined SPs

/*------------------------------------------------------------------------------
Assign explicit permission to User defined SPs
1) Correct the DB name in USE statement before the execution.
2) Assign the correct user to the @USER.
-------------------------------------------------------------------------------*/
SET NOCOUNT ON
USE TEST -- CHANGE THE DB HERE
DECLARE @USER VARCHAR(50)
SET @USER='TEST2'----CHANGE THE USER HERE
DECLARE  @SECUREPERMISSION TABLE
(
ID INT IDENTITY(1,1),
OBJNAME VARCHAR(100),
SCHNAME VARCHAR(100)
)

INSERT INTO @SECUREPERMISSION
SELECT O.NAME,S.NAME FROM SYS.OBJECTS O
INNER JOIN SYS.SCHEMAS S ON O.SCHEMA_ID=S.SCHEMA_ID
WHERE TYPE='P'

DECLARE @COUNT INT, @PROCESSCOUNT INT, @CMD VARCHAR(200)
SET @COUNT=1
SELECT @PROCESSCOUNT= COUNT(ID) FROM @SECUREPERMISSION
WHILE @COUNT<=@PROCESSCOUNT
BEGIN
SET @CMD='GRANT EXECUTE ON'
SELECT @CMD= @CMD +' '+SCHNAME+'.'+ OBJNAME +'' FROM @SECUREPERMISSION WHERE ID=@COUNT
SET @CMD=@CMD+ ' TO ' +@USER
EXECUTE (@CMD)
--SELECT @CMD
SET @COUNT=@COUNT+1
END
SET NOCOUNT OFF

Report Server DNS Issue

I had an issue with my report server that the report manager URL is not working when i use it like below.
http://servername/reports
But it was working when i gave the IP address instead of hostname.
eg: http://192.168.10.1/reports
Resolution:
In order to overcome the issues with URLs and constant credential requests I changed the following settings in RSReportServer.config:

Original:
<Authentication>
      <AuthenticationTypes>
             <RSWindowsNegotiate />
             <RSWindowsNTLM />
      </AuthenticationTypes>


Adjusted to:
<Authentication>
      <AuthenticationTypes>
             <RSWindowsNTLM />
      </AuthenticationTypes>


Such changes were necessary due to the fact that there is no correct SPN set for the service account used by SSRS. If Kerberos authentication is required the correct SPN needs to be set up in prior.

Unable to begin a distributed transaction using linked server.

One of my client reported me that they are getting below given error when they were executing a query similar to below.


insert into <Table Nam> exec <LinkedServer Name>.<schema>.<DB Name>.<SP Name>


OLE DB provider "SQLNCLI" for linked server "LinkedServer Name" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "LinkedServer Name" was unable to begin a distributed transaction.

The below Setting Resolved the issue. I had to do the same in both servers.

1. open up "Component Services"

Control Panel > Administrative tools > Component Services

2. in Component Servies, right click "My Computer" and select "Properties"

Console Root > Component Services > Computers > My Computer

3. select the "MSDTC" tab (Select appropriate MSDTC if its clustered) , and click "Security Configuration" in the "Transaction Configuration" group box

4. Enable Allow inbound and Allow outbound.

5. in "Security Settings" > "Transaction Manager Communication"

select "No Authentication Required"

6. restart the DTC service (should do so automatically).

"Microsoft Cluster Service (MSCS) cluster verification errors" failed.

One of my friend received the below shown error during the add node installation of SQL Server 2008 on a Windows Server 2008 machine.

Solution: If we are sure that the cluster verification doesn't have any issue, we can complete the installation using the below commands.

For an integrated failover Add-Note setup, run the following command on each node that is being added:

Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster

For an advanced or enterprise installation, run the following command:

Setup /SkipRules=Cluster_VerifyForErrors /Action=CompleteFailoverCluster

If you receive this validation failure when you add a node to an existing failover installation, run the following command on each node that is being added: 

Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode

Table Diff to identify data mismatch.

1) Run the below query in Publisher.
2) Paste the output in Command Prompt.
3) See the output file for the mismatch.


Declare @sourceserver nvarchar(100) = '<Publisher Server>'
Declare @destinationserver nvarchar(100) = '<Subscriber Server>'

select '"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver [' + @sourceserver +
'] -sourcedatabase [BCP_Tran] -sourcetable [' + name +'] -sourceschema [' + SCHEMA_NAME(schema_id ) +
'] -sourcelocked [TABLOCK] -destinationserver [' + @destinationserver + '] -destinationdatabase [BCP_Tran] -destinationtable [' + name +
'] -destinationschema ['+ SCHEMA_NAME(schema_id )  +'] -destinationlocked [TABLOCK] -f c:\TableDiff_' + name+ '.sql'
      from  sys.tables 
      where type_desc = 'USER_TABLE' and is_published=1
      

SQL Server 2008 Cluster Collation Change

1. First make sure you have all the database backups including system            database.
2. Generate scripts for all the logins, Linked Servers, Server Triggers etc.
3. Generate scripts for all the Jobs exist on server.
4. Reset the SA password if you don’t know. And save it to safe place.
5. To change the collation for SQL Server 2008, If we are changing from same node, First we need to take resources offline (SQL Server, SQL Agent, Fulltextsearch) and run the below syntax on command prompt from SQL Server Setup directory.




C:\SQL>Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENA
ME=INST1 /SQLSYSADMINACCOUNTS="Domain\Account" /SAPWD=<SA Password> /SQLCOLLATION=<New Collation>

Cross Domain Transactional Replication

We had the below error in my environment during cross domain pull transactional replication. To resolve this we have the below two options.

First Method

1) Generate the snapshot and manually copy the same to subscriber.

2) Change the Snapshot Location in the subscriber properties. Since the snapshot location is local, it will apply the same very fast.

Second Method

1) Keep the replication as it is.

2) Generate the BCP out of the articles.

     bcp BCP_Tran.dbo.Test out c:\Test.dat -c -U sa -P <Pword>-S <Srvr Name>

3) Apply the BCP file to the subscriber.

     bcp BCP_Tran.dbo.TestNew in c:\Test.dat -T -c -S <Server Name>