Upgrade SQL Server Evaluation edition to license Edition of SQL Server

 The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. The edition has a built in expiry of 6 months from the time that you install it. 

One of my client doesn't have licensed key for SQL Server and they have planned to add key later. To apply licensed key to an existing evaluation edition of SQL Server is different from windows. Follow the below procedure.

In below screenshot, SQL Server is Enterprise evaluation edition.

Upgrade SQL Server Evaluation edition to license Edition of SQL Server

Step 1:

  Mount SQL Server licensed setup file to the server.

 Launch SQL Server "setup.exe", select maintenance tab and select edition upgrade.

Step 2:

Enter the licensed product Key and click next

Step 3:

Continue with running Edition Upgrade Rules

Step 4:

Select the instance name that you want to upgrade

Step 5:

Select upgrade to proceed with upgrade process and close it once successfully upgraded





In below screenshot, SQL Server Edition is successfully upgraded.

Ganapathi varma Chekuri

Email: gana20m@gmail.com

Linkedin 

Cumulative Update #1 for SQL Server 2017 RTM

The 1st cumulative update release for SQL Server 2017 RTM is now available for download at the Microsoft Downloads site.

• CU#1 KB Article: https://support.microsoft.com/en-us/help/4038634/cumulative-update-1-for-sql-server-2017
• Microsoft® SQL Server® 2017 RTM Latest Cumulative Update: https://www.microsoft.com/download/details.aspx?id=56128

Resolve Network Binding Order Warning in failover cluster

In this post, I’m going to talk about an issue that I found while running the cluster validation report. You may receive below warning message that includes information about the cause of the warning, which can help you to resolve the issue.

Rule “Network binding order” generated a warning.

The domain network is not the first bound network.  This will cause domain operations to run slowly and can cause timeouts that result in failures.  Use the Windows network advanced configuration to change the binding order.

In the below screenshot, the warning says the network card used to connect to your domain network is not at the top of the network binding order.

Resolution

Step 1

Go to run and type ncpa.cpl to open network connections. Now press Alt+F and select advanced options then click on Advanced and Advanced Settings. When the Advanced Settings window opens, you will be on the Adapters and Bindings tab, which allows you to change the binding order for the network interfaces on the server.

Step 2:

In this case the Microsoft Failover Cluster Virtual Adapter is bound before the domain network and still the cluster validation report shows you warning. In the below screenshot, the TeamNIC_MGMT NIC adapater is my domain network and was already at the top of the binding order.

Read My Complete Article "Here"

Ganapathi varma Chekuri

Email: gana20m@gmail.com

Linkedin 

The Connection to the Primary Replica is not Active

In this post, I’m going to talk an issue that I found when joining replica or database on secondary replica to availability group.  This error mostly appears when we try to join the adding replica to availability group or database to availability group using GUI and T-SQL.

Error 1

TITLE: Microsoft SQL Server Management Studio

  ------------------------------

  Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)

  ------------------------------

  ADDITIONAL INFORMATION:

  Failed to join the database 'AGTEST' to the availability group 'AG01' on the availability replica 'SQL2'. (Microsoft.SqlServer.Smo)

  ------------------------------

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  ------------------------------

  The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error:

In below screenshot, joining the database abc to SQL2 failed with this error when creating a new Availability Group.

Error 2

TITLE: Microsoft SQL Server Management Studio

  ------------------------------

  Failed to join the database 'AGTEST' to the availability group 'AG01' on the availability replica 'TREDSHOST08'. (Microsoft.SqlServer.Smo)

  ------------------------------

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  ------------------------------

  The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error: 35250)

In the below screenshot, joining database abc to SQL2 failed with this error.

Resolution

To solve the issues, follow these steps.

Step 1

Query the sys.dm_hadr_availability_replica_states for the last_connect_error_number which may help you diagnose the join issue. Depending on which replica was having difficulty communicating, you should query both the primary and secondary for the local replica

--connect secondary replica
select r.replica_server_name, r.endpoint_url,
       rs.connected_state_desc, rs.last_connect_error_description, 
       rs.last_connect_error_number, rs.last_connect_error_timestamp 
 from sys.dm_hadr_availability_replica_states rs 
  join sys.availability_replicas r
   on rs.replica_id=r.replica_id
 where rs.is_local=1

In the below screenshot, the secondary instance SQL2 was not able to communicate with SQL1

Step 2

