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.

SQL Server Error 3257 – Causes and Solution

Overview

While working with SQL server, users may encounters numerous errors in different stages. These errors creates huddles in SQL transactions and its working, which sometimes vanishes the smooth working of SQL server.

SQL Server error 3257 is one of such error that resist database restoration in the system due to insufficient free disk space volume. In this article we will go through the reasons that causes SQL error 3257 and its resolution.

Reason Behind Occurrence Of SQL Server Error 3257

In many scenarios the data stored in the system get inaccessible or corrupted, In order to recover a SQL server database from these damages, Database administrator can restore SQL server database from it’s backup. While restoring the database from the SQL backup, an issue arises whenever there is insufficient disk space available in the system. The system will show below mentioned message or warning whenever the situation like insufficient disk space available while restoring the database.

Msg 3257, Level 16, State 1, Line 1
There is insufficient free space on disk volume '< volume >' to create the database. The database requires additional free bytes, while only bytes are available.

Resolving Steps for SQL Server Error 3257

In order to resolve the SQL server 3257 error, one should keep check on following points. Below are some pre- requisite that will help the admin for the restoration of backup files in the system.

• Before taking the backup, admin must firstly verify the total space database actually requires and the total free space is available in disk before taking the backup. To identify the free disk space, DBA can use RESTORE FILELISTONLY command that allows user to see a list of the files that were backed up and also the free space require for restoring the backup files.
  
  RESTORE FILELISTONLY FROM DISK='backup_file_location'
  
  this command will verify the actual backup process.
• The Restore command also used for identifying the header information of all backup files
  RESTORE HEADERONLY FROM DISK = ‘H:\test1.bak'
• Once the free pace in disk is identified then admin can go for altering the initial file sizes using the Alter command and also can reduce the size of backup by shrinking it.
• If any other hard drive has enough space to store database then user can change the location for restoring the backup i.e where user want their Physical files to be.
• If there is no enough space in hard drive then DBA can check the Multiple drive free space and then they can restore their backup files accordingly.
• Then DBA can take the backup of the database and restore it on desired location.

Conclusion:

After going through reasons that are responsible for the occurrence of Error 3257, DBA must identify the availability of free disk space and the verify the size of actual backup process. Admin can also shrink the size of backup files before restoring the database.