SQL Server Archives - SQL Freelancer Blog

Transforming Roles: The Evolution of SQL Database Administrators

By bradyupton in Uncategorized January 22, 2024

The role of a SQL Database Administrator (DBA) has evolved significantly over the years as technology and business needs have advanced. This evolution can be traced back to the early days of databases and continues to adapt to the latest trends in data management. In this post, we will explore how the SQL Database Administrator position has changed over the years, focusing on key developments and shifts in responsibilities.

Early Days of SQL DBA (1970s-1980s)

The history of SQL databases can be traced back to the 1970s when the concept of relational databases was first introduced by Edgar F. Codd. During this era, SQL databases were primarily used by large organizations for data storage and retrieval. The role of a SQL DBA was relatively straightforward, involving tasks such as data modeling, schema design, and query optimization.

SQL DBAs in the early days were responsible for managing physical storage, ensuring data integrity, and optimizing database performance. They worked closely with developers to design efficient database schemas and tune SQL queries for better performance. However, the scope of their responsibilities was limited compared to what it would become in the future.

The Rise of Enterprise Databases (1990s)

The 1990s saw the proliferation of enterprise databases, with Microsoft SQL Server, Oracle, and IBM DB2 gaining popularity. This period marked the beginning of a significant shift in the role of SQL DBAs. As organizations increasingly relied on databases to store critical business data, SQL DBAs became more integral to the IT infrastructure.

During the 1990s, SQL DBAs were tasked with database installation, configuration, and maintenance. They had to ensure high availability and data backup strategies to prevent data loss. Additionally, security became a more prominent concern, with SQL DBAs responsible for implementing access controls and encryption to protect sensitive data.

Internet Boom and E-Commerce (Late 1990s-2000s)

The late 1990s and early 2000s witnessed the explosion of the internet and the rise of e-commerce. This had a profound impact on the role of SQL DBAs. Databases became the backbone of online applications, and uptime and scalability became paramount.

SQL DBAs had to adapt to the demands of 24/7 availability and handle large volumes of data. They were now responsible for performance tuning on a massive scale, employing techniques like indexing, caching, and partitioning to ensure fast query response times. Scaling databases horizontally and vertically to accommodate growing workloads became a common challenge.

Cloud Computing Era (2010s)

The 2010s brought about a significant transformation in the IT landscape with the advent of cloud computing. Cloud-based databases, such as Amazon RDS, Azure SQL Database, and Google Cloud SQL, became popular choices for organizations looking to reduce infrastructure costs and increase scalability.

SQL DBAs had to adapt to managing databases in the cloud, which introduced new challenges and opportunities. They had to master cloud-specific database services and learn how to optimize costs while maintaining performance and security. Automation and scripting also became crucial skills as cloud providers offered tools for infrastructure as code (IAC) and database management.

Data Explosion and Big Data (2010s-Present)

The explosion of data in the 2010s, driven by social media, IoT devices, and increased digitization, posed another major shift in the role of SQL DBAs. Traditional relational databases were no longer sufficient to handle the sheer volume of data being generated.

SQL DBAs had to adapt to the world of big data, which included technologies like Hadoop, NoSQL databases, and distributed data processing frameworks. They needed to understand when to use traditional SQL databases and when to leverage alternative solutions for specific use cases. This required a broader skill set and the ability to work with a variety of data storage and processing technologies.

Data Security and Compliance (2010s-Present)

With data breaches becoming more prevalent, data security and compliance became a top priority for organizations. SQL DBAs found themselves taking on additional responsibilities related to securing data, implementing encryption, and ensuring compliance with regulations such as GDPR and HIPAA.

SQL DBAs also had to stay updated on the latest security threats and vulnerabilities and implement best practices to protect databases from unauthorized access and data breaches. This aspect of the role required a deep understanding of cybersecurity principles and the ability to work closely with security teams.

Automation and DevOps (2010s-Present)

In recent years, automation and DevOps practices have transformed the way SQL DBAs work. DevOps principles emphasize collaboration between development and operations teams, with a focus on automating repetitive tasks and achieving continuous integration and continuous delivery (CI/CD).

SQL DBAs have embraced automation tools and scripting languages to streamline database deployment, configuration management, and monitoring. They now play a critical role in enabling the rapid release of database changes while maintaining stability and reliability. This shift has also led to a more proactive approach to database management, with DBAs actively participating in the development process.

Data Analysis and Business Intelligence (2010s-Present)

As organizations recognize the value of data-driven decision-making, SQL DBAs have expanded their roles to include data analysis and business intelligence (BI) tasks. They are now involved in creating data warehouses, designing data models for analytics, and supporting BI tools like Tableau, Power BI, and QlikView.

