Tag Archives: sqlserver2014

Exploring AlwaysOn Availability Group Template in Azure

Posted on by

This is one of those posts which is due for a long time. I was really excited when Microsoft announced the release of an AlwaysOn AG template for the Azure Portal back in August 2014.

I had decided to explore this AG template soon after the launch, and this post will explain how easy it is to deploy a 2 node AlwaysOn AG deployment in Azure. Trust me, it’s just a matter of few clicks.

Sit back and relax,we are now going to deploy AG in Azure.Here are the steps :

After you login to the Azure portal, you can access the Market Place

AGTemplate1

You will be surprised to see the number of options which are available in market place now.

AGTemplate2

Search for AlwaysOn ( I really hope MSFT will correct this, it should be rather be AlwaysOn AG) in the search box.

AGTemplate3

 

Choose SQL Server 2014 AlwaysOn and proceed by clicking Create.

AGTemplate4

 

Enter details for the required fields and also select the details needed for SQL Server settings. For the purpose of this post, I will be choosing default settings for the domain controller,virtual network,storage etc.

AG6

Note : Ignore the warning messages in the picture above.Warning for Resource Group is shown as I already have a RG with the same name.

That’s it, and you click create.

It takes around 40 – 45 minutes for the whole process to complete and once it was completed, I had the below set of virtual machines.

AG5

 

2 machines for the domain controllers, 1 for the file share witness and 2 for the AG nodes.

Here is a view of the Resource Group and its resources :

AG4

If you login to one of the SQL nodes and open up SSMS, you can see that AG is fully configured and its ready for use.

AG1

Here is the dashboard view and the quorum info :

AG2

AGQ

Conclusion :

Templates are interesting, and this is an excellent way of deploying AGs in large-scale. I’m pretty sure that MSFT will be working more to optimize this, and will add more and features to it.Looking forward to it.

Thanks for reading, and keep watching this space for more.

Advertisement

This one enhancement makes AlwaysOn Availability Group better in SQL Server 2014 !

Posted on by

I keep getting questions on what is improved other than the increased number of secondaries(8 now) with AlwaysOn Availability Group(AG) in SQL 2014. In this blog post  I would like to talk about one major enhancement around the increased readable secondaries availability in SQL Server 2014.

In SQL Server 2012 there is a good chance that your read workloads can be killed during network failures. Typically this happens in a multi subnet environment where you have AG nodes in 2 or more data centers.If network failure results a communication failure between primary and secondary replicas, then the secondaries will go into resolving state.In resolving state, the read workloads are basically killed. (Yes, yet another reason why you need to test your AG deployments)

With SQL Server 2014, there is good news. Read workloads are not impacted during network failures, primary replica down or cluster quorum issues.

Yes, it stays up all the time, and will be serving your read workloads. The replica will be in resolving state, however the databases are still available for your read connections/workloads.

Keep in mind : Read-only routing is not yet supported for this, and you cannot use listener for your reporting workloads.Brent Ozar calls out this in his blog post here.

Lets now walk though this enhancement :

I will be using a 2 node AG setup and the data transfer mode(Availability Mode) is asynchronous.

If I query the DMV sys.dm_hadr_availability_replica_states I can see the replica states.

SELECT role_desc,connected_state_desc  FROM sys.dm_hadr_availability_replica_states

role_desc               connected_state_desc
PRIMARY              CONNECTED
SECONDARY        CONNECTED

Lets double-check if the data transfer is set as asynchronous

SELECT  is_primary_replica, synchronization_state_desc FROM sys.dm_hadr_database_replica_states

is_primary_replica       synchronization_state_desc
0                                  SYNCHRONIZING
1                                  SYNCHRONIZED

Everything looks good, and now lets take down the primary replica.

Note : Dont try this at home ! (I mean production)

Okay, As this is a test instance I really don’t care and what I did was shutdown the SQL Services to take the primary down.

If we query the DMV again, we can see

