Azure | SQL Freelancer Blog

Maintenance Windows for Azure SQL

By bradyupton in Azure, Maintenance August 30, 2024

The cloud has revolutionized the way we manage and maintain databases, offering new features and capabilities to enhance reliability and minimize disruptions. One such feature that has garnered significant attention is the maintenance window for Azure SQL Database.

Microsoft’s maintenance window feature allows database administrators to schedule planned maintenance events for Azure SQL Database resources during non-peak hours, effectively reducing the impact on mission-critical production workloads. By configuring a maintenance window, we can ensure that updates, patches, and other maintenance activities occur at a time that minimizes disruptions to our business operations.

The true power of this feature lies in its flexibility and customization options. While Azure SQL Database follows a default maintenance window designed to align with typical business hours, administrators can tailor the maintenance schedule to suit their unique requirements. Whether it’s opting for weekday or weekend maintenance slots, the ability to choose a time frame that aligns with our specific operational needs is a game-changer.

Moreover, the maintenance window feature comes with built-in advance notifications, allowing us to stay informed and prepared for upcoming maintenance events. This proactive approach enables us to plan accordingly, minimizing potential downtime and ensuring a smooth transition during the maintenance process.

It’s worth noting that the maintenance window is available for most service level objectives (SLOs) within Azure SQL Database, ensuring that organizations with diverse performance and availability requirements can benefit from this feature.

As database administrators, we understand the critical importance of maintaining a reliable and high-performing database infrastructure. The maintenance window feature in Azure SQL Database empowers us to strike the right balance between keeping our systems up-to-date and minimizing disruptions to business operations.

I encourage my fellow database professionals to explore this feature and leverage its capabilities to optimize their maintenance strategies. By doing so, we can enhance the reliability and availability of our database systems, ultimately contributing to the success of our organizations.

Click here to read more…

Delivering AI capabilities in SQL Server and Azure SQL

By bradyupton in Artificial Intelligence - AI, Azure August 15, 2024

As a seasoned database administrator, I can’t help but express my excitement about the latest development in integrating generative AI models into SQL Server and Azure SQL. Microsoft’s recent article sheds light on how we can effortlessly harness the power of AI within our database environments, unlocking a new realm of possibilities.

The seamless integration of generative AI models into SQL Server and Azure SQL is a game-changer. No longer do we need to navigate complex external connections or wrestle with intricate architectures. With a simple SQL query, we can tap into the vast potential of AI, empowering us to generate text-based content, gain insights, and make data-driven decisions like never before.

What truly sets this integration apart is its scalability and performance. These AI capabilities can handle large datasets and high traffic without breaking a sweat, ensuring that our data remains always up-to-date and our operations run smoothly. This level of efficiency and data freshness is crucial in today’s fast-paced business landscape.

But the implications of this integration extend far beyond mere convenience. By bringing AI directly into our database environments, we are paving the way for a future where data and intelligence are seamlessly intertwined. Imagine the possibilities – from automated report generation to predictive analytics and beyond, the boundaries of what we can achieve with our data are being pushed further than ever before.

As database administrators, it is our responsibility to stay ahead of the curve and embrace technologies that can truly revolutionize how we work. The integration of generative AI models into SQL Server and Azure SQL is a prime example of such a transformative advancement.

I encourage all my fellow DBAs to explore this exciting development and start experimenting with the power of AI within their database environments. Let’s collaborate, share our experiences, and collectively shape the future of data management.

To get started, I highly recommend checking out Microsoft’s step-by-step guide on using SQL Server and Azure SQL to generate text-based content with generative AI. It’s a fantastic resource that will walk you through the process and help you unlock the full potential of this groundbreaking integration.

Let’s embrace the future and harness the power of AI to drive innovation, efficiency, and data-driven decision-making in our organizations. The time is now, and the opportunities are boundless.

Introducing database watcher for Azure SQL

By bradyupton in Azure, Monitoring April 15, 2024

Reliable, in-depth, and at-scale monitoring of database performance has been a long-standing top priority for SQL customers. Today, we are pleased to announce the public preview of database watcher for Azure SQL, a managed database monitoring solution to help our customers use Azure SQL reliably and efficiently.

