Big TempDB Improvement In SQL Server 2017 CTP 2.0 Setup

By: on April 19, 2017 in Setup, SQL Server 2017, TempDB | 1 Comment

SQL Server 2016 is the first version of SQL Server which allows you to configure multiple files during Setup. That’s good, but it had a very severe limitation: the data files could only be created with a size up to 1GB (1024 MB). For anyone sizing TempDB properly, clearly you’re going to have sizes that are greater than 1GB, so you still had to configure TempDB manually even in SQL Server 2016. I brought this up to Microsoft in early March because I have been working with a customer who was trying to configure TempDB to their standards using SQL Server 2016, but couldn’t via Setup due to this limitation. It was a great discussion with quite a few folks (including fellow MVPs), and being honest, I didn’t think anything would be done about it in the immediate future. I was wrong.

As of SQL Server 2017 CTP 2.0, this limitation no longer exists. Setup now allows the data files you create to be up to 256GB.

Creating a 256GB data file for TempDB

Microsoft listens, folks. This combined with the AG improvements I blogged about earlier in CTP 2.0 make it a welcome release.

Thinking about SQL Server 2017 or just looking to get more up to date than you are now? Contact us today to help you get started down the path …

SQL Server 2017 Is Official & Enhancements to AGs in CTP 2.0

By: on in Availability Groups, SQL Server 2017 | No Comments

Well, it’s official – SQL Server v.Next which has been in preview is going to be SQL Server 2017. This was announced at the DataAmp event. While we do not have an  release date, it’ll be sooner rather than later. CTP 2.0 was just released today, so go and play with it.

There are two improvements for AGs in CTP 2.0 over 1.4 for listeners on Linux:

  • The listener will now be persisted post-failover (I have yet to test this … will do so soon)
  • Read only routing should be working in this releas

In other words, the listener should work properly now in CTP 2.0.

Also, there is a new CLUSTER_TYPE called External. I’ll blog more about this another time, but this is a follow on to the AGs that do not need an underlying WSFC or Pacemaker cluster that I blogged about here.

That’s it – not a long post, but all good stuff.

A Few Days Left – 25% or 30% off Upcoming Chicago Classes

By: on April 11, 2017 in Always On, Availability Groups, High Availability, SQLHAU | No Comments

Happy Tuesday. I just wanted to make sure everyone took advantage of the great discount SQLHA is offering on our two upcoming classes in Chicago this August:

The Boot Camp features our world famous labs which everyone raves about. There is no better way to learn than with hands on experience that is custom designed for this course in addition to the instructional content.

Through this Saturday, April 15, both classes are on sale for 25% off the list price. After that, there is still a discount, but this is an Early Bird special. This means that SQL Server Fundamentals would be $521.25 (list price: $695) and the Boot Camp $1646.25 (list price: $2195).

For an even bigger discount, sign up for both classes and you will get 30% off the total price, which means that for four days of SQL Server availability training, you’ll pay $2023 – less than the list price of just the Always On Availability Groups Boot Camp.

Click on the links above for more information and how to register.

Always On Availability Groups with No Underlying Cluster in SQL Server v.Next

By: on February 22, 2017 in Always On, Availability Groups, Linux, Pacemaker, SQL Server V.Next, Windows Server Failover Cluster | 3 Comments

UPDATED 2/22/17 in the afternoon

With a lot of the focus seemingly going to the Linux version of SQL Server v.Next (including the inclusion of Always On Availability Groups [AGs] in the recently released CTP 1.3) , I don’t think that a lot of love is being showered on the Windows side. There are a few enhancements for SQL Server availability, and this is the first of some upcoming blog posts.

A quick history lesson: through SQL Server 2016, we have three main variants of AGs:

  • “Regular” AGs (i.e. the ones deployed using an underlying Windows Server failover cluster [WSFC] requiring Active Directory [AD]; SQL Server 2012+)
  •  AGs that can be deployed without AD, but using a WSFC and certificates (SQL Server 2016+ with Windows Server 2016+)
  • Distributed AGs (SQL Server 2016+)

SQL Server v.Next (download the bits here) adds another variant which is, to a degree, a side effect of how things can be deployed in Linux: AGs with no underlying cluster. In the case of a Windows Server-based install, this means that there could be no WSFC, and for Linux, currently no Pacemaker. Go ahead – let that sink in. Clusterless AGs, which is the dream for many folks (but as expected, there’s no free lunch which I will discuss later). I’ve known about this feature since November 2016, but for obvious reasons, couldn’t say anything. Now that it’s officially in CTP 1.3, I can talk about it publicly.

Shall we have a look?

I set up two standalone Windows Server 2016 servers (vNextN1, vNextN2). Neither is connected to a domain. Figure 1 shows the info for vNextN1 (for all pictures, click to make bigger).


Figure 1. Standalone server not domain joined

