Data Architect for SQL Server

By: Eric Blinn | Updated: 2021-12-09 | Comments (1) | Related: More > Professional Development Career Planning

Problem

I have been looking at SQL Server job listings and have come across a few openings a for a Data Architect. What is a Data Architect? What is expected of someone that holds that title?

Solution

The Data Architect has a lot of different responsibilities that are focused on security and availability of company’s data footprint. They work regularly with members of the business and IT staff to guide related decisions.

This tip is going to consider what an average day of your life might look like if you held the role of Data Architect. Of course, as with most jobs there probably isn’t a truly typical day, but hopefully it will offer some insight into what sort of tasks a data architect will encounter.

Meet with the Business

Your day starts out in a meeting with the operations group and several members of the IT staff. The operations group informs you that the ERP software vendor has told them that the ERP software version they are running is approaching end of life and that the company needs to upgrade to a new version by the end of the year.

The presenter shares the latest requirements document from the ERP vendor. The IT hardware group is excited because the hardware running the ERP software is pretty old and due for an upgrade. The IT operations group is happy to see some older versions of Windows getting upgraded as part of this project. Those are all interesting facts, but your eyes are focused elsewhere in the document. The software needs a database server running Microsoft SQL Server 2016, 2017, or 2019. You declare that you would prefer to upgrade to 2019 since it allows for Transparent Data Encryption on Standard Edition and the company can finally encrypt the ERP system data at rest. The IT security team applauds this decision.

The operations group further informs the IT team that it is imperative that this system not go down for any significant amount of time. They would like to budget for whatever costs would get them to their RTO/RPO goals.

HA/DR Planning and Testing

The next thing on the to-do list for today is to put together a plan for both SQL Server HA and DR in the new ERP environment. Luckily, you are quite familiar with the different offerings available in SQL Server 2019 Standard Edition and can make a wise suggestion. Based on the RTO/RPO goal of the operations team you suggest an Availability Group for HA and transaction log shipping for DR. That information will be sent over to other members of the IT team so that any hardware or software costs can be calculated and included in the operations budget request.

Once the budget is approved, you will work with the operational DBAs to ensure they are prepared to properly setup, administer, and test this new environment. Further, you will supervise the first few tests to be sure everyone involved knows what to do in the event of a SQL Server failover.

Technology Selection

As the data architect, you need to stay up on new technologies. The next item on your to-do list for today is to spend some time reading. As of the writing of this tip, SQL Server 2022 is in private preview. Data architects should be reading all they can about this new version of SQL Server. Are there any features in it that would help any of the company’s projects? While you’re at it, should the company consider using Oracle? What about MySQL? Are there any places that HDFS would help the company?

When those types of decisions need to be made, they’ll be made with a lot of input from you. You must make sure that you are ready to speak intelligently about the many different options in the space. You don’t need to be expert level on any of these technologies – you aren’t a DBA after all. Just be aware of what is out there and have some idea of what sorts of problems they solve.

Help Design a New Application

The development team has been tasked with building a new web application to track reservations of available desks in support of the company’s new mostly-remote hoteling office model. Before they build a database, they want your help to be sure it is built properly.

You take advantage of your knowledge of SQL Server and relational database normalization to review the data model. It is important that the database schema be well designed for performance and security.

Since security and privacy are things that concern the data architect you pay close attention to the PII stored in the new database. Upon initial review you notice that the developers copied the entire employee table from HR into their data model. You inform the developers that there is no reason for the employees’ government identification numbers and salaries to be stored in this system that records only seating locations. They can only store the information necessary to perform that task. For this system that is limited to employee number, name, and home office. Their data model will need to be updated to reflect this change.

In this meeting with the developers, you learn that they want to use a new object-relational mapping (ORM) tool to help them write their code faster. Knowing that these types of software can be troublesome at times you ask to vet the software. Which brings us to…

Meet with a Vendor

The developers agree that they will need approval from the data architect before they can purchase software that will directly impact the company’s data platform. A meeting with the technical sales support staff from the vendor is arranged. In the meantime, you are going to look up this company and their product.

During this meeting you focus on the performance and security of the ORM. You need to learn if this software meets the company’s needs in those respects. The developers will be the end users so the day-to-day usage of the ORM isn’t your concern.

Final Thoughts

The data architect takes both a high-level and long-range look at the data platform of the company. If that sounds like something you want to do then maybe a data architect role is right for you.

Next Steps

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-12-09

A Day in the Life of a SQL Server Data Architect