I’m often asked how one should choose what aspect of SQL Server they should focus on when starting out. Essentially, they’re trying to decide what job role they should prepare for. There’s a lot of variety offered by SQL Server, and there are many different job titles. In my eyes, though, every job in SQL Server is a blend of one of three core job roles: Database Developer, Database Administrator, or Business Intelligence Developer. Let’s talk about the differences between these 3 key roles.
The Database Developer
Database developers spend time creating and coding databases. Often database developers are also application developers as well. Developers might spend their time creating a data model. Data modeling is the process of defining structure for a database, usually following a process called normalization to make data accessible and relational. (Future posts will teach you the basics of data modeling, since it is a critical skill that every database professional should understand.). Developers spend much of their time writing T-SQL code that executes in the database. We call these objects “programmability”, and they include items like stored procedures (T-SQL to perform repeatable operations by calling a single command), functions, and triggers (automated commands that occur when triggered by another action in the database).
Database developers frequently move from project to project. Developers might interact with customers (usually, the people who will be using the system being developed). As a result, it is important for developers to learn the skill of working with non-technical staff, which requires patience and frequent clarification. Database development is a good role for anyone who likes experimenting with new ideas, and who likes the changing scenery of new projects.
The Database Administrator
Database administrators, or DBAs, are responsible for maintaining SQL Server solutions. DBAs are the guardians of data, and they perform this role in many ways. It is the job of the DBA to ensure that authorized users can access their data efficiently. DBAs administer security by granting or preventing access to each database. DBAs perform a critical set of tasks that can make or break their career: database backup and recovery. Preparing for disasters might not seem important until you find that you can’t recover from one. Many DBAs have lost their job because they were unable to recover a database after some critical failure. If this sounds scary, it’s because it is scary. Nothing will make a DBA’s blood run cold than a 2am call to let them know that the database has gone down. As a result, good DBAs spend time preparing for disasters, testing backups, and automating solutions.
Database administrators are also responsible for making the database run well. They have knowledge of indexing and query optimization, and can read execution plans to find ways to make queries run more efficiently (in other words, faster). It is also not uncommon for the DBA to benchmark performance so they understand how a system should perform under normal operating conditions.
As you might suspect, some DBAs see their job as dull days interspersed with periods of sheer panic; I don’t count myself among them. I am primarily a DBA and one lesson I learned early was that good DBAs prevent emergencies before they happen. Sure, there will always be some things beyond your control to prevent, but that’s where your disaster recovery plan comes in. Database administrators should expect to spend some time on-call, but the amount of time might depend on the size of your team. If you’re the only DBA for your team, expect to be on call all the time. If you work for a large company, this duty usually rotates. Some companies hire DBAs to shifts, so that on-call duties don’t rotate, but these seem to be the exception to the on-call rule.
Given that, being a DBA can be very rewarding and exciting work. If you enjoy solving new problems everyday, choosing go become a DBA could be the right job for you. Companies want to know that their data is secure, so note that you’ll likely need to start as a junior DBA and work your way up (in other words there is room for advancement).
The Business Intelligence Developer
Business Intelligence Developers are a recent addition to the ranks of database professionals. BI developers spend their time analyzing data, building data warehouses and creating reports. Often times, BI developers are responsible for combining many sources of data into a “single point of truth”. SQL Server Integration Services (SSIS) is an amazing tool that can extract data from many sources and help a BI developer to populate their data warehouse.
BI developers get to work with other specialized tools, including SQL Server Analysis Services (SSAS). The tools are sophisticated, so BI developers often have to wear many hats. For example, data cubes a common feature of a data warehouses. These specialized tables provide dimensional data and are frequently used to understand how data changes over time. Dimensional data cubes can be queried using a language called MDX (Multi-Dimensional eXpressions, or depending on who you ask, Multi-Dimensional eXtensions), so this is another very specialized tool that the BI developers must master as they progress in their career.
Once The BI developer has reached the data they want to present, they use SQL Server Reporting Services (SSRS) or another reporting tool to build intelligent reports. SSRS can do some amazing things, and when used properly can make data beautiful. More importantly it can bring out trends and predict changes in an almost magical way.
BI projects are usually every expensive, which also means they are high profile within a company. Executives often are the recipients of the reports and dashboards the BI project, and so when things go wrong with a BI project, that failure is certainly recognized. Additionally, since this is a relatively young facet of databases, failure could be more common with companies that are not accustomed to BI projects.
It seems to me that BI developer positions are more likely to be contract positions than the other two jobs. SQL Server, and Information Technology (IT) in general have a larger consultant base than many other office jobs, but as a BI developer it seems that moving from contract position to contract position is common.
If you like working with exciting technology, love working with data, and can handle high-profile successes (and, let’s be honest, failures) then Business Intelligence Development might be for you.
The Way These Roles Blend
I’ve never seen a database professional who focused only on just one of these aspects. I often say that to be a good SQL developer, you need to know something about administration. A good example: developers often need to understand how queries you write will be handled by the database engine and the Optimizer (don’t worry if you don’t know what these are yet – that’s ok – just note that these are usually considered in the realm of the DBA).
As a DBA you’ll often assist in tuning queries, which requires development skills. You might also build SSIS packages (a BI task) to populate your databases or extract data to send to another system. DBAs often build data models, too.
BI developers work in such a different environment that they need to know much about database development and sometimes about administration, too. BI works differently; databases are structured differently, and DBAs may not always be familiar with the best practices to build good data warehouses.
I’ve found it very helpful to focus primarily on my chosen job role, database administration, while specializing in features of each of the other jobs. In my case, this is data modeling on the development side, and SSIS on the BI side. Don’t expect to need all skills as you start out, though! Add to your toolkit as you go and soon you’ll be building unique solutions. Choose your calling well and don’t be afraid to switch course mid-stream. The knowledge you gain in one database job role will certainly be valuable in any other you choose.