Ahmad Yaseen

Ahmad Yaseen

Force query execution plan using SQL Server 2016 Query store

July 29, 2016 by

SQL Server Query Store is a new feature introduced in SQL Server 2016 that is used to automatically and asynchronously capture query execution history, statistics and plans, with minimal impact to overall SQL Server Performance. The Query Store feature makes performance problem troubleshooting simple; you can view the query execution plans changes and compare its performance to decide which execution plan the SQL Server Query Optimizer should use for that query.

Read more »

The SQL Server 2014 Resource Governor

July 15, 2016 by

SQL Server Resource Governor was introduced in SQL Server 2008. This feature is used to control the consumption of the available resources, by limiting the amount of the CPU, Memory and IOPS used by the incoming sessions, preventing performance issues that are caused by resources high consumption.

Read more »

SQL Server policy-based management

June 27, 2016 by

SQL Server Policy-Based Management was introduced in SQL Server 2008, to make it easy for database administrators to define and enforce SQL Server best practices and company standards in the form of policies. This feature is available in both Enterprise and Standard SQL Server Editions.

Read more »

SQL Server network configuration

June 14, 2016 by

SQL Server Network Configuration involves enabling the protocols that manage the connection to the SQL Server and configuring the available options for these network protocols. It also provides the means to encrypt the communication between the SQL Server instance and the client applications and hide the SQL Server instance from being browsed. SQL Server Network Configuration can be managed using the SQL Server Configuration Manager tool.

Read more »

Querying remote data sources in SQL Server

June 10, 2016 by

A common activity when writing T-SQL queries is connecting to local databases and processing data directly. But there will be situations in which you need to connect to a remote database that is located in a different instance in the same server or in a different physical server, and process its data in parallel with the local data processing.

SQL Server provides us with four useful methods to connect to the remote database servers, even other database server types, and query its data within your T-SQL statement. In this article, we will discuss these four methods and how to use it to query remote SQL Server databases.

Read more »

SQL Server 2014 contained databases

June 1, 2016 by

SQL Server provides two ways to authenticate users; SQL Server Authentication, which requires a predefined username and password to connect to the SQL Server, and Windows Authentication, in which SQL Server trusts the windows integrated user. The server level user that is authenticated to connect to the SQL Server is called a Server Login. This login should be mapped to a database user and granted permissions at the database level in order to access the database and be able to perform the authorized tasks. The relationship between the Server login and the database user in addition to the database metadata stored in the master system database draw up the dependencies between the SQL Server databases the server-level resources.

Read more »

SQL Server 2014 Columnstore index

April 29, 2016 by

By default, SQL Server stores data logically in the tables as rows and columns, which appear in the result grid while retrieving data from any table and physically in the disk in the row-store format inside the data pages. A new data store mechanism introduced in SQL Server 2012, based on xVelocity in-memory technology, in which the data is stored in the column-store data format. This data store mechanism called the Columnstore index.

Read more »

How to copy tables from one database to another in SQL Server

April 29, 2016 by

In some cases, as a DBA, you are requested to copy the schema and the content of specific tables from a database to another one in the same instance or in a different SQL instance, such as copying specific tables from a production database to a DEV one for testing or troubleshooting purposes.

SQL Server offers a lot of methods that can be used to perform table’s data and schema copy process. In order to go through each one of these methods, we will consider the below scenario:

  • The hosting SQL Server: localhost.
  • Both databases hosted in the same SQL Server 2017 instance
  • The source database: AdventureWorks2018
  • The destination database: SQLShackDemo
Read more »

SQL Server indexed views

March 17, 2016 by

SQL Server Views are virtual tables that are used to retrieve a set of data from one or more tables. The view’s data is not stored in the database, but the real retrieval of data is from the source tables. When you call the view, the source table’s definition is substituted in the main query and the execution will be like reading from these tables directly.

Read more »

How to track changes in SQL Server

March 8, 2016 by

As a part of a Big Data project, we are often asked to find the best way to track the changes applied to the database tables, so that, no requirement is created to load all the huge tables to the data warehouse database at the end of the day, if not all of the data was changed.

Read more »

How to configure SQL Server mirroring on a TDE encrypted database

February 19, 2016 by

Securing and encrypting sensitive data stored in your production databases is a big concern, especially the databases storing the organization’s financial data and customers’ confidential information.

SQL Server offers multiple encryption methods in the cell, table and database levels. And in this article, we are interested in a SQL Server database encryption method, introduced in SQL Server 2008, called Transparent Data Encryption (TDE). SQL Server TDE provides encryption on the database file level; it encrypts the database (.MDF), (.LDF), (.NDF), (.BAK), (.DIF), (.TRN) and snapshot files.

The main purpose of this article is showing how we could setup a mirroring site for a database encrypted using SQL Server Transparent Data Encryption. But before starting the demonstration, it is better to introduce TDE first.

Read more »

Monitor the Query timeout expired message from a SQL Server Agent job

January 11, 2016 by

SQL Server provides you with a good solution to automate a lot of your administrative tasks using the SQL Server Agent jobs. These jobs are handled from the operating system side by a Windows service that is responsible for executing these jobs and feeding the SQL Server systems tables with the metadata about these jobs. The system database that is used by the SQL Server Agent for the job management called the msdb database. All information related to the job steps, schedules and the history can be found in the msdb database tables. The msdb system database is also responsible for the SQL Server Mail, Service Broker, SQL Server Maintenance Plans and the databases backup history.

Read more »

Backup SQL Server database to and restoring from multiple files

January 6, 2016 by

Introduction:

The SQL Server backup provides an important solution for protecting critical data that is stored in SQL databases. And in order to minimize the risk of data loss, you need to make sure that you back up your databases regularly taking into consideration the changes applied to your data. It is a best practice to test your backups by restoring random backup files to a test environment and check that the backup files are not corrupted.

Read more »

Best practices after installing Microsoft SQL Server

December 15, 2015 by

Introduction

Working as a DBA, the simplest task requested from you is installing SQL Server. As a start you will check the server’s hardware and software specs to make sure that you can start the installation, checking which services will be installed and the security authentication type required. After this pre-installation plan, you will start the normal installation process.

After completing the installation, you need to apply specific configurations on the server to make sure that the server can host production databases and the users can connect to it successfully.

In this article, I will describe the common configurations that you should apply after the SQL Server installation.

Read more »