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.

Simple Way to Decrypt SQL Server Stored Procedure

When stored procedures are created in SQL Server, their entire text body is accessible to all those who have the required permissions for accessing the data. Therefore, it becomes very easy to expose the underlying object content by creating stored procedures and analyzing it content via SQL Server Management Studio, Query Analyzer, Windows PowerShell, or any commercial utility. This data transparency, as a result, poses a risk of data compromises by the potent cyber criminals. Therefore, SQL Server developers consider encryption, the most suitable way to authenticate their data.

Need For Decrypting SQL Server Stored Procedure

Even though, encryption of stored procedures of SQL Server ensures that the objects cannot be accessed and read easily, at times it poses some issues to the users. Being a SQL Admin, I have come across many issues where the users no longer had access to their decryption script and therefore were not able to decrypt the database when required.

In certain scenarios, it happens that the administrators are handed over encrypted, SQL databases to work on. In order to work with them, the first thing that the admin needs is the encryption script and in the absence of it, the admin go for decrypting the database.

Procedure to Decrypt Stored Procedure in SQL Server

The first thing that needs to be done is to open a DAC (Dedicated Administrator Connection) to the SQL Server. It is to be noted that the DAC can only be used in three conditions:

• The user is logged in the server.
• The user is using a client on that server.
• The user holds the sysadmin role.

Keep in mind that the DAC will not work if the user is not using TCP/IP and will show cryptic error if TCP/IP is not used.

The process is mainly divided into three sections:

1. The first step is to get the encrypted value from sys.sysobjvalues via DAC connection.
2. The next step is to take out the encrypted value of some blank procedure.
3. Get the unencrypted blank procedure statement in plaintext format. Apply XOR to all the results. XOR is the simplest decryption procedure and is the basic algorithm used in MD5.

SET NOCOUNT ON
GO
ALTER PROCEDURE dbo.MyDatabase WITH ENCRYPTION AS
BEGIN
 PRINT ‘This is decrypted database’
END
GO
DECLARE @encrypted NVARCHAR(MAX)
SET @encrypted = (
  SELECT imageval
  FROM sys.sysobjvalues
  WHERE OBJECT_NAME(objid) = ‘TestDecryption’)
DECLARE @encrypedLength INT
SET @encryptedLength –DATALENGTH(@encrypted) / 2
DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N ‘ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS’
SET @procedureHeader = @procedureHeader + REPLICATE(N ‘-‘,
(@encryptedLength –LEN(@pocedureHeader)))
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptMessage = ‘’
SET @cnt = 1
WHILE @cnt <> @encryptedLength
BEGIN
 SET @decryptedChar = 
         NCHAR(
              UNICODE(SUBSTRING(
                     @encrypted, @cnt, 1)) ^
              UNICODE(SUBSTRING(
                     @procedureHeader, @cnt, 1)) ^
               UNICODE(SUBSTRING(
                     @blankEncrypted, @cnt, 1)) ^
       )
  SET @decryptedMessage = @decryptedMessage + @decryptedChar
 SET @cnt = @cnt + 1
END
SELECT @decryptedMessage

Conclusion

SQL Server database encryption ensures database authenticity from unwanted users. However, this may at times pose a problem for the user. With the help of the above-mentioned script, the user can easily decrypt their stored procedures in SQL Server. However, if the above procedure doesn’t work for you, then going with a third party SQL decryptor tool is the best solution.

What's New in SQL Server 2016 Release Candidate 3

Microsoft announced SQL Server 2016 Release Candidate 3 Evaluations

Benefits of SQL Server 2016 Release candidate 3:

• Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk based relational databases and real-time operational analytics
• New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes
• Built-in advanced analytics– provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database
• Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android
• Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology
• Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner  without application changes
• Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure

For download, Microsoft reference: [Click Here]




For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

SQL Server Failover Cluster Configuration & its Hardware Compatibility

Introduction

Failover cluster in SQL Server is a type of cluster in which two or more independent servers are interconnected with each other via means of physical cables and software. The servers in failover type of clustering are referred to as nodes. This has a major advantage of providing availability of applications and services. If one of the nodes of cluster fails then another node provides the same service.

Requirements for SQL Server Failover Cluster Configuration

The failover cluster configuration requires following necessities:

1. Hardware Requirements
2. Software Requirements
3. Network Infrastructure with domain account requirements

Hardware Requirements- User requires following hardware for failover configuration in SQL Server.

• Server: Since clustering is all about interconnected nodes, therefore we require a set of computers that consists of similar components
• Network Communication Means: For cluster formation, the network adapters and cables are required for connecting the servers/nodes with each other and these connections should be dedicated to network communication.
• Device Controllers for Storage
1. For Serial Attached SCSI: If users are using fiber channels or serial attached SCSI then all the components of storage stack must be same like multipath Input Output and Device Specific Module components must be identical.
2. For iSCSI: If the user is using Internet Small Computer System Interface (iSCSI) then each server should be dedicated to storage and should not be used for network communication.
• Storage: Shared storage is compatible with SQL Server in failover clustering. Storage requirements include the following measures:
1. Use basic disks for failover clustering
2. The partition of each server’s disk done must be with NTFS.
3. Master Boot Record or GUID partition table can be used for partitioning style of disk