The endpoints were created with the two dedicated IP's but the CREATE AVAILABILITY GROUP statement used the FQDN in the ENDPOINT_URL section. Once I changed the ENDPOINT_URL to use the IP's of the dedicated NIC (TCP://172.16.35.11:5022), the AG started working properly with the dedicated network.

--connect  primary replica
ALTER AVAILABILITY GROUP [AG01]
MODIFY REPLICA ON 'TREDSHOST07' WITH (ENDPOINT_URL = 'TCP://172.16.35.10:5022')
GO
ALTER AVAILABILITY GROUP [AG01]
MODIFY REPLICA ON 'TREDSHOST08' WITH (ENDPOINT_URL = 'TCP://172.16.35.11:5022')
GO

In the below screenshot, I have modified the endpoint URL on both replicas.

Read My Complete Article "Here"

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Email: gana20m@gmail.com

Linkedin 

Step by Step Configuring a Dedicated Network for Availability Group

One of my clients came up with the requirement to isolate the replication traffic from the public network. The advantage of using a separate network for replication is reduced latency and replication can still run without any latency issues during times of heavy network traffic issued by other applications or backup activities. In this article, I will show a procedure to setup a dedicated network for Availability Group communication

SQL Server 2012 AlwaysOn Availability Groups require a database mirroring endpoint for each SQL Server instance that will be hosting an availability group replica and/or database mirroring session. We have two nodes SQL1 and SQL2 in a Windows failover cluster. Each node has a standalone SQL Serverinstance installed and configured with an Always On AG. Each node also has a separate virtual network adapter for public communication, a virtual network adapter for WSFC communication, and a virtual network adapter that we’ll dedicate to availability group communication.

In the below screenshot, the Node1 (SQL1) Availability Group dedicated NIC IP address is 172.16.35.10. The Node2 (SQL2) Availability Group dedicated NIC IP address is 172.16.35.11.

Reconfigure Communication to a Dedicated Network

Step 1:

Identify the endpoint name configured for Always On replication. By default, “Hadr_endpoint” is the endpoint name. In below screenshot, we see this. 

Read My Complete Article "Here"

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Email: gana20m@gmail.com

Linkedin 

Step by Step Configuring AlwaysOn with Log Shipping

One of my clients came up with a requirement to use log shipping along with an AlwaysOn Availability Group. They cannot extend an AlwaysOn replica to the disaster recovery site for a few reasons listed below;

• Infrastructure or staffing to maintain WSFC configurations between different sites
• Delayed Recovery – In Log-shipping we can have definite delay on secondary database, SLAs on RPO, RTO force a fast recovery from manual error which only can be realized with delayed recovery restoring the transaction log backups on one instance of the HA/DR strategy

We have two SQL Servers in AlwaysOn AG Group SQL1 and SQL2 in Primary site for HA. One SQL Server instance SQL3 in secondary site for disaster recovery. The primary site is already setup with a two node AlwaysOn Availability Group (AG) with SQL1 as the primary replica and SQL2 as the secondary replica. 

In the below screenshot, SQL1 is the primary replica and SQL2 is the secondary replica

To configure AlwaysOn Availability Groups, Reference: http://mssqllover.blogspot.in/2017/02/alwayson-availability-groups-step-by.html

To configure Log-shipping, Reference: http://mssqllover.blogspot.in/2017/03/step-by-step-logshipping-configuration.html

How to setup Log-Shipping on Database part of AlwaysOn Availability Groups

Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. By default, Alwayson backup preferences are set to prefer the secondary. You have to change backup preference and select as primary. In an AlwaysOn Availability Group, BACKUP LOG supports regular log backups only. COPY_ONLY transaction log backups are not supported on secondary replicas.

Right click on the AG group and select backup preference. Select the primary as the backup preference.

Read My Complete Article "Here"

Ganapathi varma Chekuri

Email: gana20m@gmail.com

Linkedin 

TempDB Database On a Local Disk for an FCI

Link Below:

TempDB Database On a Local Disk for an FCI

This article will be helpful in configuring TempDB on a local Disk instead of shared storage in SQL Server 2012/2014. This will reduce the I/O requests from the shared storage and overall it can improve performance.

Backup and Restore Strategies in SQL Server : A Brief Introduction

Establishment of a successful business requires the proper maintenance of the data. SQL Server is one the popular platform for the same. In SQL Server, a proper strategy has been introduced for the management of data. The Main reason of introducing SQL Server backups and restore strategies in SQL Server is to enable the users to regain the database, which is corrupted. However, restoring and backing up of data must be done in particular environment and must work with the resources that are available. This is the reason users need a reliable strategy to backup and restore the database in SQL Server.

Webinar: Performance Troubleshooting Using Wait Statistics by Paul Randal

Hi Folks, 

Join Webinar on Webinar: Performance Troubleshooting Using Wait Statistics. Tue 3/07/2017 from 10:30 PM to 11:45 PM IST

Click [Here] to join

Step By Step Logshipping configuration

Log shipping provides a means to maintain a secondary server on an automated basis
using a chain of transaction log backups. This chapter explains the basic confi guration of
log shipping along with considerations for configuring the failover and failback within a log
shipping environment.

In this article, I will show you a step by step procedure to configure Log-shipping.

Primary Database
The primary database is accessible to applications and accepts transactions. Transaction
log backups are taken on a periodic basis and copied to the server hosting the secondary
database.

Secondary Database
The secondary database, also referred to as the standby, is normally inaccessible, and
transaction log backups from the primary database are restored on a continuous basis.
The secondary database can be in two different modes: Standby Mode or No Recovery
Mode. When the secondary database is in Standby Mode, users can connect to and issue
SELECT statements against the database. When the secondary database is in No Recovery
Mode, users cannot connect to the database. In either mode, you can restore transaction
logs to the secondary database. You cannot restore transaction logs when users are
connected to the database, so you should not use Standby Mode for high-availability
architectures.

Monitor Server
The monitor server, which is optional within a log shipping architecture, contains a set of jobs
that send alerts when the log shipping session is perceived to be out of sync.

How to enable Log-shipping between two SQL instances in DC and DR site.

Step 1:

Take full backup and log backup of LSDB database in primary instance (SQL1) and restore backups to secondary instance (SQL2) in standby mode.

Open Windows Explorer on the primary and create a share named LS_Primary
Grant Full Control permissions on this share to the SQL Server service account on
the primary as well as Read permissions to the SQL Server Agent service account on
the secondary





Step 2:

Open Windows Explorer on the secondary and create a share named LS_Secondary.
Grant Full Control permissions on this share to the SQL Server service account and the
SQL Server Agent service account on the secondary.

Step 3;

Verify that the database is configured for either the Full or Bulk-logged recovery model. If it is not, change the recovery model to Full. Start SSMS, connect to Primary instance (SQL1) within Object Explorer, right-click the database (LSDB) and choose Properties. Below Select A Page, select Transaction Log Shipping.

Step 4:

Select the Enable This As A Primary Database In A Log Shipping Configuration check
box and click Backup Settings. In the Network Path To Backup Folder text box, enter the Universal Naming Convention (UNC) path to the share you created in step 1. The Backup Folder Is Located On The Primary Server text box, enter the physical path to the directory in which your backups will be stored. Click OK to close the Transaction Log Backup Settings dialog box.

Select schedules

Delete files older than                                    72 hours

Alert if no backups occurs with in                  45 minutes



Step 5:

Click Add to add a new secondary. Click Connect and connect to SQL2; leave the name of the secondary database set to LSDB. You can select No, the secondary database is initialized as we have already restored full backup of database in standby mode in secondary instance.
selected.

Step 6:

Click the Copy Files tab. Set the destination folder that the transaction log backups will be copied to and change the copy interval as per your requirement

.

Step 7:

Click the Restore Transaction Log tab. Verify that the Standby mode is selected, set delay alert
as per your requirement. Click OK to close the Secondary Database Settings dialog box.
Click OK to generate the log shipping configuration.







Run back up job on primary SQL instance and copy, restore jobs in secondary instance. Create the log shipping jobs and alerts, and start log shipping. Verify that backups are going to the correct folder, are copied from LS_Primary to LS_Secondary correctly, and are restored to SQL2.

Step 8:

Right click Primary and Secondary instance, Select reports and click transaction log-shipping status.

This will show you Log-shipping sync status for database between two instances.

Everything on Always on Availability Groups series

Step By Step configuring Windows 2012 R2 fail over cluster How to setup windows cluster for Always on Availability Groups

AlwaysOn Availability Groups: Step by Step Setup Step by Step procedure to setup Always on Availability Groups

Configure Read only routing for an always on availability group

Always on troubleshooting: 

http://mssqllover.blogspot.in/2017/04/the-connection-to-primary-replica-is.html

Step By Step configring Windows 2012 R2 fail over cluster

One of my client requirement is to install Always on availability groups for SQL databases. You don't require shared storage to setup Always on availability. it can be configured in local disks.
AlwaysOn Availability Groups rely on the Windows Server Failover Cluster for failure detection and management of the Availability Group replicas.

Prerequisites required to install Windows 2012 R2 failover cluster feature.

• Get the operating system installed, patched and configured on all participating nodes
• See either Windows Update or an internal Windows Server Update Services (WSUS) server to get all of the required Windows Updates downloaded and installed
• All the participating servers should be added to active directory
• Domain user account must have administrator rights on the computers that are becoming part of the new cluster and the Create Computer Objects permission on the container where computer accounts are created in the domain.
• Ensure the password not temporary and there is complex password
• Both OS Editions, Versions should be at same level on all participating nodes
• You need to install 3.5.1 or greater on all participating nodes
• Make sure you have a separate NIC's for public and private communication
• Make sure you have free IP for windows cluster

How to configure Windows 2012 R2 Failover cluster

Step 1:

Failover Clustering feature has to be installed onto each cluster node
Open server manager and select Add Roles and Features. Next, Add Roles and Features Wizard and Select the Failover Clustering feature. click Next and install.

Step 2:

Validate your hardware configuration to make sure that it meets the requirements for building a failover cluster. Launch failover cluster manager console and click on the Validate Configuration option, found in the Actions pane. This will cause Windows to launch the Validate a Configuration Wizard.





Step 3:

Testing Options dialog box, make sure that the option Run only tests I select. Click Next.

Step 4:

Exclude add eligible storage cluster check box and click Next.




In the Confirmation dialog box, click Next.

click Next. This will run all the necessary validation tests.

In the Summary dialog box, click Finish to create the Windows Failover Cluster.

Step 5:

The length of time that the tests take to complete varies depending on the number of servers in your cluster and on your hardware's performance.

the test results may result few warnings. However, if you see any Error messages, you need to fix those first prior to creating the Windows Server Failover Cluster.




Step 6:

Launch the Create Cluster Wizard. Click Next and In the Select Servers dialog box, enter the host names of the nodes that you want to add as members of your cluster and click Next.

Step 7:

In the Access Point for Administering the Cluster dialog box, enter the virtual hostname and IP address that you will use to administer the cluster. Click Next




Step 8:

In the Confirmation dialog box, Exclude add eligible storage cluster check box and click Next. This will configure Failover Clustering on both nodes of the cluster.

There is no need to use shared storage to create the Windows Server Failover Cluster that we will use for our Availability Group.

Step 9:

In the Summary dialog box, verify that all the report returns successful.

In below screenshot, AONTESTWFCS01 cluster is successfully setup on SQL1 and SQL2 nodes.

Webinar- SQL Server 2016 - Always Encrypted / Security

Join Webinar on SQL Server 2016 - Always Encrypted / Security. Thu 2/23/2017 from 12 PM to 1 PM

SQL Server has had ways to encrypt data in the past - for example, Transparent Data Encryption (TDE). This technology protects the data at rest and when the database files or backups are compromised. However it is accessible to the database itself and to anyone who can own it and obtain the key/certificate/password (system administrators, hackers, what have you). Always Encrypted is a new feature in SQL Server 2016, which encrypts the data both at rest *and* in motion (and keeps it encrypted in memory). Unlike TDE, as well, Always Encrypted allows you to encrypt only certain columns, rather than the entire database.

Click [HERE] to enter lobby.

AlwaysOn Availability Groups: Step by Step Setup

An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to eight sets of corresponding secondary databases. Secondary databases are not backups. Continue to back up your databases and their transaction logs on a regular basis.

To read overview on always on availability groups click [here]

Prerequisites required to enable SQL Server 2012 AlwaysOn Availability Groups Feature.

• Get the operating system installed, patched and configured on all participating nodes
• See either Windows Update or an internal Windows Server Update Services (WSUS) server to get all of the required Windows Updates downloaded and installed 
• Dedicated domain user account be created for use by the SQL Server service. This should just be a regular or domain account 
• Having separate accounts for SQL Agent service, SSRS, SSIS & SSRS. Having separate account is more secure and resilient, since a problem with one account won’t affect all of the SQL Server Services 
• Ensure the password not temporary and there is complex password
• Both SQL & OS Editions, Versions should be at same level on all participating nodes
• All replicas in your AlwaysOn group must be in the same windows domain. They must be able to communicate with each other.
• Always on availability groups is only supported in Enterprise edition starting from SQL server 2012 ( except SQL 2016 it supports basic availability group in standard edition)
• Recommend to have same collation on all replicas
• SQL Server port must be opened at firewall level for communication between replicas
• Create shared network share on all participating nodes
• You need to install 3.5.1 or greater on all participating nodes
• Make sure your databases are in Full Recovery Mode, not Simple or Bulk Logged
• Databases included in your AlwaysOn group must be user databases. System databases cannot participate in AlwaysOn Availability Groups.
• Read-only databases cannot belong to an AlwaysOn group
• Databases may only belong to one availability group at a time
• Make sure full backups of each of your databases are made prior to installing AlwaysOn
• No cluster shared volume is required for Always on, it can be configured in local disks
• Make sure you have a seperate NIC's for public and private communication
• Additional NIC is required if you want to isolate always on replication traffic to dedicated NIC
• Make sure you have two free IP's each for windows cluster IP and Always on listener IP

AlwaysOn Availability Groups require a Windows Server Failover Cluster, we first need to add the Windows Failover Cluster Feature to all the nodes running the SQL Server instances that we will configure as replicas

To know how to install failover cluster click [here]

We have two node windows failover cluster SQL1 & SQL2 already setup as shown in below screenshot.

Once you have installed failover cluster we can now proceed with enabling the AlwaysOn Availability Groups feature in SQL Server 2012. This needs to be done on all of the SQL Server instances that you will configure as replicas in your Availability Group.

How to Enable SQL Server 2012 AlwaysOn Availability Groups Feature

Default standalone SQL instances installed on nodes SQL1 & SQL2

Step 1:

Open SQL Server Configuration Manager. Double-click the SQLServer (MSSQLSERVER) service to open the Properties dialog box.

In the Properties dialog box, select the AlwaysOn High Availability tab. Check the Enable AlwaysOn Availability Groups check box. This will prompt you to restart the SQL Server service. Click OK

In below screenshot, AONTESTWFCS01 is the windows cluster name. SQL1 and SQL2 are nodes.

Step 2:

Restart the SQL Server service

Create and Configure SQL Server 2012 AlwaysOn Availability Groups

Step 3:

To create and configure a SQL Server 2012 AlwaysOn Availability Group,

Open SQL Server Management Studio. Connect to the SQL Server instance

In Object Exporer, expand the AlwaysOn High Availability folder. Right-click on the Availability Groups folder and select the New Availability Group Wizard… option. This will launch the New Availability Group Wizard.
Step 4:

In the Introduction page, click Next.

In the Specify Availability Group Name page, enter the name of the Availability Group in the Availability group name: field. Click Next.

Step 5:

In the Select Databases page, select the checkbox beside the database that you want to include in your Availability Group. The databases have to be in Full recovery model prior to joining them in the Availability group. Click Next.

In below screenshot, we have selected AGTEST database to Always on availability.

Step 6:

In the Specify Replicas page, under the Replicas tab, click the Add Replicas button and connect to the other SQL Server instances that you joined as nodes in your Windows Server Failover Cluster.

Configure the following options:

• Automatic Failover
• Synchornous commit
• Readable secondary 

In below screenshot, specified replicas SQL1 & SQL2.

 
Step 7:

In the Endpoints tab, verify that the port number value is 5022 and endpoint name Hadr_endpoint. 

Step 8:

In below screenshot, Selected backup preferences as shown in below screenshot.

Step 9:

In the Listener tab, select the Create an availability group listener option. Enter the following details.

Listener DNS name: AGLIS01
Port: 16333

Listener IP Address: 192.168.35.18

Click the Add… button to provide an IP address. In the Add IP Address dialog box, enter your preferred virtual IP address in the IPv4 Address field. Click OK. Click Next.

Step 10:

In the Select Initial Data Synchronization page, select the Full option. Provide a shared folder that is accessible the replicas and that the SQL Server service account used by both replicas has Write permissions to. This is just a temporary file share to store the database backups that will be used to initialize the databases in an Availability group. If you are dealing with large databases, it is recommended that you manually initialize the databases prior to configuring them as your network bandwidth may not be able to accommodate the size of the database backups. Click Next.

Step 11:

In the Validation page, verify that all validation checks return successful results. Click Next.

Step 12:

In the Summary page, verify all configuration settings and click Finish. This will create and configure the AlwaysOn Availability Group and join the databases.