SQL DBAs work closely with data analysts and data scientists to ensure that data is available, accurate, and accessible for reporting and analysis. This shift highlights the need for SQL DBAs to have a broader understanding of the business context and the ability to translate data into actionable insights.

Machine Learning and AI Integration (2020s-Present)

The integration of machine learning (ML) and artificial intelligence (AI) into applications has further expanded the role of SQL DBAs. They are now tasked with managing databases that store and serve data for ML and AI models. This includes optimizing database performance for real-time inference, handling large datasets for training, and ensuring data quality for ML/AI algorithms.

SQL DBAs may also collaborate with data scientists to deploy ML models within databases and establish data pipelines that feed data to these models. This intersection of traditional database management and emerging technologies highlights the evolving nature of the role.

Conclusion

In conclusion, the role of a SQL Database Administrator has undergone significant changes over the years, reflecting advancements in technology, the growth of data, and evolving business needs. From its humble beginnings as a data storage and retrieval specialist, the SQL DBA has become a multifaceted professional responsible for ensuring the availability, performance, security, and strategic use of data within organizations.

As we move into the future, SQL DBAs will continue to adapt to emerging trends, including cloud-native databases, data analytics, AI/ML integration, and the evolving cybersecurity landscape. The ability to learn and evolve with the ever-changing technology landscape will remain a key characteristic of successful SQL DBAs, ensuring their continued relevance in the world of data management and IT infrastructure.

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

Install SQL Server on Windows 10 using Docker

By bradyupton in Docker, SQL Server 2017 December 5, 2019 0 Comment

Starting with SQL Server 2017, Microsoft announced you can run SQL in Docker containers. This is great news for DBA’s because we tend to run multiple installations of SQL Server locally and with Docker we can spin up (or remove) a SQL instance fairly quickly.

I’m not going to go into what Docker is because there’s an entire website that goes into much more detail than I ever could. This post is going to focus on a simple SQL installation on Windows 10 using Docker. Let ‘s gooooooo!

First, let’s download and install the latest version of Docker. To find the latest version, open an internet browser and navigate to https://docs.docker.com/docker-for-windows/install/. From here, click “Download from Docker Hub”

You will need to create a docker ID and password if you don’t have one. Once you have this info let’s login.

Next, let’s click “Get started with Docker Desktop” from the home page and “Download Docker Desktop for Windows”

After the download is complete, go ahead and start the install. On the Configuration screen, I’m going to Use Windows containers instead of Linux. Click OK.

Docker Desktop is installing:

After installation succeeds, you must log out so make sure to save everything!

Upon logging back in, you will be greeted with this message, Hit OK and reboot.

After reboot, let’s open Powershell (as administrator) and issue the following statement. This will show you that Docker is installed properly and it will give you the version we are running.

Docker Version

Next, let’s do a search for a SQL Server image. Back in Docker Hub, in the search box, let’s type SQL Server and click on Microsoft SQL Server:

This page will give you all sorts of valuable information.

You can also use the following PowerShell statement to pull SQL Server images:

Docker search Microsoft

Again, from Powershell, let’s download the Microsoft SQL Server Developer Edition images. To do this use the following:

Docker pull microsoft/mssql-server-windows-developer

This will take a few minutes so grab some coffee…..or a beer.

Once the download is complete you can run the following statement to see the images downloaded:

Docker images

OK, we have Docker installed and our SQL Server image downloaded. Now the fun part….install SQL Server! In PowerShell, let’s run the following:

Docker run –name DockerSQL -d -p 14331:1433 -e sa_password= St0ngP@SSw0rd! -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

–name – this is the name of the container you are creating
-d – Runs the container in the background otherwise the container will take over your cmd when it starts
– p port:port – Maps a TCP port on the host environment (first value) with a TCP port in the container (second value). In this example, SQL Server is listening on TCP 14331 in the container and this is exposed to the port, 1433, on the host. I’m using 14331 because I’ll create multiple containers and each container needs a different port number.
-e – Creates environment variables under the container execution runtime, for example, sa password and End User License Agreement (EULA)
sa_password – Create sa password for SQL instance (Hint, don’t use a $)
ACCEPT_EULA – Yes

It will take a few minutes to finish the install, but it’s much shorter than a regular SQL install. Once finished, run the following to get the IP address of the container (make sure to use your container name after docker inspect):

As you can see, the IP for my container is 172.31.29.174. If I open SQL Server Management Studio and connect to this IP using the sa account and password I created I can connect successfully:

If you have any issues connecting try using the following docker statement to view the logs:

Docker container logs –details DockerSQL

To remove the container, simply stop the container

Docker stop DockerSQL

And remove.

