Contained Database

 

First introduced in SQL 2012 and its supported now.

What it is

                Contained databases have no dependencies on server-level metadata and settings. User can access a Contained Database without authenticated a login at DB instance level. This helps to isolate the database from database engine and make it possible to easily move the DB from one SQL instance to another. In simple word, user get authenticated at DB level and DB can easily portable to other server without mapping login with DB.

Step by step implementation

Step1. Enable 'contained database authentication' on the DB instance.

exec sp_configure 'contained database authentication',1

reconfigure with override

Step2. Enable containment=partial on DB.

create database <DB_Name> containment=partial

If DB already exists.

alter database <DB_Name> set containment=partial

Step3. Create DB user with password.

use <DB_Name>

create user <user_name> with password =N'*******', default_schema=your_schema_name

Step4. Grant permission. I’m giving select permission here.

use <DB_Name>

GRANT SELECT ON <table_name> TO <user_name> WITH GRANT OPTION  AS your_schema_name

 

In my case...

Connecting DB with the user using SSMS

Use of Contained DB

·         Authentication

·         Use with AG

·         In Dev environment

·         Make a day for DBA moving here and there.

Problem with Contained DB

·         Replication is not supported

·         Change data capture does not work

·         Change Tracking does not work out

·         Connection string change

·         Cross DB query does work

·         Difficult to manage if the server is having password policy enabled

SQL Server Architecture Diagram

Hey guys, In this blog I am going to explain you about SQL Server Architecture Diagram 

In almost every SQL DBA interview, this is the question which thrown as a hard ball from interviewer.

Below is high level SQL Server Architecture Diagram which is hard to explain in telephonic interview as well as face to face some time.

So I have divided this in three broader category which can be explain easily...

1. Protocol Layer
2. Storage Layer
3. OS Layer 

1.  Protocol Layer

 

A.       Connection Protocol –                                                                                                 

Connection Protocols are of three types –

Shared Memory - MS SQL server provides SHARED MEMORY PROTOCOL. Here CLIENT and MS SQL server run on the same machine. Both can communicate via Shared Memory protocol.

TCP/IP – MS SQL SERVER provides the capability to interact via TCP/IP protocol, where CLIENT and MS SQL Server are remote to each other and installed on a separate machine.

Named Piped – MS SQL SERVER provides the capability to interact via the Named Pipe protocol. Here the CLIENT and MS SQL SERVER are in connection via LAN.

A.       Query Processing – 

Query Processing calls Access Method. It provides an execution plan for data fetching logic required for execution. Once data is received from Storage Engine, the result gets published to the Protocol layer. Finally, data is sent to the end user.

2.   Storage Layer

A.       Lock Manager –

During Transaction, the associated data in Data Storage is in the Lock state. This 

process is handled by Lock Manager. This process ensures data consistency 

and isolation. 

B.       Transaction Manager – 

Transaction Manager is invoked when access method determines that Query is a non-Select statement.

C.       File Manager – 

Every database has one primary data file. We can give any extension for the primary data file but the recommended extension is .mdf.

Secondary data file is a file other than the primary data file in that database. Some databases may have multiple secondary data files. Some databases may not have a single secondary data file. Recommended extension for secondary data file is .ndf.

Log files hold all of the log information used to recover the database. Database must have at least one log file. We can have multiple log files for one database. The recommended extension for log file is .ldf.

D.       Buffer Manager – 

Buffer Manager looks for Data in Buffer in Data cache. If present, then this Data is used by Query Executor. This improves the performance as the number of I/O operation is reduced when fetching data from the cache as compared to fetching data from Data storage.

3. OS Layer

A.       Scheduler Manager –

A scheduler manager worker threads that require processing time to carry out work on behalf of tasks. Each scheduler is mapped to an individual processor (CPU). The time worker can remain active in a scheduler is called the OS quantum, with a maximum of 4 ms. After its quantum time expires, a worker yields it’s time to other workers that need to access CPU resources, and changes its state. 

B.   Memory Manager –

SQL Server dynamically acquires and frees memory as required. Typically, an administrator does not have to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments.

C.       Lazy Writer –

The Dirty page can remain in memory. When SQL server observes a huge load and Buffer memory is needed for a new transaction, it frees up Dirty Pages from the cache. 

D.       Deadlock –

A SQL Server Deadlock is a special concurrency problem in which two transactions block the progress of each other. The first transaction has a lock on some database object that the other transaction wants to access, and vice versa.

A.       I/O Manager –

Disk I/O operations by the buffer manager have the following characteristics:

·       All I/O are performed asynchronously, which allows the calling thread to continue processing while the I/O operation takes place in the background.

·       All I/O are issued in the calling threads unless the affinity I/O option is in use. The affinity I/O mask option binds SQL Server disk I/O to a specified subset of CPUs.

·       Multiple page I/O are accomplished with scatter-gather I/O, which allows data to be transferred into or out of noncontiguous areas of memory. 

SQL Server VSS Writer Service

 Microsoft built the capability into windows allowing files to be backed up to disk while other programs are using those files.

 The VSS Servicer interacts with the Windows Volume Shadow Copy service (VSS), allowing files that are in use, such as SQL Server data files, to be copied (or backed up) without stopping all other services accessing those files. If your server needs to backup your databases without taking the database offline, or using the SQL Server Backup Stream, then turn on the SQL Server VSS Writer service on your computer.

 

By default, this service is installed for all instances of SQL Server.

If your backup software is simply copying files from your disk to your backup device, be sure to turn this service on. Otherwise, set it to manual start-up.

 

 

 

 

SQL Agent Jobs running multiple times out of schedule

     This was interesting issue which I faced in one of my production environment. Customer complained their all SSIS jobs are running multiple times at a schedule. But in job history, it was showing all well.

We found, all the scheduled Backup maintenance jobs also triggering two times at a schedule.

We tried below options...

1.       Dropped old job schedule and created new with same details.

Result: Issue not fixed.

 2.       Scripted out the jobs and drop and recreate it.

Result: The same, issue not fixed.

Now the situation was interesting, poor app DBA were deleting duplicate data from back-end and their frustration level was getting high day by day.

 

Finally we found there was a VM clone of the server exists in the network with different name which was requested by customer. Haha ha.

Their jobs which are having SSIS packages connecting to the live production box.