Dec 042019

As leader of the PASS High Availability and Disaster Recovery virtual chapter, I invite you to bring your questions for a mega open Q&A session on Tuesday, December 10th, at 1pm Eastern time. We have assembled one of the largest lineups of SQL Server HA/DR experts in our history, and any question goes! Not in any order, the speakers are:

Bob Pusateri, Database Engineering Lead – Salesforce

Brandon Leach, Vice President Database Operations – Financial Company

David Klee, Founder – Heraflux Technologies

Ed Leighton-Dick, Founder – Kingfisher Technologies

Edwin Sarmiento, Managing Director – 15C

Elizabeth Noble, Database Engineer – Pull-A-Part

Matt Gordon, Architect – Insight

Peter Shore, Database Administrator – Thirty-One Gifts

Rick Lowe, Founder – Data FLowe

Rob Volk, Senior Infrastructure Engineer – RMS Automotive

Taiob Ali, Database Administrator – GMO

Tom Norman, Data Architect – KPA

This open-ended Q&A session will help you answer any questions that you have wondered about or currently fight with on SQL Server and Microsoft data platform high availability and/or disaster recovery. Bring your questions and we have the answers! RSVP today for this free webinar!

Dec 032019

GigaOm, an independent research firm that I highly respect, released a study today comparing performance and price between a SQL Server VM on Microsoft’s Azure platform and a SQL Server VM on Amazon’s AWS EC2 platform. Azure is the clear winner. But, I’m pretty tough on vendors, as you might know from some of my blog posts. Let’s review their findings.

Before we get started, SQL Server database-as-a-service (Azure SQL Database) and/or Managed Instances are great to use, when it’s the right use case. All the rage is XaaS nowadays. But, you know as well as I do that a high number of variables exist on if you can even use one of these platforms instead of installing SQL Server in a VM somewhere. The third-party software vendor might not support a DBaaS platform (ISVs – get with the times!). The software might have some ‘legacy’ (a nice name for it) practices of leveraging software installed in the OS via xp_cmdshell, or using DB Mail to send automated emails for your business. I’m sure you have a running list of things in your environment like this that make you blush when someone mentions bad practices. Installing SQL Server in a VM, either on-prem or in the cloud, gives you full control and allows these practices to continue – because sometimes you can’t fix vendor code.

Back to the study and off of my soapbox…

First, they used an updated benchmarking suite, the TPC-E benchmark. I’m actually thrilled that they used both Windows and Linux (Red Hat Enterprise Linux specifically) as the OS platform, as it shows the validity of using Linux for SQL Server workloads, something that I’ve been a huge fan of for years. The TPC-E benchmark is more real-world than some of the older TPC benchmarks such as TPC-C, and is meant to represent an OLTP workload.

The workload on either cloud provider was not designed to be the ‘biggest’ or ‘best’, meaning massive, cost-prohibitive to most, or just unreasonable. It was meant to represent a ‘large’ deployment on either platform. I appreciate that. If you want to look at the largest platforms out there, which are admittedly quite fun to work on, they are not really representative of the vast majority of the workloads that you’ll find being placed in the public cloud. Instead, they chose to deploy a fairly big SQL Server VM on both platforms – 64 vCPUs and between 432 and 488GB of RAM. These are larger than most SQL Servers that I find in the cloud, but are representative of many of the larger SQL Servers that I manage in on-prem datacenters with businesses debating about the viability of the cloud for these workloads.

VM configurations (from GigaOm report)

The storage configuration was quite impressive too on both servers.

VM Storage Configuration (from GigaOm report)

The RHEL configuration was quite similar to the Windows Server configuration.

RHEL Configuration (from GigaOm report)

The first round of results shows that the SQL Server performance on Azure with a very similar VM configuration was over three times faster, measured in database transaction per second.

SQL Server on Windows TPS (from GigaOm report)

They also make some significant claims on the price improvements of SQL Server IaaS on Azure over AWS, claiming an 86.8% better price-performance ratio when comparing Azure Hybrid Benefit to AWS License Mobility. They chose to do the price comparison by looking at the overall dollar versus transactions per second metrics, which I feel is a fair comparison that factors performance into the price.

One item they note is one of our favorite performance-tuning tricks, which is to use the Azure BlobCache feature. It leverages the VM RAM and a local SSD on the virtualized host in Azure to provide free read caching to boost performance of any I/O-dependent workload, which definitely includes SQL Server workloads.

Everyone’s licensing scenario is different, and vary wildly. I urge you to do your own research into your licensing scenario, use cases, and judge equivalent scaled VMs on both platforms based on your needs. The results of the report are quite compelling, though, and it’s similar to many of the results that we develop when comparing the various cloud providers for our customers.

I never want to come off as a marketing pitch for any vendor. I intentionally do not resell software or hardware in my consulting business, as I always want the right tool for the specific situation and client at hand. Read the report, weigh the stats and compare it to your environment, and judge for yourself. Everyone’s needs, experiences, expectations, and budgets are different. Test and validate anything you’re considering moving to for yourself. As for me, I chose Azure for my production environment for my business.

Dec 022019

I’m proud to announce that I’ll be presenting an updated session entitled “Level Up Your Cloud Infrastructure Skills” for the Omaha SQL Server Users Group on December 4th at 6pm.