Docker rm DockerSQL

Planning and Building Microsoft SQL Server on Amazon RDS

By bradyupton in AWS, RDS April 16, 2019 0 Comment

Planning and building SQL Server in RDS doesn’t have to scare you. It’s actually pretty easy and in this post will go over planning a SQL Server deployment in RDS, creating SQL Server in RDS, and last but not least configuring the new instance of SQL Server.

Let’s jump in…

Planning the deployment is important because if your SQL Server instance needs a feature that RDS doesn’t support, then RDS isn’t an option. Here are a few, but you can get the full list here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

Log Shipping
SSRS, SSIS, SSAS, Data Quality Services, or Master Data Services
Database Mail
Maintenance Plans
SQL Server Audit
FILESTREAM support
Policy-Based Management
Replication

RDS also has some limitations that don’t exist with an on-premise SQL Server. Let’s look at a few:

As of this post, Amazon only supports SQL 2017 RTM, SQL Server 2016 SP2, SQL Server 2014 SP2, SQL Server 2012 SP4, and SQL Server 2008R2 SP3
You can only have a maximum of 30 databases per instance and 16TB of storage.
Linked Servers
Windows Authentication (must use AWS Managed Microsoft AD)

Again, the full list can be found here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

Pricing

Once you can verify that your environment will run properly in RDS you’ll need to look at the pricing model. When you setup RDS for SQL Server, the software license is included. AWS used to have a program called “Bring your own license” or “BYOL”, which allowed you to use a license that was already bought from Microsoft via an agreement or other. This has been rumored to expire on June 30, 2019. The software license that is included means that you don’t need to purchase SQL Server licenses separately. AWS holds the license for the SQL Server database software. Amazon RDS pricing includes the software license, underlying hardware resources, and Amazon RDS management capabilities. The pricing will depend on the selections such as size, edition, etc.

The following editions are supported in RDS:

Enterprise
Standard
Web
Express

Notice, Developer Edition is not included with RDS and Web Edition supports only public and internet-accessible webpages, websites, web applications, and web services.

Instance Type

You can also choose from On-demand or reserved instances. On-Demand DB Instances let you pay for compute capacity by the hour your DB Instance runs with no long-term commitments. This frees you from the costs and complexities of planning, purchasing, and maintaining hardware and transforms what are commonly large fixed costs into much smaller variable costs. This is good for development environments where you can power on and off the server as it’s being used.

Reserved Instances give you the option to reserve a DB instance for a one or three year term and in turn receive a significant discount compared to the On-Demand Instance pricing for the DB instance. Amazon RDS provides three RI payment options — No Upfront, Partial Upfront, All Upfront — that enable you to balance the amount you pay upfront with your effective hourly price.

You can see more about on-demand vs reserved instances and pricing here: https://aws.amazon.com/rds/sqlserver/pricing/

RDS provides a selection of instance types optimized to fit different relational database use cases. Instance types comprise varying combinations of CPU, memory, storage, and networking capacity and give you the flexibility to choose the appropriate mix of resources for your database. Each instance type includes several instance sizes, allowing you to scale your database to the requirements of your target workload. View more details here: https://aws.amazon.com/rds/instance-types/

Storage

Another item to look at when planning your deployment is storage. RDS uses Amazon Elastic Block Store (Amazon EBS) volumes for database and log storage. Depending on the amount of storage requested, Amazon RDS automatically stripes across multiple Amazon EBS volumes to enhance performance.

RDS offers three different storage types:

General Purpose SSD – also called gp2, this storage type offers cost-effective storage that can be used for a broad range of different workloads. These volumes deliver single-digit millisecond latencies and the ability to burst to 3,000 IOPS for extended periods of time. I would recommend putting small to medium sized databases on this type.

Provisioned IOPS – This storage type is designed for I/O intensive workloads, particularly database workloads that require low I/O latency and consistent throughput. This is also built on SSD and targeted for IO intensive, high performance databases. Cost wise, this is the highest of the three storage types.

Magnetic – This storage type is mostly used for backward compatibility. Amazon recommends using gp2 or Provisioned IOPS for any new builds. This is ideal for test and dev environments when performance isn’t a concern. This is the cheapest of the three storage types.

See more about storage here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html

Network

One more item to consider when planning the deployment is network connectivity. Applications will more than likely need to connect to your RDS environment so there are a few import concepts to look at it.

Availability Zones – this is simply a data center in an AWS region. The following AWS regions exist.

Virtual Private Cloud – also called VPC, this is an isolated virtual network that can span multiple Availability Zones. It’s used to group different types of resources to the network that need to talk to each other.