Managed monitoring for Azure SQL

To enable database watcher, you do not need to deploy any monitoring infrastructure or install and maintain any monitoring agents. You can create a new watcher and start monitoring your Azure SQL estate in minutes.

Once enabled, database watcher collects detailed monitoring data from your databases, elastic pools, and managed instances into a central data store in your Azure subscription. Data is collected with minimal latency – when you open a monitoring dashboard, you see database state as of just a few seconds ago.

Enhanced patching for SQL Server on Azure VM with Azure Update Manager

By bradyupton in Azure, Security September 7, 2023 0 Comment

With Azure Update Manager, unlike with the existing Automated Patching feature, you’ll be able to automatically install SQL Server Cumulative Updates (CUs), in addition to updates that are marked as Critical or Important.

Azure Update Manager is a unified service that helps manage updates for all your machines. By enabling Azure Update Manager, customers will now be able to:

Perform one-time updates (or maybe Patch on-demand): Schedule manual updates on demand
Update management at scale: patch multiple VMs at the same time
Configure schedules: configure robust schedules to patch groups of VMs based on your business needs:
Periodic Assessments: Automatically check for new updates every 24 hours and identify machines that may be out of compliance

thumbnail image 1 of blog post titled

Announcing public preview of enhanced patching for SQL Server on Azure VM with Azure Update Manager

Using ChatGPT with SQL Server

By bradyupton in Artificial Intelligence - AI, Azure, Development January 23, 2023 0 Comment

If you’re in the tech field you’ve probably heard the buzz around ChatGPT and Artificial Intelligence.

ChatGPT is a pre-trained language model developed by OpenAI. It uses deep learning techniques, specifically a transformer neural network architecture, to generate human-like text. It can be fine-tuned on specific tasks such as language translation, question answering, and text summarization. ChatGPT can be used to generate a wide range of text, including conversation, news articles, stories, and more. Because it is pre-trained on a large corpus of text, it can generate text that is often indistinguishable from text written by a human.

To preview ChatGPT, shoot over to https://openai.com, create a login and you can try it for free (at the time this post was written).

For this post, since I’m a DBA, I want to test how it performs when I give it questions about SQL statements.

So let’s start! I asked the following question:

“How can ChatGPT be used to learn TSQL?”

And here’s a great response:

ChatGPT can be used to learn TSQL in several ways:

You can use ChatGPT to generate TSQL code snippets and queries based on natural language prompts. For example, you can ask ChatGPT to "generate a TSQL query to select all columns from a table named 'customers'" and it will respond with the appropriate TSQL code.
You can also use ChatGPT to explain TSQL concepts and provide examples. For example, you can ask ChatGPT to "explain how to use the JOIN clause in TSQL" and it will provide a detailed explanation and examples of how to use the JOIN clause in TSQL.
You can also train ChatGPT model on TSQL text data and fine-tune the model for generating TSQL queries, this way you can generate more accurate and complex TSQL queries.
You can use ChatGPT as a code completion tool to assist you in writing TSQL code.

Not a bad start. Let’s see if it can create a database.

“Can you write SQL code to create a SQL database named AI?”

Look correct. Let’s test in SQL Management Studio.

OK, OK, that was too easy ChatGPT. Let’s see if it can create a table in our new database and insert some dummy data.

“Can you write SQL code to create a table called Orders in my AI database and populate with some test data?”

Let’s test the code.

Looks good. How about a basic SELECT statement?

“Can you write SQL code to give me all data from my Orders table?”

Again, looks good. It even goes as far as explaining it and giving an example by only selecting specific rows. This is great for learning.

Let’s see if it can create an aggregate query.

“Can you write SQL code to give me the Customer that has the highest TotalAmount from the Orders table?”

From my question, I was hoping for something a little different, but I can’t get mad at the results. The query uses LIMIT 1 instead of SELECT TOP 1 which would work in MySQL, but doesn’t exist in SQL Server. It was really close though and it even gives me an example and explanation of how this would be used in a JOIN.

Let’s be more specific. If I change the question to specify SQL Server:

