Microsoft SQL Server 2016 Cumulative update 1 (CU1)

Microsoft just announced Microsoft SQL Server 2016 Cumulative update 1 (CU1)

Microsoft reference: [Click Here]

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Linkedin

How to Change an Analysis Services Instance to Tabular Mode

One of my client accidentally set up an SQL Server Analysis Services in multi-dimensional mode instead of Tabular mode. In this post I will show a procedure to change Analysis tabular mode in SQL Server 2016. 

Analysis Services Modes 

Analysis Services can be installed in one of three server modes: Multidimensional and Data Mining (default), Power Pivot for SharePoint, and Tabular. The server mode of an Analysis Services instance is determined during setup when you choose options for installing the server.

You can check the DeploymentMode property in the msmdsrv.ini file that is included in every Analysis Services instance. The value of this property identifies the server mode. Valid values are 0 (Multidimensional), 1 (SharePoint), or 2 (Tabular).

Although changing DeploymentMode property it is not supported by Microsoft, it works.

How to Change an Analysis Services Instance to Tabular Mode

Read My Complete Article "Here" 

Change the Collation Setting for Clustered SQL Server 2014 Instance

One of our SQL Server 2014 clusters was installed and after the install, my client told me that we are supposed to change the SQL Server collation setting. In this post I will show a procedure to change the collation settings of a clustered SQL Server instance. 

To change the default SQL Server collation you have to rebuild the system databases. Before you rebuild the system databases, review the Microsoft MSDN documentation regarding backup of your system databases and logins.

• https://msdn.microsoft.com/en-in/library/ms190190(v=sql.120).aspx
• https://support.microsoft.com/en-in/kb/918992

When you rebuild the master, model, msdb and tempdb system databases are actually dropped and recreated in their original location.

Change SQL Server Collation Setting for a Clustered SQL Server Instance.

Read My Complete Article "Here" 

Configuring Integration Services in a SQL Server 2016 Cluster

In this post I will show a procedure to configure Integration Services in a SQL Server 2016 Cluster.

Clustering Integration Services is not recommended because the Integration Services service is not a clustered or cluster-aware service, and does not support failover from one cluster node to another. Therefore, in a clustered environment, Integration Services should be installed and started as a stand-alone service on each node in the cluster.

Although the Integration Services service is not a clustered service, you can manually configure the service to operate as a cluster resource after you install Integration Services separately on each node of the cluster.

How to configure Integration Services as a cluster resource

Read My Complete Article "Here" 

Microsoft SQL Server 2016 Service Pack 1 (SP1)

Microsoft just announced Microsoft SQL Server 2016 Service Pack 1 (SP1)

Microsoft reference: [Click Here]

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Linkedin

Resolving an Error When Creating an Availability Group Listener

Resolving an Error When Creating an Availability Group Listener.

In this post, I’m going to talk an issue that I found when creating an Availability Group (AG) listener by using SQL Server Management Studio's Add Listener dialog box, you may receive below error message that includes information about the cause of the error, which can help you to resolve the issue.

The WSFC cluster could not bring the Network Name resource with DNS name '<DNS name>' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.

Read My Complete Article "Here" 

Ganapathi varma Chekuri

Linkedin

Why SQL Server Database Is In Recovery Mode?

The SQL Server whenever it restarts all the databases goes into the recovery mode. In this state the database comes back in online mode and has a consistent state. It contains three sub phases within this process. Roll forward and rollback are the process contained in this phase. The detailed explanation for the above process is given below in details:

• Analysis: In this phase the SQL Server would go through the LDF file and build in-memory structures to know how much work is needed to be done in the next two phases.
• Roll Forward (Redo): At the time of shutdown of the database, there might be some transactions that are committed but are still not written to the MDF file via checkpoint.
• Rollback (Undo): In some states the transactions sometimes remains uncommitted and in order to bring the consistent state the database needs to be rolled back.

In this article we will discuss why SQL databse is in recovery mode and methods that the user should adopt in this recovery mode.

Reasons Why SQL Database Is In Recovery Mode

The reason why SQL Server database is in recovery mode is as follows:

1. While Restarting the SQL Server.
2. When the Database is Set Offline and Online.
3. Restoring the database from backup.

All the above reasons mentioned are called Recovery process of the database and all the databases must go through these processes as explained earlier.

Methods to Be Performed when the Database Stuck in Recovery State :

First thing, always check for ERRORLOG. In Errorlog the first message displayed is:

Starting up Database ‘Customer’ (Name of the Database).

It means database files are opened and recovery process is started. After this the user will be able to see method 1like this:

Recovery of database ‘Customer’ (40) is 0% complete (approximately 37 seconds remain). Phase 1 of 3.

Recovery of database ‘Customer’ (40) is 3% complete (approximately 36 seconds remain). Phase 1 of 3.

After the method 1 is performed the recovery mode will go to method 2 and method 3 like this:

Recovery of database ‘Customer’ (40) is 3% complete (approximately 36 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 0% complete (approximately 142 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 7% complete (approximately 19 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 15% complete (approximately 26 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 21% complete (approximately 25 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 34% complete (approximately 20 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 41% complete (approximately 16 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 48% complete (approximately 14 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 55% complete (approximately 12 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 62% complete (approximately 10 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 75% complete (approximately 7 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 82% complete (approximately 5 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 88% complete (approximately 3 seconds remain). Phase 2 of 3.

Recovery of database ‘Customer’ (40) is 95% complete (approximately 1 seconds remain). Phase 3 of 3.

And once all the above three methods are performed the results will be shown as follows: 3807 transactions rolled forward in database ‘Customer’ (40).

0 transactions rolled back in database ‘Customer’ (40).

Recovery is writing a checkpoint in database ‘Customer’ (40).

Recovery completed for database Customer (database ID 40) in 30 second(s) (analysis 1409 ms, redo 29343 ms, undo 72 ms.)

This result shows the three phases of recovery mode in SQL Server explained earlier.

Conclusion

In this technical article we discussed why SQL database goes into recovery mode and what the user must do in this stage. Then, we described some methods to be performed during the time of the SQL recovery process. Hope, this might be helpful to all the SQL users. There are some cases in which the user is unable to recover the SQL database properly or stuck in recovery state. So it is recommend to use the utility like SQL Database Recovery which is able to quickly recover the SQL database and all its objects.