When working as a SQL Server database administrator, you should have the skills to dive deeply into the ocean of the SQL Server Engine and find what is happening internally, in order to be able to detect, troubleshoot and fix any problem that you may face.
You can take benefits from the logs that are written by the SQL Server engine locally in the form of SQL Server logs or externally in the form of event logs, but you cannot consider them as the only source for your troubleshooting and tuning operations.
Collation in SQL Server
Introduction
You must have already heard the term “Collation” in SQL Server. Collation is a configuration that determines how character data sorting is done. This is an important setting that has a huge impact on how the SQL Server database engine behaves in dealing with character data. In this article, we aim to discuss collations in general and show a few examples of dealing with collations.
Using Transaction ROLLBACK in SQL Server
Introduction
Very recently, a colleague of mine came to me in desperation owning up that he had issued an update statement without a WHERE clause on a key application table. The implications on the front end would be dire, so he came to me directly because he urgently needed help with reversing the situation by any means before the emails and escalation started pouring in.
When we looked into the situation, we found that the changes have not been applied in the secondary database. In most cases, the lag between our primary and secondary databases is twenty minutes (we have a little staggering to avoid performance problems). Because my colleague asked for help immediately after realizing the error, we were able to recover the data from the secondary database. I described the value of such a delay in this article. Read More
SQL Server High availability: Add new disk to an existing failover cluster instance
In my previous article, I have explained the step-by-step process of installing a node in existing SQL Server Failover Cluster Instance. Along with that, I have also demonstrated manual failover and automatic failover.
In this article, I am going to demonstrate the process of adding a disk in a failover cluster and then move the existing database to a new drive. Read More
Automated testing of the desktop application: expediency and frameworks overview
Introduction
You have certainly heard about regression and acceptance testing. But do you know how much is actually spent on acceptance testing in a project?
We can quickly get an answer to this with the help of a time tracking system like TMetric.
On our project, acceptance-testing a desktop application of around 100 assemblies took more than 2 person-weeks. New QA specialists who didn’t know the application well were having the greatest difficulties. Compared to more experienced QA specialists, they spent much more time on each test case.
However, in my opinion, the most unpleasant part was this – if any critical errors are found before the release, acceptance testing must be performed again after these errors are fixed.
Written unit tests helped a little bit, but they still mostly reduced the time spent on regression testing. With this, when the amount of manual testing reached a critical level, we started moving towards automation. Read More
Dynamic Data Masking in SQL Server for beginners
The article introduces the basics of dynamic data masking (DDM) in SQL Server along with its overview supported by a simple example of data masking implementation. Additionally, the readers are going to get familiar with the benefits of dynamic data masking. This paper also highlights the importance of data masking in day to day database development tasks when some fields must be masked due to their sensitive nature in order to comply with standard practices.
Read More
SQL Always On Availability Groups: Computer Objects
SQL Server Always On Availability Groups is Microsoft’s latest technology for addressing the High Availability and Disaster Recovery needs of organizations that use SQL Server. One big advantage of AlwaysOn is the ability to address both HA and DR in one implementation. We experienced the following key benefits of AlwaysOn:
-
We can group related databases as part of a single Availability Group and have them failover together in case this is needed. This is especially useful for applications that depend on more than one database, such as Microsoft Office SharePoint, Microsoft Lync, and Sage.
-
When compared to SQL Server Failover Cluster Instances, we find that storage as a single point of failure has been eliminated since each instance which constitutes a replica is assigned its own storage.
-
With AlwaysOn, it is possible to configure HA and DR at once. This is achieved by creating a Multi-site Windows Failover Clusters as the foundations of your AlwaysOn configuration. Performing a Role Switch when using AlwaysOn is significantly simpler than doing it when using Transaction Log Shipping.
Analytic Functions in SQL Server
Aggregate and Analytic functions in SQL Server operate on a set of rows. However, unlike such aggregate functions as sum, count and average that return scalar values, analytic functions return a group of rows that can be further analyzed. In this article, we will see some of the most commonly used analytic functions in SQL server. We will be discussing the following functions:
- CUME_DIST
- FIRST_VALUE
- LAST_VALUE
- LEAD
- LAG
Ranking Functions in SQL Server
Suppose you are designing an SQL Server database application for a company’s CEO and you have to display the fifth most highly paid employee in the company.
What would you do? Read More
Applying SQL Transformations and Handling Missing Values in Azure ML
In this article, we will introduce SQL transformations in action. We will also see how to handle missing values in our dataset.
Consider a scenario of a movie rating dataset containing records of different movies along with the average user ratings associated with each movie. The ratings are in numeric form ranging from 1 to 10 with 1 as the lowest rating and, respectively, 10 as the highest rating (though no movie in the history has achieved 10 rating:). Suppose that we want to convert the numeric ratings into categorical ratings. For instance, we want to replace ratings of 1-3 with the categorical value “poor”, of 4-6 with “average” while ratings of 7-10 will have the value “good”. We can accomplish it with SQL transformation in Azure ML Studio. Read More