“Can you write SQL code to give me the Customer that has the highest TotalAmount from the Orders table in SQL Server?”

Again, not really what I was looking for, but it works. ChatGPT ended up writing more code than it needed and even wrote this statement in a common table expression (CTE). I was looking for something more along the lines of this:

SELECT TOP 1 CustomerID, MAX(TotalAmount) as MaxAmount FROM Orders GROUP BY CustomerID ORDER BY MaxAmount DESC

Either way, both statements work and produce the same results.

How about something a little more difficult such as creating a partition. Partitions are heavily used in other database platforms so I’m going to specify SQL Server again in this question.

“Can you write T-SQL code to partition my Orders table, OrderDate column by year on SQL Server?”

This was a little more challenging and it wrote out the Partition Function and Partition Scheme statements correctly, but it added a OrderDateRange column as an integer and then it tries to create a clustered index where OrderDate is datetime and OrderDateRange is int so the end result is a failure.

All in all, I think this is a great tool for learning basic (and even some advanced) SQL, but it still has some bugs to work out before it tries to replace me. 😉

Introducing SIOS high availability for SQL Server 2008/R2 in Azure

By bradyupton in AlwaysON, Azure, Clustering, High Availability, Upgrades/Migrations August 12, 2019 0 Comment

Support for SQL Server 2008 and 2008 R2 ended on July 9, 2019. That means the end of regular security updates. However, if you move those SQL Server instances to Azure or Azure Stack, Microsoft will give you three years of extended security updates at no additional cost. If you’re currently running SQL Server 2008 or 2008 R2 and you are unable to update to a later version of SQL Server, you will want to take advantage of this offer rather than running the risk of facing a future security vulnerability. An unpatched instance of SQL Server could lead to data loss, downtime, or a devastating data breach.

If you have a SQL Server failover cluster instance (FCI) or use hypervisor-based high availability, or other clustering technology on-premises for high availability, you’ll probably need the same in Azure. If you need to migrate to Azure/Azure Stack at end of support, and you require high availability for SQL Server 2008/2008 R2, there’s only one solution recommended by Microsoft: SIOS DataKeeper. SIOS DataKeeper enables clustering in the cloud, including the creation of a SQL Server 2008/2008 R2 failover cluster instance, allowing you to achieve your high availability goals.

Click here to view the rest of this post.

Create Azure Windows VM

By bradyupton in Azure February 7, 2019 1 Comment

Creating an Azure Windows VM seems pretty easy, but there are a lot of settings to be careful of before hitting the Create button. In this post, we’ll go over some of those so the next (or first) time you create an Azure VM, you’ll be ready!

Here we go….

Log into the Azure portal and click Virtual Machines and Create Virtual Machine:

Note: I’m using a free trial.

Under the Basics tab, there are a few options. Let’s start at the top.

Subscription: An Azure subscription is the agreement with Microsoft to use Azure, for which charges accrue. You can create multiple subscriptions to separate billing or management.

Resource Group: A container that holds related resources for an Azure solution. The resource group includes those resources that you want to manage as a group. You decide how to allocate resources to resource groups based on what makes the most sense for your organization.

Virtual machine Name: The identifier and host name for the VM.

Region: The region in which your VM will reside. To see more go here: https://azure.microsoft.com/en-us/global-infrastructure/regions/

Availability Options:

No infrastructure redundancy required: No High Availability.
Availability Zone: VM’s are in a different physical location within an Azure region. This offers 99.99% SLA.
Availability Set: A group with two or more virtual machines in the same Data Center is called Availability Set, this ensures that at least one of the virtual machines hosted on Azure will be available if something happens. This configuration offers 99.95% SLA.

Image: The base Operating System for the VM. The following Windows versions are supported. https://docs.microsoft.com/en-us/azure/azure-stack/azure-stack-supported-os

Size: This is the size of the VM. Here’s a good chart to use when sizing. Remember, pricing! Another cool thing with Azure is that you can resize pretty easy by shutting down the VM, resizing it, and restarting. Of course, this depends on your region and availability options. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sizes

