T-SQL Tuesday Archives - Page 3 of 3

In this post I respond to the May 2022 T-SQL Tuesday #150 Invitation “Your First Technical Job” and talk about my time as an Application Support Developer.

Introduction
Application Support
Summary

Introduction

This month, Kenneth Fisher’s T-SQL Tuesday invitation was as follows:

This month for TSQL Tuesday I’d like to hear about your first technical job(s). I know most DBAs don’t start out working with databases so tell us how you did start.

So let’s go back in time!

Application Support

My first technical job was as an Application Support Developer at Think Money Group. I’d been with TMG for almost six years at the time, starting in a customer service role before moving into an administrative role in 2012.

By 2016 I had a deep understanding of the organisation’s front and back ends, and had built several Excel and Sharepoint-based solutions to make various internal and regulatory processes easier to manage.

When a vacancy in Application Support became available that Summer, I went to meet the team and put an application in. My roles to date, knowledge of the organisation and technical skills were a good fit, and so I was welcomed aboard!

Application Support was responsible for supporting TMG’s bespoke applications. We had several tasks that I will give a brief overview of.

Ticketing System Triaging

This was my introduction to a ticketing system. As a team, we completed tickets from the backlog based on priority and age.

Incoming tickets needed to be triaged before they entered the backlog. This involved a couple of checks like:

Prioritisation: How urgent is the ticket?
Relevance: Is the ticket a reasonable request? Is it a duplicate of an existing ticket?
Serviceability: Is anything missing from the ticket? Can it be completed in its current form?

A correctly triaged and well-maintained backlog was essential. As a team, it helped us meet our SLAs and prevent bottlenecks and duplication. As individuals, it increased productivity and simplified the working day.

Servicing

Common tickets would involve giving users access to services or features within the application. For example, granting access to certain reports, or changing a user’s setup to alter their view on certain screens.

This didn’t need any code changes as it was handled within the application. Even so, care was needed to make sure requests were handled correctly, and that principles of least privilege were followed.

Scripting

Other common tickets would involve changing data at scale. For example, adding notes onto accounts when letters were sent. The application could handle this with small amounts of letters. However, it made more sense to insert notes directly into the database when there were thousands of them.

These tickets served as my introduction to SQL Server. I inserted new data into tables. I updated tables using temp tables and cursors. Every test customer once ended up with the same surname when I didn’t use a WHERE clause properly.

Mistakes are part of learning though. At least it wasn’t production…

Learning about the testing and production servers then introduced me to other SQL Server concepts like Agent Jobs, Linked Servers and Replication. So the floodgates were well and truly opened!

Troubleshooting

Users would sometimes get errors that they would send in for investigation. Common ones might be caused by:

Insufficient permissions for a certain screen.
Deadlocked processes in the backend database.
Unsupported application version.
Bugs in a new release.

Firstly, I would contact the user to explain what was happening. I would then either fix the problem or record it as a bug.

For the unclear errors, I would make contact with one of the Software Engineers for guidance. We’d then either go through the error together, or I’d escalate the error if it was very complex.

Summary

In conclusion, I have talked about my time as an Application Support Developer and have given an overview of what the role involved. In addition, I have explained how the role introduced me to SQL Server.

Thanks to Kenneth for this month’s topic! My previous T-SQL Tuesday posts are here.

If this post has been useful, please feel free to follow me on the following platforms for future updates:

Thanks for reading ~~^~~

In this post I consider the February 2022 T-SQL Tuesday #147 Invitation “Upgrade Strategies” and look at the importance of upgrades in the cloud.

For this month’s T-SQL Tuesday, VoiceOfTheDBA’s invitation was as follows:

This month I want you to write about how you look at SQL Server upgrades. A few things you might think about:

Why we wait to upgrade?
Strategies for testing an upgrade
Smoke tests or other ways to verify the upgrade worked
Moving to the cloud to avoid upgrades
Using compatibility levels to upgrade an instance by not a database.
Checklists of things to use in planning
The time it takes to upgrade your environment
What you evaluate in making a decision to upgrade or not?
Anything else

Immediately I was drawn to “Moving to the cloud to avoid upgrades”. Some perceive the cloud as a ‘set it and forget it’ environment. The reality is that cloud services still require upgrades that can cause security vulnerabilities and data issues if left unchecked.

What follows are some SQL based observations from my experience to date. While it’s true this list is AWS specific, it isn’t AWS exclusive as Azure and GCP operate similar services with similar considerations.

EC2 Upgrades

When I create a new EC2 instance I can generally expect it to be running the latest build of my chosen OS. However, an instance that has been running for a while will soon find itself needing system updates like any other computer. Some updates offer performance improvements or new features and are essentially optional. Others fix security vulnerabilities and bugs and are non-negotiable.

If that instance is running my relational database of choice, that too will need a range of updates from the desirable to the critical. AWS views this as a customer responsibility, with the AWS Shared Responsibility Model including the following:

Customers that deploy an Amazon EC2 instance are responsible for management of the guest operating system (including updates and security patches), any application software or utilities installed by the customer on the instances, and the configuration of the AWS-provided firewall (called a security group) on each instance.

However the managed services are viewed differently:

For abstracted services, AWS operates the infrastructure layer, the operating system, and platforms, and customers access the endpoints to store and retrieve data. Customers are responsible for managing their data (including encryption options), classifying their assets, and using IAM tools to apply the appropriate permissions.

So what if I get AWS to run my database for me?

RDS Upgrades

Amazon Relational Database Service (RDS) offers managed relational databases including Microsoft SQL Server, MySQL and PostgreSQL. RDS still uses EC2 instances but here they are managed by AWS and are not accessible by the user. This means OS management is no longer a customer responsibility.

Upgrades to the database engine are still a factor though. AWS try to make this as painless as possible – upgrades can be done using the console, AWS CLI or the RDS API. This is still a manual process though, although it is possible to upgrade some minor engine versions automatically.

However, even on rails it’s still possible for an update to go wrong. AWS have a nine-point checklist for testing an upgrade that wouldn’t be out of place on-premises. AWS also encourage database snapshots and non-production testing. While RDS removes infrastructure complexity, the data is still the customer’s responsibility and needs the same care as ever.

Operational Upgrades

AWS constantly release new services intended to simplify workflows and reduce costs. Even when an organisation’s cloud setup is fully mature, it can still benefit from upgrading to these services.

When Athena debuted in 2016 it enabled the analysis of data in S3 using standard SQL. This removed the need for complex ETLs and data warehouses, and with Athena being serverless it was faster to set up and cheaper to operate than EC2, RDS or Redshift.

In 2020 Amazon announced new EBS GP3 volumes. GP3s have separate settings for performance and storage, and are recommended for applications like MySQL that need high performance at low costs. This meant organisations could save money by reducing their use of the more expensive IO1 volumes.

More recently, AWS announced a new S3 Glacier Instant Retrieval storage class in 2021. This made S3 less expensive for a range of use cases including SQL backup storage and data lake archival.

Conclusion

The Cloud offers numerous opportunities for individuals and organisations to develop, build and deploy quicker and easier. But upgrades are a fact of life in technology regardless of platform. The cloud is still a collection of computers, which still need to respond to changing requirements and threats.

A well maintained and fully upgraded cloud environment is reliable, scalable and secure. A poorly maintained one can, at best, be expensive, slow and unwieldy. At worst it can be unreliable, vulnerable and in breach of terms of service.

If you want to check the health of your AWS account, AWS offers their Trusted Advisor and Well-Architected Tool services. These give free architectural advice, security recommendations, cost optimisations and best practice guidance.