SELECT role_desc,connected_state_desc  FROM sys.dm_hadr_availability_replica_states

role_desc                connected_state_desc
RESOLVING           DISCONNECTED

And note, there is no entry for primary as we I have taken it down.

Even though the secondary replica is in resolving state, read connections just works.

USE DemoAG
GO
SELECT COUNT (*) AS [TotalRowCount] FROM [dbo].[InsertCount]

TotalRowCount
6886

The secondary database is in NOT SYNCHRONIZING, NOT_HEALTHY state, however its ONLINE.

SELECT synchronization_state_desc,synchronization_health_desc,database_state_desc FROM sys.dm_hadr_database_replica_states

synchronization_state_desc         synchronization_health_desc       database_state_desc
NOT SYNCHRONIZING               NOT_HEALTHY                           ONLINE

Here is a view from Management Studio :

AG Resolving

Conclusion:

Yes, this is a very good enhancement which will make sure your readable secondaries are highly available and I’m pretty sure that this is one of those enhancement which will force you to deploy AGs in SQL 2014 rather than opting SQL 2012.

Thanks for reading and keep watching this space for more !

SELECT INTO Improvements in SQL Server 2014 !

Posted on by

SQL Server 2014 is a great release with high performance features like In Memory OLTP, Updatable Column Store Indexes etc.

These features still remains as the top favorite and the most compelling reasons for organizations to do an upgrade, However SQL Server 2014 also includes very small but very relevant and useful enhancements, and this post talks about one of those enhancement:

SELECT INTO now runs Parallel !

MP900386077

When I first heard about this feature my first impression was: What…was it not running parallel before ?

No, SELECT INTO was single threaded all these years and with SQL Server 2014 this will now use multiple threads.

Let’s do a quick walk-through of this enhancement.

In this walk-through we will use a table named MasterData which has around 12 million rows.

--Select Count
 SELECT COUNT(*) AS Row_Count FROM [dbo].[MasterData]

Row_Count
11,999,880

Data from this table will be copied over to a different table named MasterData_Copy using SELECT INTO operation.

--Turn on Actual Execution Plan
 SET STATISTICS TIME ON
 GO
 SELECT * INTO [dbo].[MasterData_Copy] FROM [dbo].[MasterData]

This operation was completed in 6 seconds (Desktop class machine) and if we examine the execution plan we can observe parallelism.

SELECTINTO1

And the distribution of row’s among multiple threads:

SELECTINTO2

Now let’s make this little more interesting. We will change the database compatibility level to SQL2012(110) and see what impact that brings to the SELECT INTO operation.

--Change Database Compatibility
 ALTER DATABASE SELECTINTO_Demo SET COMPATIBILITY_LEVEL = 110
--Turn on Actual Execution Plan
 SET STATISTICS TIME ON
 GO
 SELECT * INTO [dbo].[MasterData_Copy] FROM [dbo].[MasterData]

This operation was completed in 11 seconds and it generated a serial plan.

SELECTINTO3

And here is the row distribution.

SELECTINTO4

<Update 1/13/2015 2 PM> 

I received a comment from Toni which says “It might be a good idea to set MAXDOP, most likely having 16 threads isn’t the best option”.

I absolutely agree with this and a well tested MAXDOP setting will definitely bring better results and this is something which you have to keep in mind when queries goes parallel.

</Update>

Conclusion:

If you are using SELECT INTO a lot in your environment, then I would highly recommend you to compare and see what improvements you can again out of SQL Server 2014.

SQL Server 2014 Install – Step by Step

Posted on by

Hot cake for the day is SQLServer 2014 RTM bits. Its generally available starting today, and I downloaded a fresh copy to start testing it.

I will be writing a separate post on the features I love about SQLServer 2014,but here is the step by step install process for a standalone install.

There are no changes for the GUI based install process when you compare with SQLServer 2012,but one thing I’m pretty sure that the installer is sleek and faster.

