The READ COMMITTED transaction isolation level is the default setup of Microsoft SQL server. With the committed read transaction isolation level, data cannot be read from a data page until all UPDATE, DELETE, or INSERT operations performed by other users or connections on the table which you are trying to read from has been completed and committed to the database.
When running a select query on any database without specifying the isolation level, the query is executed under the read committed transaction isolation level. Hence it is the default.
To explain exactly what this means, I’ll take a financial system as an example. Senior management wants to check the revenue of the month by running a report. One of the managers is a bit ahead of the curve and tries to run the revenue report while the company HR is running the monthly payroll processes. The managers’ report just keeps running without returning any data. Eventually when the payroll process finishes, the managers’ report returns the revenue data. The manager, who was a bit hasty, had to wait for the payroll to finish before he could see the data and with good reason. If he was able to see the data before the payroll was completed, he would have seen false revenue figures as the salaries of all the employees have not yet been deducted which affects the revenue.
How do I specify the read committed transaction isolation level in my script?
With any default installation of SQL server, it is not necessary to specify the read committed transaction isolation level as this is the server default and any query that is run on the server without specifying an isolation level will run under the read committed transaction isolation level. If you would like to specify the read committed transaction isolation level to make sure the query runs under said transaction isolation level regardless of what the SQL Server default is, you can set it by adding the following statement to the beginning of your script or transaction:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
The transaction isolation level will be applied to the entire transaction in which it is set.
To demonstrate the read committed transaction isolation level, open SQL Server Management Studio and run the following create script:
IF OBJECT_ID('Sales_E636F1418128466888ED3A3CECC07611') is not null
begin
DROP TABLE Sales_E636F1418128466888ED3A3CECC07611
end
Create table Sales_E636F1418128466888ED3A3CECC07611
(
SalesID int identity(1,1),
SalesDate datetime,
CustomerFirstName varchar(50),
CustomerLastName varchar(50),
PaymentMethod varchar(50),
SalesAmount decimal(16,2)
)
insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20160905','Alan','Wilkinson','Credit Card',59.50)
insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20161011','Joey','Hollins','Credit Card',712.63)
insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20170406','Evander','Emmet','Cash',265.42)
insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20170512','Martin','Curtis','Cash',34.78)
insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20170518','Alton','Batts','Credit Card',275.65)
select * from Sales_E636F1418128466888ED3A3CECC07611
Open a new query tab and run the following UPDATE statement. This query tab will represent the first user or session:
BEGIN TRANSACTION
UPDATE Sales_E636F1418128466888ED3A3CECC07611 SET PaymentMethod = 'EFT'
where SalesID = 2
WAITFOR DELAY '00:02:15'
COMMIT TRANSACTION
Open a new query tab and run the following SELECT statement. This query tab will represent the second user or session which is trying to read the same data that the first user or session is updating:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
SalesDate,
CustomerFirstName + ' ' + CustomerLastName as Customer,
PaymentMethod,
SalesAmount
FROM
Sales_E636F1418128466888ED3A3CECC07611
WHERE
SalesID = 2
You will notice that the second query keeps running and does not return any data until the first query which is updating the data finishes its execution. This is the read committed transaction isolation level in action. The data that the second query is requesting will only be returned once the first user or session has committed its changes to the database.
As an added measure, open a new query tab and run the following statement:
sp_who2 ‘active’
In the result, look for the spid of the second user or query (session 2 script). You can see the spid by looking at the tab. In my example the spid I am looking for is 55.
In the results of the sp_who2 script, find the spid. You will notice that your session 2 spid is being blocked by the spid of your first user or session query (Session 1 script). In my case the spid of the first session is 54.
This is because session 2 will first have to wait for session 1 to complete the update of the data and commit it to the database before it can read the data.
As with everything there will be pros and cons in using the read committed transaction isolation level in different situations. In the end, there is a reason why it is the SQL server default. You will always see the correct data.
One thing to keep in mind is that just as you cannot read data under the read committed transaction isolation level that other users are updating, other users cannot update data which you are reading using the read committed transaction isolation level. Their spid will be blocked until you have finished reading the data before their update can start. This prevents any data from being modified while you are doing a select and will prevent dirty data which is a clear difference from the read uncommitted transaction isolation level.
Pro tip: When transactions are taking long to complete like updating data, running reports etc. Always check sp_who2 ‘active’. You might find a lot of blocking transactions which could be explained or solved by using the correct transaction isolation levels.
Sometimes referred to as a “dirty read”, the read uncommitted isolation level reads any data on a SQL Server data page regardless of whether or not the data has been committed. This means that you will see all committed and modified uncommitted data within the database.
There are a many pro’s and cons to consider when using the read uncommitted isolation level. One of the most noticeable pros is query speed. The read uncommitted isolation level will speed up queries as it does not have to wait for other blocking transactions to complete before it gets a turn to read from the table being modified by a different user. This pro, however, goes hand in hand with a con. On the flip side, you will see “dirty data” which may have been modified and may still be modified later in the transaction. Between these modifications, you may see data that does not make logically sense at that point in time. This especially holds true to financial systems where a lot of number crunching is being performed. If the system has been developed in such a way that a lot of changes occur before the final data is committed and you read that data, your figures may be skewed.
To demonstrate how this could potentially be a problem, let’s take an ordering system as an example. We have 100 laptops in stock. Sales person A is dealing with a client who wants to buy 20. Sales person B is dealing with a client who wants to buy 100. Sales person A adds 20 laptops to their cart which updates the stock level to 80. This data, however, has been updated but not yet committed. Sales person B checks the stock level using a query which reads uncommitted data and sees that only 80 laptops are in stock. The stock level of 80 is dirty data because sales person A has not yet confirmed and finalized the sale whereby committing the new stock level data to the database. In reality, the stock level is still 100 instead of the dirty read value which is 80. Sales person B now tells their client that they only have 80 laptops in stock and they lose the sale because the client is insisting on 100 laptops in one sale. Next, Sales person A’s client backs out of the sale because the price is too high. Sales person A cancels the order and the 80 stock level is never committed to the database. The stock level thus remains on 100 which could have filled the need of salesperson B’s client. Because sales person B was doing an uncommitted read on the same data sales person A was working with, they had the wrong impression of the stock level and lost the sale of 100 laptops.
You can set the read uncommitted transaction isolation level in any transaction or script by adding the following at the beginning of any transaction:
SET TRANSACTION ISOLATION READ UNCOMMITTED
The isolation level will be applied to the entire transaction in which it is set. Setting the transaction isolation level to read uncommitted is similar to specifying NOLOCK on every table which data is read from within your transaction.
To demonstrate the read uncommitted transaction isolation level, open SQL Server Management Studio and run the following create script on a test database:
IF OBJECT_ID('Sales_E636F1418128466888ED3A3CECC07611') is not null
begin
DROP TABLE Sales_E636F1418128466888ED3A3CECC07611
end
Create table Sales_E636F1418128466888ED3A3CECC07611
(
SalesID int identity(1,1),
SalesDate datetime,
CustomerFirstName varchar(50),
CustomerLastName varchar(50),
PaymentMethod varchar(50),
SalesAmount decimal(16,2)
)
insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20160905','Alan','Wilkinson','Credit Card',59.50)
insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20161011','Joey','Hollins','Credit Card',712.63)
insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20170406','Evander','Emmet','Cash',265.42)
insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20170512','Martin','Curtis','Cash',34.78)
insert into Sales_E636F1418128466888ED3A3CECC07611(SalesDate, CustomerFirstName, CustomerLastName, PaymentMethod, SalesAmount)
values('20170518','Alton','Batts','Credit Card',275.65)
select * from Sales_E636F1418128466888ED3A3CECC07611
The result set should show after the script has completed and the table has been created.
Open a new tab. This tab will represent the first session, or user, who will be updating the data. Run the following code in the first tab:
BEGIN TRANSACTION UPDATE Sales_E636F1418128466888ED3A3CECC07611 SET PaymentMethod = 'EFT' where SalesID = 2 WAITFOR DELAY '00:00:15' ROLLBACK
Open one more tab which will represent the second session, or user, who will be reading the uncommitted (dirty) data. Run the following code in the second tab:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT SalesDate, CustomerFirstName + ' ' + CustomerLastName as Customer, PaymentMethod, SalesAmount FROM Sales_E636F1418128466888ED3A3CECC07611 WHERE SalesID = 2
If you run the session 2 script while the session 1 script is running, you will see the following result set:
As you can see, the payment method is EFT, the same payment method as it was updated to in the session 1 script.
When you run the session 2 script again, after the session 1 script has completed, you will see the following result set:
Wait a minute!? The Payment method is Credit Card now? We updated the value in the session 1 script to EFT? And confirmed the update with a query? WHAT’S GOING ON? Steady now, if you check the session 1 script, you will notice that the transaction is never committed so the updated data is never committed.
As you can see, reading data using an uncommitted transaction isolation level can give you unexpected results. In most cases, this should not be an issue as the default setting of SQL Server nowadays is to commit data to the database automatically whereas with earlier versions you had to manually commit your changes to the database. That had to be a pain! The default of SQL Server is also to use that read committed transaction isolation level so you have to deliberately specify the usage of the read uncommitted transaction isolation level. In the end. When you use the read uncommitted isolation level and you see funny or unexpected results, keep all of the characteristics of this isolation level in mind. You may just end up saving yourself a lot of frustration.
Waiting for worker thread. The message every DBA loves to see in their job activity monitor. Yeah right! Although seldom noticed, these do tend to pop up from time to time. Especially when using PowerShell steps within your jobs. I will demonstrate the issue using PowerShell jobs.
What does this message mean and why do you see it in the job activity monitor? It basically means that the job is sitting in a queue, waiting to execute. Microsoft implemented limitations for the various subsystems which SQL Server uses. This was done for a good reason. It would be a mess if queries were waiting to execute due to a bunch of PowerShell jobs all running at the same time and hogging the worker threads. As we know, however, default configuration is not always the best configuration and this depends on your specific needs. I tend to run all my jobs and ETL processes on a separate server set up specifically for that purpose. This is a standard practice for me. On this server, no production queries are run and I can increase the worker threads for my jobs whether they are SSIS ETL processes, PowerShell backup jobs or normal database maintenance jobs. In this event, I can alter the subsystem limitations to fit my requirements.
Under the default subsystem configuration you can create 3 PowerShell jobs to see how this issue occurs. Each job should have a PowerShell step with the following code:
Start-Sleep -s 600
It’s a simple line which tells the PowerShell subsystem to wait for 10 minutes.
After creating the jobs, you can execute them one after the other from the SQL Server Agent. In the activity windows, you see that all three jobs are running. They should look like the screenshots below
If you open the job activity monitor, you will notice that PowerShell Job 1 and PowerShell Job 2 is executing as expected. Take note the Status message of PowerShell Job 3. As predicted, waiting for worker thread. This is what the message will look like in a production environment.
As you can imagine, this could have serious ramifications if you have jobs that kick of close to each other where your processes will be delayed heavily if these limitations of simultaneous execution is reached.
As mentioned earlier, this is due to the subsystem limitations implemented by default. You can check the subsystem limitations on your server by running the following query:
SELECT [subsystem_id]
,[subsystem]
,[max_worker_threads]
FROM [msdb].[dbo].[syssubsystems]If you check the values of the PowerShell subsystem, you will see the limitation is set to 2 by default. This ties up with the previous experiment where PowerShell Job 1 and PowerShell Job 2 were both executing and PowerShell Job 3 was sitting and waiting in the queue (or waiting for a worker thread if you will) to execute. This subsystem limits you to run only 2 PowerShell processes simultaneously.
To change the subsystem limitation, you can update the table in which the subsystem limitations reside directly and restart the SQL Server Agent. That’s all there is to it. As an example, you can use the following statement to update the subsystem limitations for PowerShell to 3.
UPDATE msdb.dbo.syssubsystems
SET max_worker_threads = 3 --Increase this value to increase the subsystem limitation
WHERE subsystem = N'PowerShell'After performing this change, you will be able to run all three of the test PowerShell jobs, which was created above, simultaneously and none of them will be waiting for a worker thread.
As with any SQL server change, I will again mention, be careful and take note in what you are doing. You may have to do some proper planning when changing these values and this should not be done just because it “may” solve your problem. Always make sure and make sure you update the right values. SQL does not ask questions. It assumes you know what you are doing.
We all know there are many features within management studio that we take for granted. One of these features is the Object Dependencies feature. In short, this tool will show you any objects which are dependent a chosen object. For instance, if you want to check which objects are dependent on a specific table. Object dependencies will include any Views, Stored Procedures etc. which references the table in question.
This information is easily retrievable in management studio by right-clicking on the object you want to check and clicking on “View Dependencies”. The following screen will show with a list of dependencies for the chosen object.
Source Object
Referencing Objects
Recently, I had to develop a process to automatically deploy SQL scripts to a specific server through a TFS process. Although I did not create the TFS process, I had to make sure that TFS could execute one stored procedure to figure out which objects to create or alter and in which order.
As an example, the problem is that if you try to create a View which references a table which does not exist, the creation of the view will fail which means that the process will fail.
All I had to work with was the object names, the script files and the database. Hmmm. That View Dependencies functionality would come in pretty handy right now. The problem is that I need the results in SQL so that I can query the results to check which objects have dependencies and in which order I should execute my deployment SQL scripts to ensure a successful deployment.
I set out to write my own stored procedure to analyze object dependencies for me.
To install the object dependencies procedure, run the install script on the database on which you want to analyze the object dependencies.
To use the object dependencies stored procedure execute the following command:
exec sp_GetObjectDependencies
The procedure will execute with the default parameter which analyzes all objects on the database excluding constraints.
The main 2 columns to note in the result set is the SourceObject and the ReferencingObject. The SourceObject is the object which was analyzed. The ReferencingObject is the object which is dependent on the source object.
To view all options available, execute the object dependencies stored procedure with the @help parameter:
exec sp_GetObjectDependencies @Help = 0
Now you can view all object dependencies on an object in SQL Server in data format which can be queried and used to achieve all sorts of wonderful things.
Object dependencies Install Script:
A DBA is a specialized individual responsible for the performance, integrity, and security of a database.
These points are a very high level overview of what a DBA does. Your duties as a DBA can get extremely integrated and detailed depending on what type of DBA you are or want to be.
Check out this video by Technology Profession for a great explanation of what exactly a DBA is and what a DBA does:
There are many types of DBA’s. The type of DBA you are classified as varies largely depending on what your duties, for which organization, and how the organizations structure is laid out.
Operational DBA’s are responsible for monitoring the operation of databases including uptime and performance. Operational DBA’s will act on numerous amounts of issues such as performance issues, space related issues, data corruption issues etc. Operational DBA’s are also, usually, responsible for backups strategies on the production databases as well as any other operational critical elements of the databases.
Database architects are responsible for creating large enterprise grade data architecture. In short, Database Architects plan, design and develop databases which will later be used by the business to fulfill a specific needs or requirements such as storing data for a new software product. Database Architects are responsible for the planning and development of the data architecture, such as the servers which the databases will run on, the physical design of the database according to best practices, the implementation of the database architecture etc.
Database Analysts are responsible for the planning of data collection from various business entities or departments. Database Analysts develop and manage the flow of said data between departments. Data Analysts used to be called data administrators. A database analyst is one example where their duties may overflow with those of a system DBA.
Data Modelers are responsible for gathering the requirements of data for business entities or departments. The data modeler will then design a data model adhering to these requirements and needs. Data Modelers and Data Analysts frequently work fairly close together and in some instances their duties may also overlap.
Application DBA’s design and develop databases for a specific application. They are experts in writing and debugging extremely complex SQL statements and make sure that the database design, application design and code standards work seamlessly together adhering to best practices for optimal performance.
Large organisations and corporations sometimes appoint DBA’s whose sole purpose is to focus on one specific area or task. Although this is not very common, they do exist. An organisation will, for instance, appoint a DBA who is specifically responsible for the backup plans, processes, and procedures on their enterprise systems.
A performance analyst is a type of task oriented DBA whose sole responsibility is to monitor the performance of the server and immediately act and resolve any issues which may affect the performance of the server. Performance Analyst DBA’s are experts in understanding the inner workings of the Database Management System server, how it fits together and what may or may not cause these performance issues.
DW administrators are responsible for the day to day activities and upkeep of data warehousing systems. Data warehouse administrators are extremely skilled in data migration, data flow and the tracking and monitoring of data changes.
In more common environments, you, as DBA will ultimately fulfill more than one of these roles at a time and will most likely experience, and be labelled as, every one of these DBA types during your career. As a DBA you will wear many hats and you never know what you will be doing next. That’s half the fun of being a DBA.
DBA’s spend most of their time, if not all, working directly with the Database Management System, or DBMS for short, in which they specialize. Although we focus a little more on Microsoft SQL Server in this article, there are many database management systems out there.
Some database management systems are used more on some platforms than others. It is for instance more likely that you will see much more of MySQL on web platforms than you will see Microsoft SQL Server or Oracle.
Some of these database management systems are also more common in the enterprise arena than others. You are very likely to come across all of them during your career as a database administrator.
It’s not an easy choice to make and definitely not a light hearted decision either. There are so many options and I can fully understand the frustration and confusion, especially if you are completely new to all of this DBA wizardry.
There are a couple of questions you have to ask yourself before you decide which DBA career path you should follow.
When you have asked yourself the difficult questions, you know, those types of questions for which you will be better off lying horizontally on an uncomfortable couch before answering, you should have a pretty good idea as to what type of DBA will suit you the most. Follow your gut and go for it. There is nothing worse than being stuck in a job where you do the same thing over and over and you do not enjoy it. Trust me, that’s life. A lot of being a DBA is doing the same thing over and over again.
Now is the time to choose in which Database Management System you want to specialize in. As mentioned previously there are many options. I have listed the top 5 most common database management systems but there is a wealth of others out there. It’s up to you to choose the right one. To help you choose, there are a few considerations you have to keep in mind when deciding which DBMS you would like to specialize in.
Do research about how many jobs are available for individuals who specialize in the DBMS which you are considering. Is there a need for someone who specializes in the DBMS which you are considering in your current organization? Speak to the relevant team members and managers and get their input about the DBMS which you are considering to specialize in. These guys are in the industry and they come with experience. You might just find that they will invite you to join their team after successful completion of your certifications. This means you can skip the job searching and build up great experience alongside people you most likely already know.
Research the market related salaries in your area for entry level DBA’s and work out for yourself if you can make a living by specializing in the DBMS which you are considering. This may not be an issue if you are younger but if you are already established in a position which pays good money, it will be something to think about. Specializing in a different DBMS might just give you that little extra to get you through the month in the beginning stages.
Technically, there are a lot of technologies out there that will be able to interact with each other in one way or another. With enough effort you should theoretically be able to integrate different technologies with each other such as using Microsoft SQL Server Integration Services to read data from Oracle or IBM DB2 both of which are not Microsoft products. The cases in which these combinations exist though, are rare. It is more common to start a job with an organization where all the surrounding technologies are from the same vendor. Microsoft is a perfect example of this. Organizations who use Microsoft SQL Server as the DBMS tend to stick with Microsoft’s surrounding technologies such as SQL Server Integration Services for ETL Processes, SQL Server Reporting Services for reporting and SQL Server Analysis Services for their Business Intelligence and Data Warehousing requirements.
Consider how difficult it would be to transition from one type of DBA to another. From experience I can say that in the Microsoft SQL server space this should not be too difficult as the exams prepare you for the full range of DBA types. Furthermore, many of the tasks between the different DBA types overlap. It is fairly simple to transition into a new DBA type.
Where do you want to be in 5 years? It is highly unlikely that your answer would be in the line of “Still backing up databases”. Consider the career growth opportunities when deciding on a DBMS. Career growth is usually coupled very closely to role transitioning and supporting technologies. If there are more supporting technologies to sink your teeth into, there will be more roles for you to fulfill and in the end more career paths and better career growth.
Great! You have successfully decided what type of DBA you want to be and in which DBMS you want to specialize in. You can get off that uncomfortable couch now. It’s time to pull up your sleeves and start working on getting to know your DBMS so that you can get those certifications to start your career as a DBA.
You will need training, and lots of it. Luckily, you live in the age of the internet. Online training and various other sources are available everywhere. Most of them for free.
Doing a quick search on YouTube for tutorials on your chosen DBMS will reveal a wealth of tutorial videos which will help you jump over that first hurdle. Search for your chosen DBMS tutorial i.e. “SQL Server 2016 tutorial”
Google is a DBA’s best friend. No day shall pass without using it. Whether it is to check your syntax or finding a script, it is your best friend. In this instance you can use it to search for getting started tutorials on you chosen DBMS.
Free information could be a little more difficult to come by if you chose a less common DBMS to specialize in. For more common DBMS’s there is a wealth of information available for free on various websites. Websites such as w3schools and onekeydata are valuable resources for SQL training where the latter also has training for Data Warehousing and Business Intelligence. Remember, SQL (Structured Query Language) is, as its name states, a language. The SQL language is used across multiple DBMS’s. The structure of the language may vary over the different systems but the underlying concepts remain the same. This makes it a valuable skill to have.
Using resources to get you started on your new adventure as a DBA is a great start. You will soon be familiar with all the different tools that are used as well as some of the terminologies used in the field. It is, however, always a good idea to get some sort of training which relates directly to the exams which you will write should you decide to get certified. The internet is full of institutes, websites and services where you can get the required training and exam preparation to prepare you for your certification exams. This training will also prepare you to be a DBA. You can find training in various formats and it’s up to you to choose which form of training works for you. Here are a few common training mediums which you should consider:
Some may say there is nothing better, although I am not a fan. There are many great institutes where you can do old school classroom training which will prepare you for you journey. The only downside to classroom training is that usually the schedules are not flexible and it takes forever. The courses are laid out in specific time frames and there is no way for you to study at your own pace. On the subject of becoming a Database Administrator, this could take quite some time. Unfortunately, with some DBMS technologies and paths, you have no choice but to go the classroom training route. Be sure to keep this in mind when you choose a DBMS to specialize in.
As the saying goes, “There is no friend as loyal as a book”. Check your favorite book store. This could be online or offline. You will find tons of books containing a wealth of information on the DBMS you have chosen. Whether you are a beginner or a seasoned DBA, there’s a book out there just for you. Some books were written with the certification exams in mind which will prepare you for all of the modules which you will be tested on in the DBA certification exams.
Digital content has taken the internet by storm. Gone are the days where you have to sit in a crowded classroom where you cannot study on your own pace. Online courses give you the ability to train anywhere you like, whenever you like. The training courses are usually presented by industry leaders who have years of knowledgeable experience. Experience which they share with you through their training videos. Most of these online courses such as the CBT Nuggets & AppDev courses work alongside the exam specifications. This means that they train you to understand each module which you will be tested on in your exams when doing your certification.
Time to ace those exams and get your certifications. Which certifications you get will be based on which DBMS you chose to specialize in as a DBA. There are certifications for every major DBMS and some certifications have different paths which you can take if you don’t decide to complete all paths. The first levels of the certification though will give you the groundwork to start your career as a DBA. Microsoft is a good example of this process. On the Microsoft learning website, the SQL server Certifications are laid out quite well according to the different levels. At the entry level you will complete the Microsoft Technology Associate (MTA): The fundamentals of IT – Database exam. This exam provides you with the fundamentals of IT databases groundwork which will make following exams easier. If, however, you are already in the IT field this exam may be skipped. It is possible to start on the next level though it is up to you to decide if you want to do this. The next level is the Microsoft Solutions Associate (MCSA) exam. This will give you the groundwork to be a DBA. At the time of writing the two paths that are available are for SQL Server 2016 and SQL Server 2012/2014.
When following the SQL 2016 path, which is a good idea as it is best to always stay current in IT, there are three paths which you can take. All of these paths have 2 exams which have to be written to obtain the associated certification
Each of these paths will give you the Microsoft Certified Solutions Associate qualification in the associated field. If you are really motivated, you can do all three.
After successfully completing one of the SQL 2016 MCSA certifications you can move on to the MCSE: Data Management and Analytics certification. To obtain this MCSE certification, you need to pick one of the following exams:
Which exam you choose to write is directly dependent on which path you want to take. For instance, if you decided on becoming a Database Administrator, it would make sense to write exams that relates to DBA work and not on Business Intelligence.
After successfully passing any one of these exams, you will have successfully obtained you MCSE Certification for the associated field.
When following the SQL 2012/2014 path there is basically only one path that you can take to obtain your Microsoft Certified Solutions Associate certification which can be obtained by writing the following three exams
After successfully completing your MCSA certification you can move on to the Microsoft Certified Solutions Expert (MCSE) exams. As with the MCSA certification, there are also multiple paths which you can follow here.
Congratulations, you will now have successfully obtained an MCSE certification in Microsoft SQL Server.
To get certified in Oracle, you can visit the Oracle University website. Pick one of the options under Database Categories section. For the purpose of this example, pick the Oracle Database option. Pick the Oracle Database product which you want to get certified in. All of the certifications which you can obtain for the Oracle product will now be listed. The path which you need to take is perfectly outlined on the page. There should be no confusion as to how, and in which order, you should write the exams.
As you can see there are many paths which you can follow to becoming a database administrator. The path that I would choose, as a Microsoft guy, is as follows:
By following this path, you will successful complete and obtain all MCSE certifications in both the SQL 2016 as well as the SQL 2012/2014 paths. I don’t know if you have noticed but all of the exams required to obtain the SQL 2012/2014 MCSE: Data Platform and SQL 2012/2014 MCSE: Business Intelligence certifications are also listed in the SQL 2016: MCSE Data Management and Analytics certification. This means by following the one path, you can get all of the certifications.
This is the difficult part. Landing a job. I won’t be going into the whole interview process of what to wear, how to answer questions, how to stand etc. For that you can check out the below list of interview articles. Make sure you read these immediately:
Also be sure to check out this post from Brent Ozar: SQL Interview Questions. It’s extremely insightful.
Finding a job as a Database Administrator could prove to be difficult. The area in which you are looking to find a job also has an impact on this. The best place to start looking for a DBA job is online. Go visit your best friend, Google, and start looking. Here are a few of my favourite sights which you can use to start looking for a job as a DBA:
South Africa |
United Kingdom |
United States of America |
| Pnet.co.za | Indeed.co.uk | TheLadders.com |
These are some great places to start looking for a job as a DBA. Keep in mind though, nothing is stopping you from actually applying directly to companies which you would like to work for so brush up that CV of yours and start applying!
A DBA’s salary varies widely depending on where you will be working. I have listed some of the averages for South Africa, the United Kingdom and the United States of America. The averages are also categorized by level of experience.
Keep in mind the following when reading this table:
Experience |
South Africa |
UK |
USA |
Entry Level |
R147,395 | £29,974 | $62,578 |
Mid Career |
R232,943 | £37,492 | $80,445 |
Experienced |
R428,979 | £35,468 | $89,257 |
This graph, along with a little bit of research from your side should give you a clearer understanding about the wages you can expect as a Database Administrator. This will ensure that you can negotiate fairly and that you get a market related salary.
Being a database administrator is great. I personally specialise in Microsoft SQL Server. I just love all the surrounding technologies and the amount of things you can accomplish by knowing what to do with it all. As a DBA your world will always be interesting. Whenever you start a new venture or project, start working at a new firm or in a new industry, things will be different. You will always have something new to learn every day.
Sending e-mail with sp_send_dbmail. Should you or should you not. Well as with everything, it will depend on your requirements on your circumstances. Recently, I have been wondering the exact same thing. In my case it was more in the line of application specific emails.
Our developers are sending e-mail with sp_send_dbmail for various in-application tasks and events. You know, password reset emails, event notification emails, that kind of thing. To me, as a DBA, this always felt like a dirty solution. I took it upon myself to investigate as to whether or not sending e-mail with sp_send_dbmail is a good idea. To my surprise, I could not find any discussions or debates regarding the best practices for sending e-mail with sp_send_dbmail. I spoke to a couple of fellow SQL experts and I finally figured it out. Everyone has there own opinion. This does not really help. Eventually I turned to the good folks of stack exchange. Here, I got some interesting responses as well a some key point which is simply not debatable, and not in the favor of sp_send_dbmail either.
I hear you shouting from behind your screen. “I use sp_send_dbmail to send administration and notification messages from my SQL server!”. This is fine. These types of emails do not take up a lot of space and they will not consume large amounts of resources to send out”. I, however, have never liked sending administration emails using sp_send_dbmail for the following reasons:
Let’s be honest, it’s not always avoidable and I have sp_send_dbmail enabled on some of my servers due to the problem mentioned above. However, when it comes to maintenance, I like to keep my system on a central server which sends instructions to my other servers like backup databases, rebuild indexes, etc. I will collect the data and store it on the central server and send out notifications based on the data that I collected i.e. disk space usage and so forth. Third party applications monitor the performance of the SQL Servers and will send out notifications based on the data it collects. In the end, there is no sending email with sp_send_dbmail and no need to enable SMTP, or sp_send_dbmail for that matter, on a production server.
In the end it will be for you to decide whether it is necessary to enable sp_send_dbmail on your SQL server as it is not always avoidable, even though there are risks involved. Whether there is a right or wrong answer is debatable and as mentioned, every SQL expert has his or her own opinions regarding the matter. When deciding though, keep the above points as to why it could be a bad idea in mind and make an informed decision based on the information at hand.
A few days ago I started applying the techniques which I learned in the Digital Marketing course from Lowercase Academy. This week I will be sorting out my websites meta data on the search engine optimization side of thins. First, here is an overview of what I did last week in the Search Engine Optimization: Getting my website ready post:
After applying the changes, I gave it a couple of days and checked the rankings of my website. The rankings by keyword as they currently stand are as follows:
| Keyword | Rank on Google |
|---|---|
| JJ Prinsloo | 17 |
| SQL Expert | 132 |
Next I will be continuing the optimization of my website by performing the following changes:
On a closer analysis it does not really seem like my websites rankings have changed all that much after the first round of changes which is somewhat expected. The big changes are only being made in the next couple of days although I have no doubt that the changes I already did make will certainly contribute to the effort.
I have heard of, seen, investigated, and debated about so many problems relating to database sizes, disk space, and shrinking SQL Server database files. It always puts a question mark on my face when listening to so many people offering bad advice and/or solutions to these problems.
“Just shrink the file”. Seems to be the general consensus here. DON’T!! I understand the urge. You might be a new DBA and want to show you can solve the problem as soon as possible. The problem is that you may be creating additional problems in the background that you are not aware of.
The problem with shrinking your files is that you are only solving the immediate problem on the surface whilst creating additional problems deeper down. Shrinking your files causes SQL server to move data around within the physical files. This in turn causes extreme index fragmentation. Alright, so we can just rebuild the indexes right? Well in theory yes, and this will sort out the fragmentation issue. The problem now is that your database size has grown again and you are back to square one. This turns into a vicious cycle.
As with everything SQL this will depend on your requirement and SLA. A good practice in any event will be to consider the following options:
A good practice is to plan for your data usage requirements as it will be in the future and not for what your requirement is right now. This will save you a lot of headache. If you size your databases to cater for the amount of data you will have in a years’ time, you’ll be grand.
Work on a proper backup solution from day one. Review the SLA and business requirements and set your database recover models to match the SLA’s. Design your backup solutions around this and if you go with full recovery, take regular transaction log backups which in turn will keep your transaction log file sizes at bay.
Once again, make sure your auto growth setting is set to a reasonable amount of fixed bytes rather than a percentage. Percentage settings on these can quickly become a handful to deal with.
I admit that sometimes you cannot avoid the shrinking of a data file. You may have deleted a huge chunk of data after archiving it and the database is not expected to grow which makes it necessary to shrink the files. After all, that space can be used for the data files of other databases.
If this is the requirement, consider following the following alternative approach:
Make sure your settings on your database is correct, that your backup policies and procedures are in place, and that these agree with your SLA.
Make sure you plan ahead regarding the required storage space for your data.
NEVER shrink your data files, especially not as part of your automatic maintenance procedures (It sounds harsh and extreme but as a DBA, this is how you should think about it)
If it’s not a problem, leave it! As mentioned before, your disk is there to be used and there is no difference between free space on the disk and free space in your data file.
‘Tis December 20th, 2016. The season of joy and all things magical are upon us. On this day I didn’t have much to do for the first time in a long time. I ended up on my sofa, mobile in hand, mindlessly scrolling through Facebook as we all do. I decided to have a look at what was happening in the land of Pi. I went over to The Raspberry Pi Foundations official Facebook page. Let’s be honest, it would be a Christmas miracle if I came across an announcement of a new Raspberry Pi. In the end though, no such luck. Pretty soon however, this year changed to a Raspberry Pi Christmas.
While I was browsing the Facebook page I noticed there were quite a lot of Raspberry Pi related questions and nobody was answering. I took it upon myself to help some of the people out with my experiences relating to their questions. The next day, Alex J’rassic hit me up on Twitter thanking me for my help on their Facebook page and that I should drop her an email. This is what I did. Alex informed the that she and the Foundation would like to send me some goodies to say thank you for my help. “Tis the season of giving after all” she said.
I’m not going to bore you with all the things that happened in between regarding the delivery but, secretly I was really hoping there would be a Raspberry Pi Zero in the package as I have been struggling for more than a year to get one in South Africa. Just have a look at what was delivered to me today:
The goodie bag included:
I would really like to thank Alex Bate and the Raspberry Pi Foundation for this huge gift. You guys are awesome! Really. I will definitely be using these in my projects and I will let you know what I make.
I hope you all had a Merry Christmas! I know I did.
Developing mobile apps which are aesthetically pleasing to the eye can sometimes be difficult. It becomes even more so when you have a case of the infamous creative block syndrome which is never fun to diagnose yourself with. Luckily, we have guys like Unipiazza, the developer of the Material Two Steps Login Android library which makes it a little bit easier with the great work they do.
Today’s featured library is the Material Two Steps Login library developed by Unipiazza. Material Two Steps Login is a beautiful Android library which you can import into your project and wire up to handle the login functionality of your application. The library can easily be styled to fit in with your design and works great. The only downside to the library in its current version is that the styling capabilities are very basic. If you would like to implement more advanced styling such as custom fonts, you will be better off to clone the library and implement it in your project rather than just including the bas URL in your build.gradle.
To use the library, all you need to do is add the dependency to your build.gradle file:
compile 'com.unipiazza.materialtwostepslogin:materialtwostepslogin:0.1.4'
After adding the dependency, create a new activity and add the Material Two Steps Login form in the activity with the following XML:
<com.unipiazza.material2stepslogin.view.MaterialTwoStepsLogin
android:id="@+id/login_view"
android:layout_width="match_parent"
android:layout_height="wrap_content" >
From here you will be able to customize the activity in the activity code as you see fit:
MaterialTwoStepsLogin login_view = (MaterialTwoStepsLogin) findViewById(R.id.login_view);
//REQUIRED
login_view.setListener(this);
login_view.setActivity(this);
login_view.setFirst_step_background_color(getResources().getColor(R.color.colorPrimary));
login_view.setSecond_step_background_color(Color.WHITE);
login_view.setLogo(R.mipmap.ic_launcher);
login_view.setDescription(R.string.insert_email_login);
//OPTIONAL
//TEXTS
login_view.setRegister_description(R.string.not_registered_login);
login_view.setRegister_text(R.string.registrati);
//login_view.setPassforget_description_text();
//login_view.setButton_passforget_text(Color.WHITE);
//login_view.setButton_login_text();
//REGISTER BUTTON background
//login_view.setRegister_background(R.drawable.rounded_white_stroke_button);
login_view.setButton_register_text_color(Color.WHITE);
//EDITTEXT BACKGROUNDS AND COLOR
//login_view.setEdittext_password_background();
//login_view.setEdittext_email_background();
//login_view.setEmail_text_color(Color.BLACK);
//login_view.setEdittext_password_text_color(Color.BLACK);
//BUTTON BACKGROUNDS
//login_view.setButton_login_background();
//login_view.setButton_next_background();
//BUTTON TEXT COLOR
login_view.setButton_next_text_color(Color.WHITE);
login_view.setButton_login_text_color(getResources().getColor(R.color.colorPrimary));
//login_view.setButton_passforget_text_color(Color.WHITE);
//login_view.setPassforget_description_text_color(Color.WHITE);
//TEXTVIEW TEXT COLOR
//login_view.setDescription_text_color(Color.BLACK);
//login_view.setRegister_description_text_color(Color.WHITE);
//login_view.setName_text_color(Color.BLACK);
//login_view.setEmail_secontstep_text_color(Color.BLACK);
login_view.setPassforget_description_text_color(Color.BLACK);
Thanks Unipiazza, You really outshine on this one!
Be sure to check out Unipiazza’s Github page for more awesome libraries.
I’m pretty much just your average guy who loves what he does and is passionate about technology in any form, shape or size. The names provided to me by my elders (those are my parents 🙂 ) is Joachim Prinsloo which led to JJ Prinsloo for short. The second J is a state secret.
I am also a Microsoft Certified Solutions Expert in all platforms of Microsoft SQL Server. I have been working in the Information Engineering industry for the last 10 years. I started as a C# developer and later shifted towards the SQL side of things.
Currently I work as a Senior BI Developer and Integrations Specialist for Metrix Software Solutions (IsoMetrix) and loving it.
I’m passionate and innovative and I always look for ways as to how technology can be used to improve our lives and the way to do things.
In my spare time I like to build and tinker. I constantly spend my free time building projects around Raspberry Pi’s and Arduino’s for home automation and various other applications.
On this website, I scratch all of my thoughts and experiences around the stuff that I do with the hope that it might help someone else. It sometimes even helps me out 🙂
Still trying to get the hang of this Instagram thing…
 |
 |
 |
 |
Stay up to date by receiving my updates…