Microsoft has recently released a powerful new DMV specifically to help with memory issues, sys.dm_os_out_of_memory_events. It is currently available in Azure SQL Database and Azure SQL Managed Instances. This DMV consolidates and simplifies telemetry from SQL Server ring buffers, applies heuristics, and provides a result set. The DMV stores a record for each out-of-memory (OOM) event that occurs within the database, providing details about the OOM root cause, the memory consumption of database engine components at that point in time, potential sources of memory leaks, and more.
Posted June 02, 2022
Database applications have vastly improved in recent years, but despite our best efforts, they can still feel frustratingly slow. A report that is supposed to take minutes might instead (for unknown reasons) take 2 hours. Corporate teams complain that every time they work with the database, it gums up the works, leaving users grumpy and managers suspicious. This is no small matter. According to the SolarWinds Query Report 2021, nearly one-third of data pros surveyed manage more than 300 databases in their organization's environment.
Posted April 07, 2022
Microsoft announced at the end of February 2022 that significant changes are in store across its SQL Server analytics products. To set the context, the broader enterprise IT marketplace was still primarily on-prem when Microsoft first introduced its cloud analytics products in 2017. Since that time, we've seen a flood of migrations to the cloud with analytical workloads at the forefront, since a cloud-based approach to analytics offers greatly improved manageability, deployment simplicity, as well as standard cloud benefits like flexibility, cost control, and scalability.
Posted March 11, 2022
In early November 2021, Microsoft announced the private preview of its flagship database product, SQL Server 2022. At some point in 2022, we will see SQL Server 2022 released as a general availability (GA) product, but the specific release date has not yet been disclosed. We can expect to see a variety of the announced features in the next major release deployed to Azure SQL customers. Microsoft has a long history of deploying new features to the Azure cloud far in advance of the on-prem products.
Posted February 08, 2022
When it comes to improving the performance of database systems, there are nearly as many approaches as there are DBA (database administrator) teams. Mature disciplines—from medicine to aviation to accounting—also have mature processes that represent the generally accepted workflows and procedures needed for best results, or, at a minimum, to prevent the worst from happening. However, DBAs do not have an equivalent for the FAA handbooks found in every aircraft cockpit in America—that's a shame because so many of our DBA workflows and processes could be made easier, more reliable, and more predictable with a bit of planning and foresight.
Posted December 08, 2021
The world of the data professionals and DBAs is swirling with threats and risks, and those dangers are on the rise. You're probably accustomed to using longstanding database features to secure your databases, including roles and permissions, and you're likely familiar with working with your identity management admins to control and create your user authentication scheme.
Posted October 05, 2021
Due to my background working with other major database platforms such as Oracle, MySQL, and PostgreSQL, I've long been an advocate for the SQL Server Migration Assistant (SSMA). This powerful and easy product can quickly and effectively migrate most popular relational database platforms into SQL Server. The migration process using SSMA usually results in a 80/20 or 90/10 migration, in which 80%-90% of the database objects migrate without an issue. But 10%-20% typically requires additional hand-holding and manual migration work.
Posted June 10, 2021
An important new set of features for security and confidential computing was announced at the recent Microsoft Ignite virtual conference and are now in public preview. Although the name doesn't exactly trip off your tongue, Always Encrypted with Secure Enclaves offers important new capabilities for organizations that need greater control and security over their data while also enjoying the agility, scalability, and productivity gains of the public cloud.
Posted April 06, 2021
In 2020, we endured the COVID-19 pandemic's impact on health and life, and we also saw multitudes of businesses and organizations collapse. One of those casualties was PASS, also known as the Professional Association for SQL Server.
Posted March 01, 2021
DBAs and data management teams have long struggled with supporting data governance functions. Naturally, a well-managed organization wants to extract maximum value from their data assets. But that is incredibly difficult for companies whose data assets were created over a period of years or decades without an integrated, top-down data architecture.As these data management systems grow organically over time and as new data management systems are added, users of corporate data resources face a variety of challenges.
Posted February 10, 2021
Microsoft and Red Hat have collaborated for more than 4 years on SQL Server and have come up with a variety of ways to better manage the server internals for higher performance. For example, the Windows OS has Large Pages and an option called Lock Pages in Memory. In Linux, you have the same capabilities using Transparent Huge Pages via trace flag 834, and even more memory management options by tuning dirty page ratios to prevent disk swapping.
Posted December 10, 2020
Many organizations are building their new applications on Linux rather than the Windows operating system for reasons of security, performance, and manageability. In addition, they are now able to take advantage of avoiding mixed operating system environments in the public cloud. Many of these customers have been longtime Linux users—and although they loved SQL Server, they disliked having to run SQL Server on Windows since they were the only Windows servers in their entire enterprise.
Posted November 04, 2020
If you are an old hand at Microsoft SQL Server, you have probably used the SQL Server Agent (i.e., SQL Agent) service and MSDB database for the last 20 years. With a humble and largely unchanged feature set since it was first introduced, SQL Agent is one of those incredibly useful and cost-saving features we have come to expect from the Microsoft Data Platform. However, SQL Agent faces one important shortcoming: It can run on Azure VMs and on Azure SQL Managed Instances, but it is not available on Azure SQL Database. As a result, we need a substitute.
Posted October 08, 2020
Microsoft continues to make positive strides in the world of open source. The company once considered open source software to be an anathema, but now it's common for Microsoft to pull software from—and also push software to—the open source software community.
Posted August 11, 2020
Here's one big advantage to moving to Microsoft Azure SQL Database—you never have to patch. That's all well and good, but most of the SQL Server installed license base is still on-premise. If you're in this category, then it is essential that you keep up with patching and security fixes regularly. The information in this article is the sort that experienced SQL Server DBAs keep close at hand. So, let's take a few minutes to walk through the details of how to do that.
Posted June 10, 2020
I've kept you informed over the years about not just the latest news and features in SQL Server, but across the entire Microsoft Data Platform, both on-premise and in the cloud. This includes additional products such as SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and their cloud siblings, Azure Synapse Analytics, and Azure Data Factory (ADF). Another important and powerful sibling product is Azure Cosmos DB, previously known as Document DB, which I first wrote about here many years ago.
Posted April 08, 2020
Azure Arc and the Rising Tide of Kubernetes
Posted February 10, 2020
A New Chapter of Business Intelligence Begins with Azure Synapse
Posted January 02, 2020
The annual PASS Summit, the industry's largest gathering of SQL Server professionals, hosted more than 4,000 attendees in Seattle recently recently. PASS (www.pass.org) has grown to more than 300 local chapters around the world, and its 1,000th SQL Saturday will likely be held sometime in early 2020. The opening keynote by Rohan Kumar, corporate vice president of data and AI at Microsoft, focused on the achievements and growth of SQL Server in the marketplace, as well as on the new features and capabilities now available in the public release of SQL Server 2019.
Posted December 01, 2019
Back in the 1980s, a Stanford University computer scientist and professor named Donald Knuth introduced the concept of "literate programming," in which a computer program's logic is explained in a natural language such as English. The "literate" program would be further enriched with images, graphs, traditional source code, related macros, and scripts almost as if the programmer were writing an essay, and using all of these components together could still be compiled as usual. The most notable products to arise from this concept were notebooks, such as Azure Notebooks, Jupyter Notebooks, Apache Zeppelin, and the Apache Spark Notebook.
Posted October 01, 2019
The big cloud vendors tout many reasons for running IT infrastructure in the cloud. A very prominent benefit is "accelerated innovation and delivery." That's a powerful selling point because every IT manager I have ever known wants to deliver better results, faster, and at lower cost. However, it seems that the less IT managers know about doing actual hands-on IT work, the more demanding they are.
Posted September 03, 2019
Maybe you are still getting up-to-speed with the whole concept of cloud computing. If so, I have a lot of sympathy for you because there is so much to learn and because cloud technologies advance so rapidly. Here's one more important concept to add to that pile of things to learn—edge computing.
Posted August 07, 2019
SQL Server Drill Down: What's New in the Microsoft Data Platform, Build 2019 Edition
Posted June 10, 2019
Will the DBA disappear? No, far from it. But specific skills needed by DBAs and certain job roles fulfilled by DBAs will certainly change as we move to the cloud.
Posted May 01, 2019
If you haven't been paying a lot of attention, you might have missed the rise of a new form of virtualization, based at the operating system level, called "containers." While there are many specific software programs, by far the most prominent is Docker. So, from here on out, consider "Docker" and "container" to be synonymous.
Posted February 08, 2019
About 5,500 of the SQL Server faithful came out for the PASS Summit 2018 in Seattle last November. As usual, the event had the feel of a great big family reunion. It's always a fun time, with lots of socializing in the halls of the conference center and at the various restaurants and gathering places around town. The training was also top-notch this year with more than 16 tracks of sessions going on from early morning until evening—literally hundreds of SQL Server developer, DBA, architect, BI, and data science session
Posted January 02, 2019
The 2018 Microsoft Ignite conference was overflowing with attendees this year, as user enthusiasm continues to grow with the advent of CEO Satya Nadella.
Posted December 04, 2018
On August 30, Microsoft announced the public preview of the Azure Database Migration Service (DMS) to support online migrations of various databases to Azure. What's an "online migration"? Online migration means that your databases remain operational while conducting the migration. This provides quite a few obvious benefits.
Posted October 10, 2018
Microsoft is on the march with multiple upgrades and improvements across the span of its cloud data products. Here are a few of the more prominent highlights.
Posted August 08, 2018
On June 20th, the Microsoft SQL Server team dropped the most recent release of SQL Operations Studio to the public. This is just one of many, many product announcements that demonstrate Microsoft's very serious commitment to interoperability and to meeting the needs of their customers wherever they reside, even if that's outside of the Microsoft ecosystem. One of the hallmarks of SQL Operations Studio, compared to the venerable SQL Server Management Studio, is that it's lightweight and expandable.
Posted July 02, 2018
If you've used Azure in the past, you probably know that there are two main ways to deploy SQL Server on Microsoft's cloud—Azure SQL Database, the PaaS offering; and Azure VMs running SQL Server. Microsoft is now offering a third deployment option in preview which provides full SQL Server engine capability, including SQL Agent, along with native VNet support.
Posted June 01, 2018
Women's issues have headlined the news media for the past several months. Many stories, ranging from the #MeToo movement to the "Brogrammer" email blast at Google, have shown that women in technology (WIT) face negative work conditions and social pressures. In light of that, it seemed appropriate to dive into the topic of WIT within the SQL Server community (aka, the Data Platform community) and gauge where we're at.
Posted April 12, 2018
The SQL Server Vulnerability Assessment tool (VA) is a feature within SQL Server Management Server (SSMS) 17.4 that scans your SQL Server instances of version 2012 and later, identifies security issues, and suggests fixes to the vulnerabilities it finds. It works for on-premises SQL Server and Azure SQL Database, whether housed on physical or virtual servers.
Posted February 01, 2018
3 New Year's Resolutions for the SQL Server Pro
Posted January 02, 2018
This year's PASS Summit packed more than 5,000 data professionals into the Washington State Convention Center for hardcore technical sessions covering all aspects of the Microsoft data platform. Here are some highlights.
Posted December 01, 2017
At this year's Microsoft Ignite, a variety of executives made a host of announcements that are important and relevant to data professionals. It is worth noting that there were actually four areas of focused innovation discussed at the keynote level: enabling IT and developer productivity, providing a consistent hybrid cloud, unlocking intelligent solutions (read: AI), and ensuring trust through security, privacy, and cost controls.
Posted November 01, 2017
With all of the hype around data being "the oil of the 21st century" and the rise of "the algorithm economy," it seems reasonable to make it a priority to learn more about how laws affect our lives as DBAs. The first law that I have targeted is probably the best-known data law in the U.S.—HIPAA, the Health Insurance Portability and Accountability Act of 1996.
Posted October 18, 2017
The next release of Microsoft SQL Server, announced as SQL Server 2017, is available for download in CTP2 at http://tinyurl.com/SQLvNextLinux and offers many cool new features. One set of features that I really like focus on adaptive query processing. These new features speed processing of workloads, especially those that have historically been the hardest to tune.
Posted July 05, 2017
Not only has the Microsoft SQL Server engineering team added more and stronger leadership, outstanding individual engineers and staff members, but it is also radically moving its entire development process into a "cloud-first" ethos. Now, I'd like to show one of the most salient results of this new development process-accelerated product releases. It was only a few years ago (and is still the case for other data management platforms) that a major new release takes at least 2, but more likely 3 years. With the "production-ready" release of SQL Server 2017 Community Technology Preview 2.0, we are only 1 year from the last production release.
Posted May 05, 2017
I always look forward to new research from Unisphere Research, a division of Information Today, Inc., publishers of this magazine and other great products for data professionals. The latest report which you should read is "SQL Server Transformation: Toward Agility & Resiliency 2017; PASS Database Management Survey."
Posted April 07, 2017
SQL Server Named DBMS of the Year
Posted March 02, 2017
There's a very handy new tool from Microsoft called the Database Experimentation Assistant (DEA). The DEA makes it very easy to conduct A/B testing for SQL Server upgrades, providing quantitative insight into the performance differences across two versions of a database workload on SQL Server.
Posted February 08, 2017
One of the long-standing differentiators between SQL Server's Enterprise Edition (EE) and Standard Edition (SE), besides price, were the large number of features available only in EE. This is also true for other lower editions of SQL Server such as Web Edition and Express Edition. But there's big news in the air!
Posted January 03, 2017
If you are a SQL Server professional, but you don't know about the PASS Summit, then you are missing out. The annual conference is convened every fall in downtown Seattle, the backyard of Microsoft, and attracted over 6,000 attendees this year. And, since it's so close to the Microsoft Redmond campus, hundreds of the SQL Server developers and program managers get to attend—answering user questions, delivering sessions, and presenting chalk talks and panel discussions.
Posted December 01, 2016
Back when I managed my employer's enterprise architecture, prior to the days of virtualization and cloud computing, one of my most difficult projects was to build out a sandbox environment for a major proof of concept (POC). Locally hosted servers and all of the required licenses were expensive and scarce. If you're still struggling with scarcity when conducting research and performing POCs, I've got to tell you you're doing it wrong.
Posted November 02, 2016
SQL Server Adds Even More New Features in Data Science
Posted October 07, 2016
As a heavy user of other RDBMS data platforms, I have long appreciated the extra effort that Microsoft put into their data management and administration tool, the SQL Server Management Studio (SSMS). While SSMS is an outstanding administration and T-SQL programming tool, it does have its minor qualms and quibbles.
Posted September 02, 2016
I had the pleasure to spend some time with my old friend Mark Souza, a general manager in the Data Platform team at Microsoft, while speaking at the SQL Saturday event in Dublin, Ireland. Now keep in mind that Mark and I have known each other since the 1990s when SQL Server was just being ported to a brand new operating system called Windows NT. Mark and I were having a laugh and more than a twinge of nostalgia about how much SQL Server has improved over the decades and now sits atop the heap on most analysts' "best database" reports. This isn't just two old-timers sharing a few war stories though. This is a living, breathing transformation that is still in process.
Posted August 04, 2016
There's a new buzzword on the loose, the data lake. At first glance, a data lake could be easily mistaken for a data warehouse. The two big data concepts have a common focus on analytics and they may, in certain situations, produce roughly equivalent output. But that's about where their similarities end.
Posted July 12, 2016
While temporal data support is something that has existed in the past within other database platforms, it is a newly available feature with the RTM version of SQL Server 2016. In case you haven't heard of temporal data values (or for some, "bitemporal"), here is a brief explanation.
Posted June 09, 2016