SQL Server 2016 AlwaysOn Availablility Enhancements

In this Article I have listed out SQL Server 2016 AlwaysOn Availability Enhancements.

SQL Server 2016 [CTP 3.2] SQL Server 2016 CTP Standard Edition now supports AlwaysOn Basic Availability Groups.

AlwaysOn Basic Availability Groups replaces the deprecated Database Mirroring feature for SQL Server 2016 Standard Edition. 

AlwaysOn Basic Availability Groups provide a high availability solution for SQL Server 2016 Standard Edition or higher.

To create a basic availability group, use the CREATE AVAILABILITY GROUP transact-SQL command and specify the WITH BASIC option (the default is ADVANCED). 

A basic availability group supports a fail-over environment for a single database. It is created and managed much like traditional (advanced) AlwaysOn Availability Groups (SQL Server) with Enterprise Edition. 

Basic availability groups enable a primary database to maintain a single replica. This replica can use either synchronous-commit mode or asynchronous-commit mode

Limitations of Basic availability groups

• Limit of two replicas (primary and secondary).

• No read access on secondary replica.

• No backups on secondary replica.

• No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3).

• No support for adding or removing a replica to an existing basic availability group.

• Support for one availability database.

• Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition. 

Load-balancing of read-intent connection requests is now supported across a set of read-only replicas. The previous behavior always directed connections to the first available read-only replica in the routing list. 

The number of replicas that support automatic failover has been increased from two to three.

Group Managed Service Accounts are now supported for AlwaysOn Failover Clusters. 

AlwaysOn Availability Groups supports distributed transactions and the DTC on Windows Server 2016.

You can now configure AlwaysOn Availability Groups to failover when a database goes offline. This change requires the setting the DB_FAILOVER option to ON

SQL Server [CTP 2.2] AlwaysOn now supports encrypted databases. The Availability Group wizards now prompt you for a password for any databases that contain a database master key when you create a new Availability Group or when you add databases or add replicas to an existing Availability Group.

Reference: BOL

Regards,

Ganapathi varma

Senior SQL Engineer, MCP

Linkedin

Email: Gana20m@gmail.com

What's New in SQL Server 2016, Community Technology Preview 3 (CTP 3.1)

What's New in SQL Server 2016

Benefits of SQL Server 2016:

• Enhanced in-memory performance provides 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.
• 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.
• 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 by querying both with T-SQL using PolyBase.
• Faster hybrid backups, high availability and disaster recovery scenarios to back up and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure.

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

Announcing SQL Server Management Studio - November 2015 Release

For Summary On Improvements In the Latest Release
Please go through the below link.

Summary On Improvements In SSMS Nov 2015


To Download SSMS November 2015 Preview

Download Link

Database Mirroring Error 1418

Database Mirroring Error 1418

Hello Everyone.

This post will be helpful in resolving the error 1418 that we may come across when tried to configure Database Mirroring between Primary and Secondary SQL Servers.

Error:

The server network address “%.*ls” cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

To resolve this, we will have to restart the endpoints on the Principal (Primary) Server and proceed with enabling Database Mirroring again.

Please execute the below to check the Mirroring Endpoint Name and status of endpoints.

SELECT * FROM sys.endpoints

WHERE type = 4

                                        

Recycle the endpoint on the database mirror. To do this, follow these steps:

On the principal database, execute the following SQL script to stop the endpoint:

ALTER ENDPOINT <Endpoint Name> STATE=STOPPED

Execute the following SQL script to restart the endpoint:

ALTER ENDPOINT <Endpoint Name> STATE=STARTED

Note: If communication between the endpoints does not restart after you execute the scripts, execute the scripts on the database mirror. 

However, the database may enter a "Suspended" state after you do this. If this issue occurs, execute the following SQL script:

ALTER DATABASE <Database Name> SET PARTNER RESUME

        

Cumulative update package 9 for SQL Server 2012 SP2

Microsoft just announced Cumulative update package 9 for SQL Server 2012 SP2

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

Add Dependencies to a SQL Server Resource in Windows 2003

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