There’s also a free tool on the web that will help you size your VM based on certain resources. https://www.vmchooser.com/vmchooser

Username: The administrator username for the VM

Password: The administrator password for the VM

Public inbound ports: Ports that are accessible from the public internet.

Already have a Windows license?: You can save money if you already have a Software Assurance license or an Active Windows server subscription.

In my example, I’m using a Free Trial, so that’s the only option for Subscription. For Resource Group, let’s create a new group (by clicking Create New), named SQLFreelancerRG.

I’ll name my VM SQLVM, put it in the East Region, with no redundancy and a fresh copy of Windows Server 2016 DataCenter.

Next, I’ll size this VM with the Standard DS1 v2, which is going to give me 1vCPU and 3.5GB RAM. I’ll create an Administrator account, allow no ports from the public internet and choose No for the Azure Hybid Benefit.

The next page is all about the Disks. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/about-disks-and-vhds

OS disk type: This is type of disks we can use for the VM. Let’s look at Standard vs. Premium:

You can also create and attach a new disk or attach an existing disk (from snapshot of another disk or BLOB storage)

In my example, I’ll use a Premium SSD disk and I’ll create a second disk called SQLVM_DataDisk_1.

The next page gets into a little Networking. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/network-overview

Virtual Network: A virtual network enables VM’s to communicate privately with each other, and with the internet.

Subnet: The subnet is the range of IP addresses in the Virtual Network.

Public IP: Public IP addresses allow Internet resources to communicate inbound to Azure resources. Public IP addresses also enable Azure resources to communicate outbound to Internet and public-facing Azure services with an IP address assigned to the resource. The address is dedicated to the resource, until it is unassigned by you. If a public IP address is not assigned to a resource, the resource can still communicate outbound to the Internet, but Azure dynamically assigns an available IP address that is not dedicated to the resource.

NIC network security group: Security rules in network security groups enable you to filter the type of network traffic that can flow in and out of virtual network subnets and network interfaces.

Public inbound ports: Ports that are accessible from the public internet.

Accelerated Networking: Enables low latency and high throughput on the network interface.

Load Balancing: Azure Load Balancer delivers high availability and network performance to your applications. A load balancer can be configured to balance incoming Internet traffic to VMs or balance traffic between VMs in a VNet. A load balancer can also balance traffic between on-premises computers and VMs in a cross-premises network, or forward external traffic to a specific VM.

For this post, I’m just going to use the defaults and move forward.

Next up is Monitoring. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/monitor

Boot diagnostics: As VMs boot, the boot diagnostic agent captures boot output and stores it in Azure storage. This data can be used to troubleshoot VM boot issues.

OS guest diagnostics: Allows you to get metrics every minute for your VM. You can use them to create alerts and stay informed.

Diagnostics storage account: The storage account in which your diagnostics are stored.

System assigned managed identity: Managed identities for Azure resources provides Azure services with an automatically managed identity in Azure Active Directory. You can use this identity to authenticate to any service that supports Azure AD authentication, without having credentials in your code. https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/qs-configure-portal-windows-vm

Enabled auto-shutdown: Configures your VM to automatically shutdown each day.

Enable backup: Auto backup for your VM.

I’m going to keep defaults and click Next.

The next page is Guest Config where you can add additional configuration, scripts, agents, or applications via VM extensions. Click Next.

Tags are name values pairs that enable you to categorize resources and view consolidated billing. Think of this as Twitter hashtag.

Last page is Review and Create. This is the page that displays a summary of our selections including price per hour of uptime. You’ll also notice at the bottom there is a link that will allow you to download a template for automation. This will allow you to use the same specs that you created for this VM on other VM’s. Click Create to build your new VM.

If you click on the Notification Bell at the top of the portal, you’ll see that Deployment is in progress.

Once the VM is created, you can click Virtual Machines from the Favorite Bar and view your newly created VM:

If you click on the new VM it will bring up the Overview page (along with tons of other pages to choose from).

Click on the Networking tab and Add inbound port rule. Under Destination Port Range type 3389 and under Name type RDP inbound. This will allow RDP access.

Click Connect and Download RDP file to remote into the server.

TADA!