Note: The copy of the cluster configuration database is held by a disk in clustered storage and this disk is known as disk witness.

• Deploying SAN with failover configuration: When activating a storage area network (SAN) with a failover cluster follow below mentioned guidelines:
1. Confirming Storage Compatibility: Recheck from the vendors or manufacturers about drivers, firmware, and software (used for storage) whether they are compatible with failover clusters or not.
2. Keep apart storage devices: The servers from different clusters must be unable to access the common storage devices. Generally, a unique Logical Unit Number is used for one set of cluster servers that should be separated.
3. Prefer Using multipath I/O: The hardware vendor should supply a multipath Input-Output device for failover clustering.

Software Requirements- While configuring failover clustering in SQL Server the servers must either run processor supporting 64-bit operating system or Itanium architecture based versions of SQL Server. All the connected servers must have the identical same software updates along with their service packs.

The feature of failover clustering is updated in server products like Windows Server 2008 R2 Enterprise and Windows Server 2008 R2 Datacenter but not in Windows Server 2008 R2 Standard or Windows Web Server 2008 R2.

Network Infrastructure with Domain Account Requirements- Following network infrastructure is used for SQL Server failover cluster configuration.

• IP Address and Network Settings: Ensure at the time of adapter installation that no settings are in conflict. Remember that each adapter must have a unique IP address even if they have identical physical network
• Domain Name System: For naming resolution, the server in the cluster must be DNS.
• Account Administering: At the time of creation of SQL Server cluster, user must be logged on domain with account that has administrator rights and permissions on every servers of the cluster. It can be Domain Users account that is in Administrators collection on each node.
• Domain Role: All the nodes of clusters must be in same Active directory domain, possibly all nodes should have the same domain.
• Clients: As such, no technical expertise required apart from connectors who connect the server for clustering and run softwares that are compatible with services.

Steps to Configuring Failover Clustering in SQL Server:

Follow the given steps for performing the SQL Server failover cluster configuration:

1. Go to Administrative Tool >> Server Manager
2. On each node of the cluster, enable ‘Failure Cluster Feature’ in all servers
3. Click on Next >> Install and then on one of the cluster perform further steps
4. Open Failover Cluster Management from Start >> Administrative Tool >> Failover Cluster Management
5. On the window appearing, on the LHS of window you’ll find Failover Cluster Management there you right click on that and then select Validate a configuration
6. Now Validate a Configuration Wizard window will display on that Enter name and Selected Server and click on Next
7. Now choose Run all tests and then click Next
8. Now a confirmation window appears displaying all the nodes that are connected to that particular node. Validate the information displayed on that screen
9. Now again click on Next and then Finish.

Conclusion

We can configure failover cluster in SQL server at the time of setup installation also otherwise if necessity is after installation the article guides you for such.

Fixing Maintenance Plan Error code 0x534

Recently I have posted a article in SQLServerCentral on Fixing Maintenance Plan Error code 0x534

Read My Article "Here" 

For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

Troubleshooting SQL Server Operating System Error 995

Operating System Error Message

Error: 18210, Severity: 16, State:1
BackupMedium::ReportIoError: write failure on backup device ‘VDI_ DeviceID ‘. Operating system error 995 (The I/O operation has been aborted because of either a thread exit or an application request).

Reason Behind SQL Server Operating System Error 995

There are a number of scenarios when Operating System Error 995 can be experienced by SQL users:

1. The dominant reason behind the occurrence of os error 995 is the outdated versions of SQLVDI.DLL on SQL Server 2005 or SQL Server 2000 instances.
2. In case the database backup of SQL server exits abruptly in Activity Monitor while using the NetBackup SQL Server extension, Error 995 can occur.

Workaround To Remove SQL Server Error 995

Whenever the SQL API attempts to create a virtual device, a virtual memory or a physical memory is required for doing the same. However, with the functioning of NT file system for creation of backups, SQL services, and additional programs at the same time, the system does not end up in having enough virtual space or physical memory. When the third and the fourth database of the batch are in the process of being backed up, the NT system backup is completely processed and the memory that is to be used for creation of virtual device is freed.
The OS error code 995 can be removed by splitting the SQL database into multiple policies, which will individually backup few databases at a particular instance. In addition to this, the second method that can be used is increasing the physical or virtual memory, which can overcome this issue.

Conclusion

The above-mentioned error, SQL Server Error 995, at times leads to corruption of SQL Server database. So it is better to repair corrupt SQL .bak file using third party commercial tool.




For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

Error: Microsoft .NET framework 3.5 service pack 1 is Required- SQL Server 2014

Read My Article "Here" 

Ganapathi varma

Senior SQL Engineer, MCP

For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at gana20m@gmail.com

Linkedin