How to Add a Drive as a Clustered Physical Disk Resource in Windows

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

EXCLUSIVE ACCESS COULD NOT BE OBTAINED BECAUSE THE DATABASE IS IN USE

To read my blog post click [Here]

Regards
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

Steps to hide SQL Database from Users

The SQL Server Alliance is becoming more popular in these days as a result various departments or client of the company would access same SQL Server Instance. When they worked in a combined instance, they do not want to make their database visible to other users. In this blog post we will discuss how to hide the database from users using Transact-SQL or using SQL Server Management Studio.

Using T-SQL

For this first we have to create two databases by using the following T-SQL query

Create database [Example_1]
go
Create database [Example_2]
Go

Create logins for both newly created databases

create login [Example_1Login] with
password='pa$$w0rd@123'
go
create login [Example_2Login] with
password='pa$$w0rd@123'
go

Use the following T-SQL script to hide all users database from all logins. The database then only visible to sysadmin and database owners

revoke view any database to [Example_1Login]
go
Revoke view any database to [Example_2Login]
go

As we know that only sysadmin and owners of the database can see database. So run the below statement to select Example_1Login as an owner of Example_1 and Example_2Login as an owner of Example_2

use [Example_1]
go
sp_changedbowner 'Example_1Login'
go
use [Example_2]
go
sp_changedbowner 'Example_2Login'
go

Using SQL Server Management Studio

Right click on the root section (Server Name) of SQL Server Management Studio >>Click on Properties

Under select page column, select Permission >> Click on the account, and select deny to view the database

In the next step, right click on the newly created database >>Click on Properties

Select Files >>Change the owner to newly created account

Now Example_1Login can see only Example_1 database and Example_2Login can see only Example_2 database

Conclusion

There are limited options to hide your database, as we discussed above. Once your database login gets restricted with DENY VIEW to PUBLIC then only the owner of the database and sysadmin can see the database

How To Generate Cluster Log In SQL Server

What is SQL Server Cluster log? How can it be generated? Many of the users are unaware of it. Here, let us have a discussion regarding it. In this informative page you will come to know about cluster logs in SQL Server and how it is been created. The session starts with a small introduction to Cluster Log.

Cluster Log

Cluster log stores the entire information of the cluster that includes SQL nodes, storage details as well as management. Apart from these, it also contains information about statistics, startup, errors etc. So, it is clear that information contained in this file is more important and these log files are situated in the data directory of the Server.
While troubleshooting the problems occurred in the cluster, data in the cluster.log file is important. To know about the problems in cluster, it is better to check out the details by creating a cluster log file. Then, the query that arises will be how to create cluster.log file.

Steps To Generate Cluster Log Files

One point to be noted while generating the cluster log file is that, the command for creating the log file is different since it depends on the operating systems. When you check out on different servers, you will get to know that, in Server 2003 the log files were generated automatically. However, from the later versions it has to be created separately. It is created with the help or support of cluster.exe command.

• From the nodes of cluster, open Command Prompt. Right click it and Run As Administrator.
• Type the command cluster log/g, where ‘g’ stands for ‘Generate’.
• cluster.log file will get generated and you can see it in C:\Windows\Cluster\Report

One of the advantage of cluster log method is, users can specify the level in the generated file by /level option. The adverse effect is that, cannot mention the destination directory for the files. Another way to generate is to use PowerShell, command is; Get-ClusterLog. In this methods the merits and demerits is just the opposite of the previously mentioned method. In this PowerShell method, users cannot mention the level but, can create log file from all the nodes and store in one directory.
Let us see some other commands related to the generation of cluster.log files.
/Copy:< directory >
This command is used for copying the logs generated to single destination. This makes collection of logs much easier. Suppose, if you want to save the files at c:\archive\logs then, you need to type command as, c:\archive and then only you can execute the command ‘cluster log /g /copy:logs’.
/Span:< minutes >
It is to mention the minutes to go back for log collection. If you do not use this, you will get the history of several days. This helps to limit the contents of log and only includes the last few.
/Node:< node name >
It enables the stipulation of specific node. When this is done, logs for other nodes will not be created. If the option is not mentioned, all the nodes in cluster will have cluster.log file.
/Level:<0-5>
This is used to change the logging level and in Server 2008, the default level is 3. If the level is changed, more information will get logged, but there will be some effect on the performance of the system. If it is less than 3, less information will be present. Though in Server 2008, you can set the level from 0 to 10, the maximum is 5.
If you have come across with the situation to generate cluster.log files, hope this session will be helpful.





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