Abstract: Think infrastructure in the cloud is still just for sysadmins? Think again! As your organization moves into the cloud, infrastructure skills are more important than ever for DBAs to master. Expert knowledge of cloud-related infrastructure will help you maintain performance and availability for databases in the cloud. For example, know what an IOP is? How many does your database consume during a given day? Properly sizing a cloud database depends on your knowledge of this metric. Failure to properly configure storage performance at the time of deployment will slow down your SQL Server considerably. Come learn many of the key cloud infrastructure points that you should master as the DBA role continues to evolve!

RSVP for this free event here, and I look forward to seeing you all there!

May 062019

SQL Server Enterprise Edition can leverage large memory pages to reduce the amount of memory pointers required for larger SQL Server deployments. Reducing the number of pointers makes the database engine more efficient, especially for SQL Servers with greater than 32GB of RAM. A normal memory block is 4KB, and many thousands of pointers are required to manage the memory underneath a larger SQL Server. Large memory pages can change the block size to 2MB, greatly reducing the number of pointers required for memory management.

Leverage SQL Server trace flag 834 to enable this setting and restart the instance to validate.

Note: Large memory pages requires the use of lock pages in memory for the service account before large memory pages are enabled.

For maximum performance of SQL Server in a virtual machine, the use of large memory pages is highly recommended, as it reduces the number of translation lookaside buffer (TLB) misses, which will improve the performance of the SQL Server. Large memory pages are fully supported within all modern hypervisors.

Note: Do not use large memory pages if columnstore indexing is in use in any SQL Server database on this instance.

Note: Be careful if the VM is also under memory pressure, as if the operating system is squeezed too hard, SQL Server will not return memory to the OS and system instability can occur.

To enable large memory pages, add trace flag 834 to a startup parameter for the SQL Server service through the SQL Server Configuration Manager. Restart the instance once this trace flag is added.

To validate that large memory pages are enabled, execute the following query.

You can check the error log for entries related to large memory pages on instance startup.

If the results show large memory page allocations greater than zero, large memory pages are enabled.

Now, validate your system performance before and after! Let me know the results!

 Posted by at 9:22 am
Apr 302019

We have a discount code for last-minute attendees looking to go to our exciting preconference training session at this year’s SQL Saturday Atlanta on May 17th, 2019, called “Level Up Your Data Platform Cloud Infrastructure“! The discount code is PAPERJAM, and enter it on the EventBrite signup page. If you’re even remotely thinking of the cloud for any of your servers, this precon is for you!

Abstract: While your on-premises infrastructure is likely maintained by another team, in the cloud this responsibility falls on you. Whether your organization is just planning for the cloud, all-in, or leveraging a hybrid architecture, this course is designed to ramp you up on maximizing your investment in the cloud components that you utilize.

Infrastructure skills are essential when managing your environment in the cloud. Knowledge is power; key items such as firewalls and connectivity, storage presentation and performance, baselining, and even platform selection are all necessary areas of concern. Capacity management is also crucial, as the old ways of “throwing hardware” at a performance problem will translate into a larger bill each month.

Attend this session to learn how to efficiently manage cloud resources, set yourself apart from your peers, improve the performance of your mission-critical workloads, and potentially save your employer money!

Register for this exciting preconference training session today! If you’re even remotely interested in the cloud, this session is for you!

Nov 192018

I am proud to contribute to this year’s Live360 conference, to be held in Orlando Florida at the Royal Pacific Resort at Universal from December 2nd to 7th.

I’ll be presenting two sessions at this conference. The first is Thursday in room SQH01 called “Cosmos DB for SQL Server Pros” at 8:00am.

Microsoft’s Cosmos DB is the first globally distributed database service, allowing low-latency scalability across multiple geographical regions. As a SQL Server pro, why does this matter? SQL Server is a great multi-function database that can handle many of your tasks with ease. However, it’s not the only tool in the toolbox, and Cosmos DB is a tremendous alternative for some use cases. This introductory session explores Cosmos DB and walks you through the similarities and differences in the two platforms. We explore how to provision and design your database, insert sample data and query for specific records. We’ll review the scalability and throughput settings, and help you ramp up your DBA and development skills on this compelling new platform.

You will learn:

  • The core concepts of Cosmos DB and the reasons to consider it for data requirements
  • Key concepts around data design, placement and document construction
  • How to query for data and process client-side

Just after this session completes on Thursday, the next session is called “Level Up Your SQL Server Cloud Skills” in room SQLH05 at 9:30am.

Think infrastructure in the cloud is still just for sysadmins? Think again! As your organization moves into the cloud, infrastructure architecture skills are more important than ever for DBAs to master. Expert knowledge of cloud-related infrastructure will help you maintain performance and availability for databases in the cloud. For example, know what an IOP is? Should you use a Database as a Service or provision a cloud-based virtual machine? How many compute resources does your database consume during a given day? Can you secure it properly? Come learn many of the key cloud infrastructure points that you should master as the DBA role continues to evolve!

You will learn:

  • To identify key concepts around database platform selection, such as Azure SQL DB versus Infrastructure as a Service, and make educated recommendations for the appropriate platform
  • To identify key performance metrics, such as storage performance, vCPU/DTU requirements and so on, that help with properly sizing the destination resources
  • About Identifying and recognizing the key reasons to migrate databases to the cloud versus keeping them on-premises

Register for this exciting conference today! We look forward to seeing you there!

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.