Using Configuration Manager, I enabled the AG feature. Prior to v.Next, you could not continue at this point since there was no WSFC; you would be blocked and get an error. However, in v.Next, you will get what is seen in Figure 2. It still indicates that there is no WSFC, but it happily allows you to enable the AG feature.

Figure 2. Enabling the AG feature in v.Next

After enabling and restarting the instance, you can clearly see in Figure 3 that the AG feature is enabled. We’re not in Kansas anymore, Toto.

Figure 3. AG feature is enabled, but no WSFC

Just to prove that there is no magic, if you look in Windows Server, the underlying feature needed for a WSFC is not enabled which means a WSFC cannot be configured. This is seen in Figure 4.

Figure 4. Failover clustering feature is not installed – no WSFC!

In SQL Server, configuring this is done via T-SQL and is similar to how it is done for AD-less AGs with Workgroup WSFCs in SQL Server 2016/Windows Server 2016. In other words, you’re using certificates. In addition to certificates, there is a new clause/option (CLUSTER_TYPE) that exists in the CREATE and ALTER AVAILABILITY GROUP T-SQL. Unlike the Linux example in documentation which shows how to use the CLUSTER_TYPE syntax, I altered the syntax I’ve been using for the AD-less AGs with certificates since it is basically the same and I did not use seeding (you can if you want); I manually restored the database AGDB1. I created an AG called AGNOCLUSTER. This can be seen in Figures 5 and 6.

Figure 5. vNextN1 as the primary replica of AGNOCLUSTER

Figure 6. vNextN2 as the secondary replica of AGNOCLUSTER

To support this new functionality, there are new columns in the DMV sys.availability_groups – cluster_type and cluster_type_desc. Both can be seen in Figure 7. You will also get an entry in sys.availability_groups_cluster with this new cluster_type (also a new column there).

Figure 7. New columns in sys.availability_groups

So what are the major restrictions and gotchas?

  1. This new AG variant is NOT considered a valid high availablity or disaster recovery configuration without an underlying cluster (WSFC for Windows Server or currently Pacemaker on Linux). It is meant more for read only scenarios, which means it’s more meant for Enterprise Edition than Standard Edition. I cannot stress enough this is NOT a real HA configuration.
  2. UPDATE – A major reason this is not a real HA configuration is that there is no way to guarantee zero data loss without you first pausing the primary and ensuring that the secondary replica is in sync (or replicas, as the case may be).
  3. Having said #1, you can do a manual failover from a primary to a secondary. This would be true even in the case of an underlying server failure, hence this not being really a true availability configuration: there’s no cluster and the mechanism (sp_server_diagnostics) to detect and handle the failure.
  4. Since there’s no underlying cluster, you can’t have a Listener. This should be painfully obvious. This also means that you will connect directly to any secondary replica for reading. This makes it possibly less interesting for read only scenarios, but again, did you expect you’d get everything?
  5. Since there is no Standard Edition version of the CTP, it is unknown if this will work with Standard Edition in SQL Server v.Next. I would assume it will, but we’ll see when v.Next is released.
  6. UPDATE – This also can most likely be used for migration scenarios (arguably it will be the #1 use), which I will talk about in a Windows/Linux cross platform blog post soon.
  7. UPDATE – This is not the replacement for database mirroring (DBM). That is/was putting AGs in Standard Edition in SQL Server 2016 even though it requires a WSFC. You get so much more that you really should stop using DBM. It’s been deprecated since SQL Server 2012 and they could pull it at any time (and I’m hoping it’s gone in v.Next).

Keep in mind this is how things are now, but I don’t see much, if anything, changing whenever RTM occurs.

I won’t really be talking about this configuration this weekend at SQL Saturday Boston or at SQL Saturday Chicago in March, but I will be talking about it a little bit at both the SQL Server User Group in London on March 29th and at the SQL Server User Group in Dublin on April 4. I will be covering this configuration in more detail as part of my Training Day at SQL Bits 16 – “Modern SQL Server Availability and Storage Solutions”  (sign up for it today – seats going fast!). It will also be part of my upcoming new SQLHAU course Always On Availability Groups Boot Camp coming up in August, and incorporated into the 4-day Mission Critical SQL Server class (next scheduled delivery is in December).

Announcing Two New Courses and the 2017 SQLHAU Schedule

By: on February 17, 2017 in SQLHAU, Training | No Comments

TGIF! It’s been a busy week here, and I’m finally getting a chance to breathe. I’m proud to announce that SQLHAU has two new instructor led courses:

There is still the original 4-day Mission Critical SQL Server class which covers everything and has labs as well.

Here are the dates and locations for the classes:

Click on the links above to see pricing and discounts, or just check our Events page. We’re running some great specials (up to 30% off the list price). Don’t miss out on the best SQL Server high availability training. Reserve your seats today.

We’ve got a few more exciting things in the fire so stay tuned!