How to Fix MS SQL Server Error: 18456?

SQL Server is the most commonly used Relational database management by many organizations to maintain their database. The primary function of the SQL server is to store and retrieve data from the database as per the application request. SQL server provides data management on the top of the core database management system. Server authenticates the valid user by login credentials. The authentication phase may sometimes prompt error messages due to several reasons. SQL Server Error 18456 is one such error where the Server fails to authenticate the user.

Error details
Error ID: 18456
Symbolic Name: LOGON_FAILED
Message: "Msg 18456, Level 14, State 1, Server, Line 1” or “Login failed for user’’
This error occurs mostly when the login to the server fails. The error message includes account distributor_admin and issues with account replication. The repl_distributor created by the replication allows distributor and publisher to communicate. The distributor_admin must possess a valid password to login to remote server.

Common Reasons Behind the SQL Server ERROR 18456

There can be several reasons behind the error. The common causes behind the login failure can be due to any of the below mentioned cases.

• SQL Authentication is not enabled in the server.
• Invalid user ID or Password.
• Misspelled user ID or Password.
• Expired password due to any fatal errors.

The error state displayed along with the error message indicates the cause of the error. So that user can easily carry out appropriate actions to fix the error. Some common error states and description are given below.

• State 2 or 5 indicate Invalid user ID
• State 7 shows Login disabled and password mismatch error
• State 8 is for Password mismatch
• Invalid password is denoted by state 9.
• 11 and 12 states indicate Valid login but the server access fails due to other reasons
• State 18 indicates that password Change is required

How to Overcome the Above Situations?

The first and foremost solution is to check whether the SQL Authentication mode in enabled on the Server. Before that, make sure that the password and the username are not misspelled or incorrect. So that error due to manual mistakes can be eliminated.

How to Enable the SQL Authentication Mode?

Use SQL Server Management Studio to configure or change the properties of the SQL server. The following steps help you to enable SQL authentication on SQL Server 2008.

1. Connect to SQL server using Windows Authentication mode.
2. Right click on SQL Server and go to its properties as shown in the figure.
   
3. Select the security settings from the server properties window

4. Enable the option SQL server and Windows Authentication Mode and click OK.

5. Restart the server after setting the authentication mode to SQL Server Authentication mode. The figure shows the restarting of SQL Server 2008.

This helps you to fix the user Login failure with error ID: 18456 if SQL server authentication mode was not enabled in the server.

Observation

SQL Server error 18456 occurs if the server fails to authenticate the user and thereby user login fails. This can happen if the SQL server authentication mode is not enabled in the server. This can happen due to manual errors such as misspelled password or username. The above mentioned procedure helps you to enable SQL Authentication mode on SQL server 2008 thereby helps you in fixing error: 18456, the user login failure. But If you forget the password of your crucial SQL database file, then to reset SQL Admin password you need an advanced SQL password recovery utility which allows you reset lost MS SQL database password and regain access on your database.


Appreciate your suggestions. Please comment your feedback and reach me out via email

Prerequisites for Configuring Log Shipping

1)      Both SQL & OS Editions and Versions should be at same level on all participating servers

2)      Express Edition doesn’t supports SQL server Logshipping

3)      The user who configures the Logshipping must be member of sysadmin server role

4)      SQL Server and SQL Server agent services must be configured under windows authentication account with sysadmin permissions on all participating server

5)      Recommended to have unique logon account for both SQL Server and SQL Server agent services

6)      The Logon account must have at least local administrator privileges to carry out the administrative tasks

7)      SQL Server port (1433) must be opened at network level for communication between the both servers

8)      Sharing ports must be enabled on both production & DR servers for log shipping