DB Subnet Group – this is a collection of subnets inside a VPC that contains the RDS instance addresses. See more about VPC and DB subnet groups here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.WorkingWithRDSInstanceinaVPC.html

Create SQL Server RDS via AWS Console

Now that we’ve outlined some of the deployment planning tasks, let’s build an instance through the AWS console.

Once inside the console, we’ll click on RDS under the Database heading:

Once we are on the home page for RDS, we can click Create Database under Get Started. There’s also info for Pricing and costs and some documentation on getting started:

Notice in the top right corner is the Availability Zone in which you are logged into. In my case, I’m logged into US East (Ohio) since I’m on the Central Time Zone and Ohio is located closer to me than any other zone:

Back to the Select Engine page. For this post, I’m going to install Microsoft SQL Server Express, but you can see the other database engine platforms that are available and the associated editions:

Next page we can see some of our database details. There are all items we discussed in the planning deployment section above. License model, DB engine version, DB instance class, Time Zone, Storage Type and allocated storage are all configurable on this page. Below are my selections:

Scroll down to Settings header and configure the DB instance identifier, Master username and password. The DB instance identifier is a unique name for your DB instances across the current region. For this RDS instance I’ll name it SQLFreelancer:

On the Advanced Settings page we’ll configure Network and Security, Windows Authentication, Database Options such as port number, Encryption (where available), Backup retention, Monitoring, Performance Insights, Maintenance options, and Deletion protection. I’m going to choose all the defaults for this post, but this is a page where you want to make sure you choose what is best for your environment.

Once you are finished on the Advanced Settings page, click Create Database.

Now how easy was that? Creating a new DB instance took about 2 minutes. Once your instance is created let’s click on View DB Instance details:

The details page gives you all sorts of info about your instance:

Microsoft SQL Server on Amazon RDS in a Nutshell

By bradyupton in AWS, RDS April 3, 2019 0 Comment

As defined by Amazon, Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups. It frees you to focus on your applications so you can give them the fast performance, high availability, security and compatibility they need.

RDS is also referred to as a Database as a Service (DbaaS) or Platform as a Service (PaaS) not to be confused with Infrastructure as a Service (IaaS) which we’ll discuss in the next paragraph.

DbaaS vs. IaaS

DbaaS	IaaS
You can choose any DB platform such as Oracle, MySQL, SQL Server, Amazon Aurora, PostgreSQL, and MariaDB	You create a Virtual Machine and install OS and DB platform such as SQL Server
DbaaS takes care of backups, High Availability, Patching, OS, underlying hardware	Iaas will only take care of the VM host layer and it’s hardware. You will need to manage patching, HA, security, etc. This is essentially like an on premise server.

Being an Operational DBA, there are a few tasks that RDS will take over freeing up time for the DBA to focus on other things. Some of those tasks include the following:

Backups: RDS will continuously take backups and allow point in time restore capabilities. We no longer have to worry about disk space or archiving backups to another location.
HA: RDS can automatically setup mirroring to another data center which allows for redundancy of databases.
Patching: RDS will automatically patch your SQL Server based on a maintenance window defined by you.
Add Resources such as CPU/Memory: RDS can increase CPU or Memory on demand as opposed to managing an on premise where the server might need downtime and you would have to orchestrate the change with Server Administrators.
Upgrade: With a push of a button you can automatically upgrade SQL Server and easily roll back if necessary.
Monitoring: Instead of buying a third party monitoring tool and running through the setup RDS provides a service called CloudWatch that can easily tap into SQL Server and alert when things go wrong.

Wow! All of these items make managing a SQL Server much easier for a DBA right? Do you even need a DBA if you’re running RDS? Of course you do! While it does make some tasks easier for a DBA, RDS will not do the following:

Write queries, tune queries, test queries: RDS has no knowledge about the data in each DB. Only a DBA knows the application and business processes to write and tune queries.
Manage DB security, change control, configuration settings: Only a DBA familiar with all of the procedures of his/her company can really make sure the environment is secure, that all changes are being documented, and that a specific configuration applies to what the databases are supposed to do.
Tune indexes or maintenance: Again, only the DBA knows what databases might need indexes or aren’t using specific indexes. You also know when to run maintenance procedures.

Now that we’ve discussed some of the pros and cons, why do businesses use DbaaS?

The speed of provisioning increases business value because instead of waiting weeks to bring a server online including purchasing software, servers, licenses, managing resources, etc. you can click a few buttons in the Amazon console and have a fresh SQL Server online in a matter of minutes.

The automation of regular tasks means there’s less possibility of human mistake and less hours spent by the admins patching and managing certain parts of the servers, which means no more late nights. Employees can now spend more time query tuning, deploying new functionality, and making sure the performance is the best. All of this leads to increased revenue for the business.