Maintenance plans in SQL Server give us an easy way to organize, configure, and schedule tasks that ensure that the database engine and the databases that are hosted therein are kept in shape.
Read MoreAuthor: Kenneth Igiri
Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud.
Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.
Basics of Working with JSON in SQL Server
JSON – A Brief Background
JSON is an acronym for JavaScript Object Notation, that became popular a little over seventeen years ago. JSON is essentially a data format, it was popularized by Douglas Crockford, a well-known programmer with an interesting history who was also involved in the development of JavaScript. JSON has nearly replaced XML as a cross-platform data exchange format. It is reported to be lightweight and easier to manipulate compared to XML. In AWS CloudFormation, templates, which are actually JSON (or YAML) formatted documents, are used to describe AWS resources when automating deployments.
JSON is also used extensively in NoSQL databases such as the increasingly popular MongoDB. Virtually all the Social Media giants expose APIs that are based on JSON. I am sure you begin to get the idea of how widespread its applications have become. JSON was standardized in 2013 and the latest version of the standard (ECMA-404: The JSON Data Interchange Syntax) was released in 2017.
SQL Server introduced support for JSON in SQL Server 2016.
Accelerated Database Recovery in SQL Server 2019
An Overview of Traditional Recovery
As with all relational database systems, SQL Server guarantees the durability of data by implementing crash recovery. Durability in the acronym ACID which refers to the characteristics of transactions in relational databases means that we can be assured that if the database fails suddenly, our data is safe.
SQL Server implements this capability using the transaction log. Changes made by all Data Manipulation Operations in SQL Server are captured in the transaction log before being applied to data files (through the checkpoint process) in case it’s needed to roll back or roll forward.
Using Alerts and Operators in SQL Server
Introduction
With all the advancements in SQL Server and data, it feels great that such native SQL tools as Alerts and Operators are still available. They are the key SQL Server Agent features that are related to anything in SQL Server automation.
Their roles are clear:
- Alerts, when configured, let database administrators know when a specified event has occurred.
- An operator is an object defined within SQL Server that maps anyone or a group that can receive notifications when these events occur.
Deploying a SQL Server Docker Container
Introduction
Containerization has become the dominant topic after the Server Virtualization and Cloud Computing Eras. While many thought leaders assert that current computing paradigms are not particularly new, containerization, as we have it today, can be viewed as having been born after cloud computing. Using containers on-premise allows us to perform migration to the cloud while building a containerized environment mimics the features of solutions deployed in the cloud.
Filtering Data in T-SQL
Introduction
In most scenarios when working with data, users do not require all the data in each instance of a query. T-SQL (and SQL of course) provide means of retrieving only part of a data set in a table or a similar structure. The WHERE, HAVING, TOP and OFFSET-FETCH clauses are used to accomplish this in different ways. Read More
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 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.
Switching Out Table Partitions in SQL Server: A Walkthrough
Introduction
A few years ago we were tasked with a business requirement for card data in a specific format for the purpose of something called “reconciliation”. The idea was to present the data in a table to an application which would consume and process the data which would have a retention period of six months. We had to create a new database for this business need and then create the core table as a partitioned table. The process described here is the process we use to ensure that data older than six months is moved out of the table in a clean fashion. Read More
AlwaysOn Availability Groups: Quorum
SQL Server AlwaysOn Availability Groups is Microsoft’s latest technology for addressing the High Availability and Disaster Recovery needs of organizations using SQL Server. One big advantage of AlwaysOn is the ability to address both HA and DR in one implementation. The key benefits of AlwaysOn that we have experienced are as follows: Read More