Every backup operation in SQL Server is written to the SQL Server Error log. This includes Transaction Log Backups even when they occur as part of a Transaction Log Shipping Configuration. Sometimes logging the entire Log Backup can be a nuisance in the SQL Server Error Log and needs to be managed. Trace Flag 3226 is used to suppress such logging and we will demonstrate how this can be done in this article.
Assuming you maintain a table containing customer data, and your boss asks you to send him the current list of customers and their phone numbers. You would typically extract the data and send him a spreadsheet with rows and columns. You could also decide to be a bit stylish and send him the required information in a more people-friendly format. SQL Server provides functions that allow us to achieve this by combining English expressions with data within tables to present an output that is easier for non-technical people to read. These functions can also be put to more subtle uses.
Organizations are becoming more and more concerned about how to reduce the cost of licensing database solutions using consolidation. Some consolidation can be achieved in SQL Server simply by taking advantage of the existing one-to-many relationship between instances and databases. However, there are cases where the solution demands that data is consolidated into one table. In such a case, there may be concerns about how to restrict access to the data.
Row Level Security was introduced in SQL Server 2016 as a solution to scenarios similar to the above. It allows you to restrict access to rows in a table based on conditions defined in an inline Table Valued Function called a Predicate Function. When a Predicate Function is applied to a user table containing consolidated data, the system can be configured to return different data sets to different users depending on their roles which in turn depends on their job descriptions or departments for example.
What is NULL? NULL or the NULL marker is the way we represent an unknown value in SQL, by SQL I am referring to the standard Structured Query Language, not MS SQL Server. The last sentence takes us a little back to 1969 when the standard was first defined by Dr. E.F. Codd. NULL becomes necessary because we need to think in terms of what is called three-valued predicate logic. A predicate is the property of an expression that either holds or does not hold. At face value, one would think of two possibilities: TRUE or FALSE. However, there is a third possibility: UNKNOWN. Read More
It is common knowledge in database circles that indexes improve query performance either by satisfying the required result set entirely (Covering Indexes) or acting as lookups which easily direct the Query Engine to the exact location of the required data set. However, as experienced DBAs know, one should not be too enthusiastic about creating indexes in OLTP environments without understanding the nature of the workload. Using Query Store in SQL Server 2019 instance (Query Store was introduced in SQL Server 2016), it is quite easy to show the effect of an index on inserts. Read More
One of the key skills you need as a database administrator or an IT person is generally the ability to monitor systems very carefully. Lack of this key skill can lead to misdiagnosis when troubleshooting issues. SQL Server exposes a number of tools that can aid the DBA in troubleshooting problems that occur in production. The SQL Server Error Log and the SQL Server Agent Log are two of the most important facilities for troubleshooting SQL Server. In this article, we shall explore the ways we can manipulate the Server and Agent logs.
In the last two or three months, I have been asked twice for a solution native to SQL Server that consolidates a backup report for several SQL Server instances across an enterprise. This question came from friends that did not necessarily want to spend money buying a tool but were more inclined to leverage the capabilities of SQL Server. I have thought about two possible ways to achieve this:
In this article, I will demonstrate the first and hope we shall have a second part of the article sometime later. Read More
In the first part of the two-part series, we explored migrating databases by first updating the master database system catalogs which contain records of the physical location of data files. In the current article, we shall look at two other methods of migrating databases in SQL Server which essentially have the same effect through the approach is different. Read More
There are a number of situations which would warrant the movement of database files or transaction log files from one volume to another on the same server. These may include:
Transaction Log Shipping is a very well-known technology used in SQL Server to maintain a copy of the live database in the Disaster Recovery Site. The technology depends on three key jobs: the Backup Job, the Copy Job, and the Restore Job. While the Backup job runs on the Primary Server, the Copy and Restore jobs run on the Secondary Server. Essentially the process involves periodic transaction log backups to a share from which the Copy Job moves same to the Secondary Server; subsequently, the Restore Job applies the log backups to the secondary server. Before all this starts, the Secondary Database must be initialized with a full backup from the Primary server restored with NORECOVERY option.