Home » SQL Server Architecture

SQL Server Architecture

sql server DBA 999

MS SQL Server is going to be available on Linux platforms

MS SQL Server is going to be available on Linux platforms

Microsoft has announced the plans to bring MS SQL Server to Linux which is going to be major one. Linux and other linux based platforms are widely used in today’s Cloud which is where this is going to fit.

Satya Nadella’s leadership has made plans to make SQL Server a Leader in Database technology and to expand the SQL Server beyond just Windows platforms and this has surprised all IT World.

The Preview version packages are already available for Red Hat Enterprise Linux 7, CentOS 7, Ubuntu Server 16.04 64 bits etc..

There is a minimum system requirement of the preview version for server … Read the rest

sql server DBA 999

SQL Server Memory & Storage Technology

SQL Server- Understanding Server Storage Technology Explained

First we need to understand the different types of memory below

a.Static random access memory (SRAM) comprising L1 and L2 caches for each physical core of a processor,larger but slower shared L3 cache for the entire processor.

b. Dynamic random access memory (DRAM) which also our RAM we install/upgrade in servers.

Both SRAM and DRAM are volatile and hold data only when electrical power is on.

c. SATA/SAS SSD hard drive(NAND)

d. Magnetic spinning media hard disk drives.

Each of Storage/memory above cost lower per MB or GB with higher total capacity as you go from a to d above.

Now New NVM Express PCIe SSD’s (NAND). These … Read the rest

sql server DBA 999

Setup R Services for SQL Server 2016 Step by Step Explained

Setup R Services for SQL Server 2016 Step by Step Tutorial

Microsoft purchased Revolution Analytics (around 2015) and with that they were able to give R Services which is the most popular open source programming language for advanced analytics of data inside of SQL Server bringing R processing closer to the data..

By adding R processing into SQL Server 2016 which is offered as a new feature that supports enterprise level data services.

What is R (Revolution) Services?

Revolution Analytics (formerly Revolution Computing) is a statistical software company focused on developing open source software for enterprise, academic and analytics customers. Revolution Analytics was founded in 2007. In 2009 the company received nine million in venture capital from Intel along with … Read the rest

sql server DBA 999

In Memory Optimized Tables SQL 2014

    In Memory Optimized Tables SQL 2014

What are Memory Optimized Tables in SQL Server?
Memory Optimized tables are newly introduced in SQL Server and they are fully ACID compliant data storage structures, which stores the data mainly within the system memory(RAM), allowing users to access data very very quickly compared to the stored File system stored Table.

The transactional consistency is maintained in Memory Optimized Tables in SQL Server 2014 by:

In-Memory Row Versioning:
Memory Optimized tables maintains various versions of the same row dispersed over the in-memory data structure of the table which allows the reading and writing of the rows concurrently without transactional inconsistency issues. It is equivalent to SNAPSHOT ISOLATION LEVEL where row-versioning handles conflicts by throwing … Read the rest

sql server DBA 999

CTE vs Temp Table vs Temp variables

Temp tables: they are just like any normal tables except that they are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that it calls. Just like normal tables and have primary keys, constraints, indexes and column statistics which are kept for the table which makes query execution really fast in most cases.

Temp tables have space assigned to them in the TempDB database and are generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table Variables: These tables behave very much like other variables in their … Read the rest

sql server DBA 999

Unable to Shrink Temp DB Size in SQL Server 2008

It occurred in production environment when I was Unable to Shrink Temp DB Size SQL Server 2008 eventhough nothing was using it.

To check the free space you can use the query below:

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

and to get idea on files

sp_helpdb 'Tempdb'

When trying to shrink Tempdb it does not let me shrink with command below to 1000MB.

USE [tempdb]
DBCC SHRINKFILE (N'tempdev' , 1000)

I have tried setting intial sizes for tempdb which did not work either,But when we run below query to set the tempdb appropriately with fixed filegrowth then it works

USE [master]
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev',
Read the rest

sql server DBA 999

When do we use SQL Server Database in Restricted User Mode?

Why do we need restricted user SQL server?

When performing some maintenance tasks, particularly in recovery situations, it is essential to limit access to databases by general users by restricting the user access by putting into restricted user sql server or for sql database in single user mode after restore. restricted user sql server access mode can be employed to limit connections to high-level users only.

How do we put restricted user SQL server?

In order to put SQL server restricted user mode we need to use the alter database set restricted_user below. This command can help with sql database in single user mode after restore.

The ALTER DATABASE command allows a database administrator to modify SQL … Read the rest

sql server DBA 999

Protecting your database using Transparent Data Encryption in SQL Server 2008

Encryption is the process of transforming information in plain text using a cipher, or algorithm, to make it unreadable to everyone other than the person who has the key. There are two types of keys; symmetric and asymmetric. When the same value is used to encrypt and decrypt, then it is known as a symmetric key. An asymmetric key has two parts: one is a private key and the other is a public key. The private key is used to encrypt the data and the public key is used to decrypt the data.

TDE: is a full database level encryption that protects the data files and log files.
As per Microsoft documentation for TDE

“Transparent data encryption (TDE) performs
Read the rest

sql server DBA 999

Why Copy-Only Backups taken in SQL Server?

A copy-only backup in SQL Server(any version like 2012 or 2008 or 2005) is taken in cases you do not want effect the sequence of conventional SQL Server backups.
Usually in using a normal Backup,the backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. copy-only backups serve this purpose.

Note: The transaction log is never truncated after a copy-only backup.

The types of copy-only backups are as follows:

Copy-only full backups (all recovery models)

Copy-only log backups (full recovery model and bulk-logged recovery model only)

Note:A copy-only full backup cannot serve as a … Read the rest

sql server DBA 999

SQL Server 2005/2008 Lock Escalations

Please read my previous post on SQL Server Locks and concurrency control before this….
Lock Escalations are there for a reason. For every lock taking place on SQL Server it takes up around 100 bytes approximately in memory in RAM. So, if we are holding 500,000 locks then it would be 47.68372 MB of memory used. This includes all types of locks.

Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.
As the SQL Server Database Engine acquires low-level locks, it also places intent locks on the objects that contain the lower-level objects:

•When locking rows or index key ranges, the Database Engine places an … Read the rest