Getting Started with Contained Databases By Basit A. Farooq Introduction Before SQL Server 2012, when we move or restore databases to a different SQL Server instance, then any logins associated with the database users do not automatically relocates to the new instance. As a result of this, we end up having orphaned users inside databases and these databases do not...
Author: Basit Aalishan Masood-Al-Farooq
How to secure SQL Server infrastructure?
How to secure SQL Server infrastructure? By Basit A. Farooq Introduction Databases are an important reservoir of sensitive information in most organizations, and it is important to keep unauthorized users from accessing this information. Obviously, securing SQL Server and database is not an easy task, and we have to follow a set of best practices in order to accomplish this...
Understanding DBCC CHECKDB Command to Deal with Corrupt Database
Understanding DBCC CHECKDB Command to Deal with Corrupt Database Author: Basit A. Farooq DBCC CHECKDB Overview: Database files are volatile, constantly being changed and updated, so that, like any other volatile files in the system, there is potential for them to become corrupt. Because of this, it is necessary to perform periodic maintenance, to validate the allocation, logical, and structural...
DML Triggers to Track SQL Server Agent Jobs Creation, Deletion and Modification Events (Part 2)
DML Triggers to track SQL Server Agent Jobs creation, deletion and modification events (Part 2) Author: Basit A. Farooq Now that we have created our tables, the next step is to create the four triggers to monitor and log any job modification activities that are occuring on SQL Server instance: 1) AuditDB_AuditNewSQLAgentJobTrigger – will trigger, when the new job will...
DML Triggers to Track SQL Server Agent Jobs Creation, Deletion and Modification Events (Part 1)
DML Triggers to track SQL Server Agent Jobs creation, deletion and modification events (Part 1) Author: Basit A. Farooq Introduction to Problem As we know, SQL Server Agent jobs are crucial to any SQL Server environment, as they are used for scheduling and performing critical business and operational tasks. For example, I’ve got several hundred SQL Server Agent jobs that...
Detecting and Removing Duplicate Indexes
Detecting and Removing Duplicate Indexes Author: Basit A. Farooq Problem Indexes are the solution to many performance problems, but with too many indexes on tables can hurt the performance of your database. This is because SQL Server performs extra work in order keep these indexes up-to-date during insert/update/delete operations against tables where these indexes exist. This means that the SQL...
Introduction to Windows Azure SQL Database (Part 2)
Introduction to Windows Azure SQL Database Services (Part 2) Author: Basit A. Farooq Introduction In my last article "Introduction to Windows Azure SQL Database Services (Part 1)", I’ve provided an overview of Microsoft Windows Azure SQL database, which is a cloud-based relational database platform that is built on SQL Server technologies. We also learned about the steps on how to...
Configuring Database Level Encryption on SQL Server instance
Transparent Data Encryption (TDE) with Database Mirroring Author: Basit A. Farooq Introduction Database security is essential for the organisations working in healthcare and financial sector because the databases in such organisations contain sensitive and confidential information about their customers. The organisation working in such sectors also falls under the boundaries of data regulatory and compliance requirements (such as HIPAA, PCI-DSS,...
Introduction to Windows Azure SQL Database (Part 1)
Introduction to Windows Azure SQL Database Services (Part 1) Windows Azure SQL Database also known as SQL Server Data Services or simply SQL Azure is a Microsoft’s cloud services that offers a transactional and relational data-storage capabilities on Microsoft SQL Server 2012 (such as Amazon S3 and Amazon Relational Database Services), as part of the Azure Services Platform. Windows Azure...
Using a Dedicated Administrator Connection (DAC) on SQL Server
Using a Dedicated Administrator Connection (DAC) on SQL Server Introduction: Today, I received a call from one of our clients complaining they are not able to login to SQL Server after the recent code deployment. To investigate the problem, I tried to connect to SQL Server remotely using SSMS on my laptop, but my connection also hangs and I’m not...