Step 1  – The landing page looks pretty similar to the earlier versions, and you can choose the installation tab to get things started.

SQL2014 RTM Install1

Step 2 – Choose the stand-alone install option, and it will take you to the product key page.

SQL2014 RTM Install2

Step 3 – This screen is where you need to accept the license terms. There is also an option to turn on select Customer Experience Improvement Program and Error Reporting.

SQL2014 RTM Install3

Step 4 – 6 These are the steps where Global Rules, Product Updates are checked and Setup files are extracted and installed. If the checks are good, then the install will proceed to the next step automatically.

SQL2014 RTM Install4

Step 7 – Install rules are checked for this step, and I have highlighted a check which was interesting to me.

SQL2014 RTM Install5

 Step 8 – This step provides you with Feature installation options. In this install I will discuss about the first option, ie SQLServer Feature Installation.

SQL2014 RTM Install6

Step 9 –  This step is where you will select the features. I’m choosing only those ones which I need for my testing.

SQL2014 RTM Install7

Step 10 – This step lets you decide if you want to go with Default Instance or a Named Instance.

SQL2014 RTM Install8

Step 11 – This step lets you select the account for the services. I’m choosing all defaults as that’s good for my test lab.

SQL2014 RTM Install9

Step 12 – This step lets you choose the Authentication mode, Data directories, and File Stream options.

SQL2014 RTM Install10 SQL2014 RTM Install11 SQL2014 RTM Install12

Step 13 – Feature Configuration Rules step. As mentioned earlier if the checks are fine, setup automatically proceeds to the next step.

SQL2014 RTM Install13

Step 14 – This is the final step of the installation, and you can pick the ConfigurationFile.ini from the path

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\Date_Time\ConfigurationFile.ini

SQL2014 RTM Install14

 

If everything goes smooth, your install will succeed.

SQL2014 RTM Install15

I’m really excited about some real good features in SQLServer 2014,and I will be writing about them during the next couple of days.

Thanks for reading and keep watching this space for more !

 

Monitoring Memory Usage of Memory Optimized Objects – SQL Server 2014

Posted on by

Starting SQL 2014 monitoring memory usage of the memory optimized objects is super important to ensure that your instance don’t run out of memory and cause real bad situations.

One way of ensuring that the memory optimized objects wont utilize a certain amount of memory is by setting up resource governor. This is a great level of control when you have multiple databases running on the same instance and you don’t want memory optimized tables eating up the whole memory.

Is there an easy way to get a quick overview of memory usage of the memory optimized tables in SQL2014?

Yes, you have a real good SSMS report just for this purpose. This one report utilizes DMVs under the hood and provides you with some valuable information.

InmemOLTP Reports

Lets do a quick walk through of what this report gives –

InmemOLTP Reports1

At any point of time I will be interested to see the usage of Table Used Memory counter.

Index Used Memory is based on the bucket count which you mention during non clustered hash index creation.

The query which the report runs under the hood to give you the usage related value is –

SELECT t.object_id, t.name, 
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_used_by_table_kb)/1024.00)), 0.00) 
 AS table_used_memory_in_mb,
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_allocated_for_table_kb - TMS.memory_used_by_table_kb)/1024.00)), 0.00) 
 AS table_unused_memory_in_mb,
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_used_by_indexes_kb)/1024.00)), 0.00) 
 AS index_used_memory_in_mb,
ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_allocated_for_indexes_kb - TMS.memory_used_by_indexes_kb)/1024.00)), 0.00) 
 AS index_unused_memory_in_mb
FROM sys.tables t JOIN sys.dm_db_xtp_table_memory_stats TMS 
ON (t.object_id = TMS.object_id)

This query leverages the power of a new DMV dm_db_xtp_table_memory_stats

Conclusion

SSMS reports are great ways to get a quick overview of what is happening, and I expect more and more powerful reports getting incorporated to SQL 2014 during the coming days.

Thanks for reading and keep watching this space for more.