9)      Create shared network share on both Primary and secondary servers

10)   The backup and restore directories in your log shipping configuration must follow these requirements.

For the backup job, read/write permissions to the backup directory are required on the following:

·         The SQL Server service account on the primary server instance. 

·         The SQL Server Agent account on the primary server instance. 

·         For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the service account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.

For the restore job, read/write permission to the copy directory are required by the following:

·         The SQL Server service account on the secondary server instance. 

·         The SQL Server Agent account on the secondary server instance. 

11)   SQL service log on accounts must be same on both Environments.

12)   Database being log shipped must be in Full recovery model or Bulk logged recovery model. The simple recovery model is not supported

13)   Should not run any maintenance plan or backup job against log shipped databases since that would break Logshipping chain

14)   Logshipping configuration can be done with in trusted domain computers or work group computers

15)   Recommended to have the same Drive Space capacity in fact more space of secondary server considering the data growth in future

16)   Ensure you do not hardcode your server name in your application connection connecting the databases. Create in a such way that you can switch easily to the database servers and take advantage of log shipping

Appreciate your suggestions. Please comment your feedback and reach me out via email

Performance Dashboard Error: Could not find stored procedure 'msdb.MS_PerfDashboard.usp_CheckDependencies'.

In this post, I’m going to talk an issue that when you try to launch the main Dashboard Report in SQL Server 2008 R2. You may receive the below error message that includes information about the cause of the error, which can help you to resolve the issue.

Performance Dashboard Error: Could not find stored procedure 'msdb.MS_PerfDashboard.usp_CheckDependencies'.

Cause

Microsoft does not provide the Performance Dashboard for SQL Server 2008R2 for download, but there is a work-around by installing the Performance Dashboard for SQL Server 2005 and modifying the provided Performance Dashboard T-SQL setup script from Microsoft.

For 2005/2008/2008R2 download here 
For 2012 download here

Resolution

First install the setup file. By default it is installed in: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard. Once you install this, You will find two files at the installed location. Traverse to C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard 

After installing the Dashboard report setup, now setup the dashboard report using SSMS and have to run setup.sql script.

Connect to SQL Server using SSMS and open the file, setup.sql, located in C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard.

Find the code in setup.sql. Go to the statement where you see 'select @ts_now = cpu_ticks' as shown below

Change the statement to

select @ts_now = ms_ticks from sys.dm_os_sys_info

In below screenshot, I have modified select @ts_now = ms_ticks from sys.dm_os_sys_info

Now, execute the “setup.sql” script. 

Now you can right-Click the SQL Server, navigate to the server level reports section, select Custom Reports.In below screenshot, I select "Custom Reports". 

Read My Complete Article "Here" 

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Email: gana20m@gmail.com

Linkedin

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

Always on database in suspect mode and suspended data movement.

Always on database in suspect mode and suspended data movement.

What is suspect mode of database?

SQL database is marked as suspect when primary group file is corrupt and when SQL Server tries to start, database cannot be recovered during that time. 

There can be a lot of reasons for a database to go in suspect mode, some of them are:

• Hardware failure

• Improper shutdown of the database server

• Corruption of the database files

• Unavailable device files

• Unavailable database files

• Database resource used by operating system

• SQL Server incorrectly asserts free data page space when a row is inserted

Issue:

My drive where the 'tempdb' is located is completely filled up and there is no space left on drive causing the 'tempdb' in suspect mode.

This also suspended the data movement between databases in Always on group from primary to secondary.

Step 1:

Identified tempdb filled up the drive space and database is in suspect state

    Step 2: 

    Restarted SQL services to clear space on tempdb db and Now the database is online but the Always-on dashboard report shows critical warning and database is not synchronizing. 

    Secondary replica database data movement is suspended (fail-over dash board report shows data movement got suspended)

    Step 3:

    Expand Always-on High Availability pane and select availability databases.

    Right click on the database and select resume data movement.

     Step 4:

     Issue resolved. Now the database is completely synchronized.

    Appreciate your suggestions. Please comment your feedback and reach me out via email

Regards,

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