SQL SERVER – Practical Tips to Reduce SQL Server Database Table Size – Experts Opinion

I am one of those fortunate people who has friends everywhere in the world. Syrovatchenko Sergey (LinkedIn) is one of my good friends who is an expert on SQL Server and works at Devart. When we met a few days ago in one of the event, we end up discussing about practical tips to reduce database table size. Upon returning home, I found Sergey has sent me an amazing article on this subject. It is beautifully written article and I enjoyed it very much. Let us read what Sergey has to say about this subject in his own words.

In this article, I’d like to elaborate on the subject of boosting productivity while working with database tables. Chances are you already know this from multiple resources on database development process.

experts1 SQL SERVER   Practical Tips to Reduce SQL Server Database Table Size   Experts Opinion

However, the topic seems to become a front-burner issue when there are continuous data growths — tables become too large that leads to the performance loss.

It happens due to an ill-designed database schema that was not originally designed for handling large volumes of data.

To avoid the performance loss in the context of continuous data growth, you should stick to certain rules when designing a database schema.

Rule # 1 — Minimum Redundancy of Data Types

The fundamental unit of SQL Server data storage is the page. The disk space intended for a data file in a database is logically divided into pages numbered contiguously from 0 to n. In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte.

Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages. The more compact data types is used, the less pages for storing that data are required, and as a result, less I/O operations needed.

Introduced in SQL Server, buffer pool significantly improves I/O throughput. The primary purpose of the SQL buffer pool is to reduce database file I/O and improve the response time for data retrieval.

Thus, when compact data types are used, the buffer pool stores larger amount of data on the same amount of pages. As a result, you will not waste the memory and reduce a number of logical operations.

Consider the following example — a table that stores working days of employees.

CREATE TABLE dbo.WorkOut1 (
, EmployeeID BIGINT
, WorkShiftCD NVARCHAR(10)
WorkHours DECIMAL(24,2)

Are the selected data types correct? The most probable answer is no. It is unlikely that an enterprise has (2^63-1) employees. Therefore, the BIGINT is an unsuitable data type in this case.

We can remove this redundancy and estimate the query execution time.

CREATE TABLE dbo.WorkOut2 (
, EmployeeID INT
, WorkShiftCD VARCHAR(10)
WorkHours DECIMAL(8,2)

The following execution plan demonstrates the cost difference which depends on a row size and expected number of rows.

experts2 SQL SERVER   Practical Tips to Reduce SQL Server Database Table Size   Experts Opinion

The less data you need to retrieve, the faster query will run.

(3492294 row(s) affected)
SQL Server Execution Times:
CPU time = 1919 ms, elapsed time = 33606 ms.
(3492294 row(s) affected)
SQL Server Execution Times:
CPU time = 1420 ms, elapsed time = 29694 ms.

As you can see, the usage of non-redundant data types is a keystone for the best query performance. It also allows reducing the size of problem tables. By the way, you can execute the following query for measuring a table size:

table_name = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
, data_size_mb = CAST(do.pages * 8. / 1024 AS DECIMAL(8,4))
FROM sys.objects o
, total_rows = SUM(p.[rows])
total_pages = SUM(a.total_pages)
usedpages = SUM(a.used_pages)
pages = SUM(
WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0
WHEN a.[type]! = 1 AND p.index_id < 2 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages ELSE 0
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id]
GROUP BY p.[object_id]
) do ON o.[object_id] = do.[object_id]
WHERE o.[type] = 'U'

For the above-considered tables, the query returns the following results:

table_name           data_size_mb
——————– ——————————-
dbo.WorkOut1         167.2578
dbo.WorkOut2         97.1250

Rule # 2 — Use Database Normalization and Avoid Data Duplication

Recently, I have analyzed a database of a free web service that allows formatting T-SQL code. The server part is quite simple over there and consists of a single table:

CREATE TABLE dbo.format_history (
session_id BIGINT
, format_date DATETIME
, format_options XML

Every time when formatting SQL code, the following parameters were saved to the database: current session ID, server time, and the settings that were applied while formatting user’s SQL code.

This data subsequently were used for determining of most popular formatting styles. There were plans to add these styles to SQL Complete default formatting styles.

However, the service popularity rise led to a significant table rows increase, and profiles processing became slow. The settings had the following XML structure:

&lt;PropertyValue Name="Select_SelectList_IndentColumnList"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="Select_SelectList_SingleLineColumns"&gt;false&lt;/PropertyValue&gt;
&lt;PropertyValue Name="Select_SelectList_StackColumns"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="Select_SelectList_StackColumnsMode"&gt;1&lt;/PropertyValue&gt;
&lt;PropertyValue Name="Select_Into_LineBreakBeforeInto"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="UnionExceptIntersect_LineBreakBeforeUnion"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="UnionExceptIntersect_LineBreakAfterUnion"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="UnionExceptIntersect_IndentKeyword"&gt;true&lt;/PropertyValue&gt;
&lt;PropertyValue Name="UnionExceptIntersect_IndentSubquery"&gt;false&lt;/PropertyValue&gt;

450 formatting options in total. Each row takes 33 KB in the table. The daily data growth exceeds 100 MB. As an obvius outcome, the database has been expanding day by day, thus making data analysis yet more complicated .

Surprisingly, the salvation turned out to be quite easy: all unique profiles were placed into a separate table, where a hash was defined for every set of options. As of SQL Server 2008, you can use the sys.fn_repl_hash_binary function for this.

So the DB schema has been normalized:

CREATE TABLE dbo.format_profile (
format_hash BINARY(16) PRIMARY KEY
, format_profile XML NOT NULL
CREATE TABLE dbo.format_history (
session_id BIGINT
, format_date SMALLDATETIME
, format_hash BINARY(16) NOT NULL
CONSTRAINT PK_format_history PRIMARY KEY CLUSTERED (session_id, format_date)

And if the previous query looked like this:

SELECT fh.session_id, fh.format_date, fh.format_options
FROM SQLF.dbo.format_history fh

The new schema required the JOIN usage to retrieve the same data:

SELECT fh.session_id, fh.format_date, fp.format_profile
FROM SQLF_v2.dbo.format_history fh
JOIN SQLF_v2.dbo.format_profile fp ON fh.format_hash = fp.format_hash

If we compare the execution time for two queries, we can hardly see the advantages of the schema changes.

(3090 row(s) affected)
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 4698 ms.
(3090 row(s) affected)
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 4479 ms.

But in this case, the goal was to decrease time for analysis. Before we had to write an intricate query for getting the list of popular formatting profiles:

;WITH cte AS (
, hsh = sys.fn_repl_hash_binary(CAST(fh.format_options AS VARBINARY(MAX)))
FROM SQLF.dbo.format_history fh
SELECT c2.format_options, c1.cnt
SELECT TOP (10) hsh, rn = MIN(rn), cnt = COUNT(1)
FROM cte
) c1
JOIN cte c2 ON c1.rn = c2.rn

Now due to the data normalization, we managed to simplify the query:

, t.cnt
, cnt = COUNT(1)
FROM SQLF_v2.dbo.format_history fh
GROUP BY fh.format_hash
) t
JOIN SQLF_v2.dbo.format_profile fp ON t.format_hash = fp.format_hash

As well as to decrease the query execution time:

(10 row(s) affected)
SQL Server Execution Times:
CPU time = 2684 ms, elapsed time = 2774 ms.
(10 row(s) affected)
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 379 ms.

In addition, the database size has decreased:

database_name    row_size_mb
—————- —————
SQLF             123.50
SQLF_v2          7.88

To retrieve a file size, the following query can be used:

database_name = DB_NAME(database_id)
row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files
WHERE database_id IN (DB_ID('SQLF'), DB_ID('SQLF_v2'))
GROUP BY database_id

Hope I managed to demonstrate the importance of data normalization.

Rule # 3 — Be careful while selecting indexed columns.

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from a table or a view. Indexes are stored on pages, thus, the less pages is required to store indexes, the faster search process is. It is extremely important to be careful while selecting clustered indexed columns, because all the clustered index columns are included in every non-clustered index. Due to this fact, a database size can increase dramatically.

Rule # 4 — Use Consolidated Tables.

You do not need to execute a complex query on a large table. Instead, you can execute a simple query on a small table.

For instance, we have the following consolidation query

FROM dbo.WorkOutFactor
WHERE Value > 0

If there is no need to often change the table data, we can create a separate table

FROM dbo.WorkOutFactorCache

The data retrieval from such consolidated table will be much faster:

(185916 row(s) affected)
SQL Server Execution Times:
CPU time = 3448 ms, elapsed time = 3116 ms.
(185916 row(s) affected)
SQL Server Execution Times:
CPU time = 1410 ms, elapsed time = 1202 ms.

Rule # 5 — Every rule has an exception

I’ve shown a couple of examples that demonstrated how to eliminate redundant data types and shorten queries execution time. But it does not always happen.

For instance, the BIT data type has a peculiarity —  SQL Server optimizes the storage of such columns group on a disk. If a table contains 8 (or less) columns of the BIT type, they are stored in the page as 1 byte. And if the table contains 16 columns of the BIT type, they are stored in the page as 2 bytes etc. The good news is that the table will take up little space and reduce disc I/O.

The bad news is that an implicit decoding will take place while retrieving data,  and the process is very demanding in terms of CPU resources.

Here is the example. Assume we have 3 identical tables containing information about employees work schedule (31 + 2 PK columns).  The only difference between tables is the data type for consolidated values (1– presence, 2 – absence)


When using less redundant data types, the table size decreases considerably (especially the last table)

table_name           data_size_mb
——————– ————–
dbo.E31_INT          150.2578
dbo.E32_TINYINT      50.4141
dbo.E33_BIT          24.1953

However, there is no significant speed gain from using the BIT type

(1000000 row(s) affected)
Table ‘E31_INT’. Scan count 1, logical reads 19296, physical reads 1, read-ahead reads 19260, …
SQL Server Execution Times:
CPU time = 1607 ms,  elapsed time = 19962 ms.
(1000000 row(s) affected)
Table ‘E32_TINYINT’. Scan count 1, logical reads 6471, physical reads 1, read-ahead reads 6477, …
SQL Server Execution Times:
CPU time = 1029 ms,  elapsed time = 16533 ms.
(1000000 row(s) affected)
Table ‘E33_BIT’. Scan count 1, logical reads 3109, physical reads 1, read-ahead reads 3096, …
SQL Server Execution Times:
CPU time = 1820 ms,  elapsed time = 17121 ms.

But the execution plan will show the opposite.

experts3 SQL SERVER   Practical Tips to Reduce SQL Server Database Table Size   Experts Opinion

So the negative effect from the decoding will not appear if a table contains less than 8 BIT columns. One must note that the BIT data type is hardly used in SQL Server metadata. More often the BINARY data type is used, however it requires manual manipulations for obtaining  specific values.

Rule # 6 — Delete data that no longer required.

SQL Server supports a performance optimization mechanism called read-ahead. This mechanizm anticipates the data and index pages needed to fulfill a query execution plan and brings pages into the buffer cache before they are actually used by the query.

So if the table contains a lot of needless data, it may lead to unnecessary disk I/O. Besides, getting rid of needless data allows you to reduce the number of logical operations while reading from the Buffer Pool.

In conclusion, my advice is to be extremely careful while selecting data types for columns and try predicting future data loads.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Looking Inside SQL Complete – Advantages of Intellisense Features

Recently SQL Complete add-in was updated, which I use when writing SQL queries in SSMS. In the product history I saw that a several features were added, such as code refactoring or automatic semicolon insertion. However, in this post I do not want to talk about them. I have noticed a new logic for metadata queries, which significantly accelerated my work with database objects. So I decided to look inside SQL Complete… to understand how it works and what changed…

We can use the free dbForge Event Profiler for SQL Server tool to see what queries SQL Complete sends. We can apply filter to eliminate custom queries from the tracing.

sqlcomp 1 SQL SERVER   Looking Inside SQL Complete   Advantages of Intellisense Features

When we first open an SQL document in SSMS, we get the following trace.

sqlcomp 2 SQL SERVER   Looking Inside SQL Complete   Advantages of Intellisense Features

I wrote in comments what each of these queries return.

-- 1. instance version



-- 2. default schema (dbo by default)





-- 3. don’t know why SQL Complete needs a default backup path



SELECT @dir=CAST(SERVERPROPERTY('instancedefaultdatapath') AS NVARCHAR(512))


EXEC [master].dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 'BackupDirectory', @dir OUTPUT, 'no_output'



-- 4. available databases



, name

, CASE WHEN [dbid] >= 1 AND [dbid] <= 4 THEN 1 ELSE 0 END AS is_fixed_database

, CASE WHEN [status] & 32768 != 0 THEN 0 ELSE CONVERT(BIT, [status] & (32 | 64 | 128 | 256 | 512)) END AS is_unavailable

FROM [master].dbo.sysdatabases WITH(NOLOCK)

ORDER BY name;


-- 5. system languages

SELECT alias

FROM [master].sys.syslanguages WITH(NOLOCK)

ORDER BY alias;


-- 6. system collations (SQL_Latin1_General_CP1_CI_AI, ....)


FROM ::fn_helpcollations()

ORDER BY name;


-- 7. linked servers

SELECT srvname AS name

FROM [master].dbo.sysservers

WHERE srvid != 0

ORDER BY srvid;


-- 8. server triggers



, t.[object_id]

, CASE t.[type] WHEN 'TR' THEN 1 ELSE 0 END AS is_sql_trigger

, CASE WHEN ssm.[definition] IS NULL THEN 1 ELSE 0 END AS is_encrypted

FROM [master].sys.server_triggers t WITH(NOLOCK)

LEFT JOIN [master].sys.server_sql_modules ssm WITH(NOLOCK) ON ssm.[object_id] = t.[object_id]

WHERE is_ms_shipped = 0;


-- 9. system objects (like sys.views, sys. ...)


o.[object_id] AS id

, o.name

, o.[type]

, o.[schema_id]

FROM sys.system_objects o WITH(NOLOCK)

WHERE o.[type] in ('U', 'V', 'IF', 'TF', 'FT', 'FN', 'AF', 'FS', 'P', 'RF', 'PC', 'X')

ORDER BY o.[object_id];


-- 10. extended properties for current database

SELECT value

FROM sys.extended_properties WITH(NOLOCK)

WHERE class = 0 AND name = 'MS_Description';


-- 11. available schemas



, s.name

, CASE WHEN s.[schema_id] BETWEEN 16384 AND 16399 OR s.name IN ('guest', 'INFORMATION_SCHEMA', 'sys')



END AS is_fixed_role

, CASE WHEN ISNULL(NULLIF(SCHEMA_NAME(), 'guest'), 'dbo') = s.name



END AS is_default_schema

, ep.value AS [description]

FROM sys.schemas s WITH(NOLOCK)


SELECT value, major_id

FROM sys.extended_properties WITH(NOLOCK)

WHERE class = 3 AND name = 'MS_Description'

) ep ON s.[schema_id] = ep.major_id;


-- 12. user objects (tables, views, procedures, triggers, etc)


o.[object_id] AS id

, o.name

, o.[type]

, o.[schema_id]

, o.is_ms_shipped AS is_system

, COALESCE(OBJECTPROPERTY(o.[object_id], 'IsEncrypted'), 0) AS is_encrypted

, ep.value AS [description]

FROM sys.objects o WITH(NOLOCK)


SELECT value, major_id, minor_id

FROM sys.extended_properties WITH(NOLOCK)

WHERE class = 1 AND name = 'MS_Description'

) ep ON o.[object_id] = ep.major_id AND ep.minor_id = 0

WHERE o.[type] in ('U', 'V', 'IF', 'TF', 'FT', 'FN', 'AF', 'FS', 'P', 'RF', 'PC', 'X')

ORDER BY o.[object_id];


-- 13. foreign keys between tables



, fk.[object_id]

, fk.name

, o.name AS referenced_table_name

, SCHEMA_NAME(o.[schema_id]) AS referenced_table_schema

FROM sys.foreign_keys fk WITH(NOLOCK)

JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = fk.referenced_object_id

WHERE o.[type] = 'U'



, fk.[object_id];


-- 14. columns for foreign keys


fc.parent_object_id as owner_object_id

, fc.constraint_object_id AS constraint_id

, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS column_name

, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name

FROM sys.foreign_key_columns AS fc WITH (NOLOCK)



, fc.constraint_object_id

, fc.constraint_column_id;


-- 15. user types



, t.[schema_id]

, t.is_assembly_type AS is_clr

, st.name AS base_type

FROM sys.types t WITH(NOLOCK)

LEFT JOIN sys.types st WITH(NOLOCK) ON st.is_user_defined = 0

AND st.is_assembly_type = 0

AND st.is_table_type = 0

AND st.user_type_id = st.system_type_id

AND st.system_type_id = t.system_type_id

WHERE t.is_user_defined = 1 AND t.is_table_type = 0

ORDER BY t.user_type_id;


-- 16. table types


tt.type_table_object_id AS id

, t.name

, t.[schema_id]

FROM sys.types t WITH(NOLOCK)

LEFT JOIN sys.table_types tt WITH(NOLOCK) ON t.user_type_id = tt.user_type_id

WHERE t.is_user_defined = 1

AND t.is_table_type = 1



-- 17. database triggers



, t.[object_id]

, o.[schema_id]

, CASE t.type WHEN 'TR' THEN 1 ELSE 0 END as is_sql_trigger

, COALESCE(OBJECTPROPERTY(t.[object_id], 'IsEncrypted'), 0) AS is_encrypted

, o.name AS parent_name

FROM sys.triggers t WITH(NOLOCK)

LEFT JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = t.parent_id

WHERE t.is_ms_shipped = 0 AND t.parent_class = 1



, t.[object_id];


-- 18. synonyms



, s.name

, s.[schema_id]

, s.base_object_name

, OBJECTPROPERTYEX(s.[object_id], N'BaseType') AS base_object_type

, ep.value AS [description]

FROM sys.synonyms s WITH(NOLOCK)


SELECT value, major_id, minor_id

FROM sys.extended_properties WITH(NOLOCK)

WHERE class = 1 AND name = 'MS_Description'

) ep ON s.[object_id] = ep.major_id AND ep.minor_id = 0;


-- 19. xml collections

SELECT c.name AS name, c.[schema_id]

FROM sys.xml_schema_collections c WITH(NOLOCK)

WHERE c.[schema_id] <> 4

ORDER BY c.xml_collection_id;


Then I switched to another database and noticed that only a part of queries (10-19) has been executed. This is a way of optimization, when we retrieve names of system objects only once. You need to understand that names of system objects do not change between databases. However, the data returned by these objects is subject to change.

Another interesting optimization is hidden in the settings. If such option «Detect changes on a server…»

sqlcomp 3 SQL SERVER   Looking Inside SQL Complete   Advantages of Intellisense Features

If you turn it on, then while updating the custom objects prompt, the following query is executed:



, MAX(modify_date) AS max_date

, COUNT(1) AS num

FROM sys.objects WITH(NOLOCK)

WHERE [type] IN ('U', 'V', 'IF', 'TF', 'FT', 'FN', 'AF', 'FS', 'P', 'RF', 'PC', 'X', 'SN', 'TR', 'TA')

GROUP BY [type];

The optimization goal is to define what types of objects have changed since the last update of metadata and get changes only for changed types.

When profiling, I noticed another interesting optimization. When writing code, additional information about the objects is loaded when they are selected. For example, we choose a table.

sqlcomp 4 SQL SERVER   Looking Inside SQL Complete   Advantages of Intellisense Features

And we have information about this table immediately:

-- table/view columns



, c.[object_id] AS parent_id

, c.column_id

, c.is_rowguidcol

, c.is_identity

, c.is_nullable

, c.is_computed

, CASE WHEN c.default_object_id <> 0 THEN 1 ELSE 0 END AS default_exist

, c.[precision]

, c.scale


CASE WHEN t.base_type_name IN ('nchar', 'nvarchar') AND c.max_length <> -1

THEN c.max_length / 2

ELSE c.max_length

END AS INT) AS [length]

, t.name AS [type_name]

, t.base_type_name

, t.is_user_defined

, t.type_schema_name

, ep.value AS [description]

FROM sys.all_columns c WITH(NOLOCK)




, SCHEMA_NAME([schema_id]) AS type_schema_name

, COALESCE(TYPE_NAME(system_type_id), name) as base_type_name


) t ON c.user_type_id = t.user_type_id


SELECT value, major_id, minor_id

FROM sys.extended_properties WITH(NOLOCK)

WHERE class = 1 AND name = 'MS_Description'

) ep ON ep.major_id = c.[object_id] AND ep.minor_id = c.column_id

WHERE c.[object_id] = {0}

ORDER BY c.column_id;


-- index for selected table/view



, i.index_id

, i.name

, i.is_unique_constraint

, i.is_primary_key

, i.[type]

FROM sys.indexes i WITH(NOLOCK)

WHERE i.[object_id] = {0}

AND i.index_id > 0

AND i.is_hypothetical = 0

ORDER BY i.index_id;


-- index columns for selected table/view



, ic.index_id

, c.name

FROM sys.index_columns ic WITH(NOLOCK)

JOIN sys.columns c WITH(NOLOCK) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id

WHERE ic.[object_id] = {0}

AND ic.index_id > 0



, ic.index_column_id;


This same approach works while prompting another database objects (e.g. stored routines, functions and synonyms).

The described optimization improves usability when working. You do not need to wait a second to write a query.

In the future, I plan to continue to look under the hood of other SSMS add-ins. It is more interesting to work, when you know how it works.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Generating Meaningful Test Data with dbForge Data Generator for SQL Server

Recently I faced with necessity of generating meaningful SQL data for testing purposes. During my search for a proper application for the task, I found out that Devart, a recognized developer of database solutions, released dbForge Data Generator for SQL Server, a GUI tool for fast generation of meaningful SQL data for development and testing purposes. Devart stated that with the tool, I will be able to:

  • Populate SQL Server tables with millions of rows of test data
  • Generate meaningful data that looks just like real data
  • Use 100+ predefined generators with sensible configuration options
  • Customize built-in generators
  • Create user-defined generators
  • Preview generation results
  • Save data population script for future reference
  • Automate data generation with the command-line interface

It sounded great, so I decided to give a quick test for the tool. Moreover, Devart offers a 30-day free trial for the most of its products, including Data Generator for SQL Server.

The first thing I noticed after running SQL Data Generator was clear user-friendly interface. The start page of the tool includes quick access to the main features and allows to quickly run recent projects, if any.

dbforge 1 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

After running new data generation, I was prompted to select connection and database as well as to set default data generation options. I decided to generate meaningful data for few columns of the AdventrureWokrsTest database.

dbforge 2 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

On the Options page, I selected to generate 2000 rows, and cleared the Include Null values option.

dbforge 3 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

After that, Data Generator showed the generation document. I selected the following columns of the Person.Contact table for data population:

  • ContactID
  • Title
  • First Name
  • LatName
  • EmailAddress

Now, the time has come for selecting generators for each column. In dbForge Data Generator for SQL Server, all generators are split into several categories for easier look-up.

dbforge 4 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

I was really impressed with the collection of generators the application offers. The tool automatically detects and offers appropriate generators for a given column.  Also, Data Generator for SQL Server includes multiple ways to make generated data more customized. For instance, you can:

  • Set the range of generated values for different SQL data types
  • Define the percentage of NULLs to be generated
  • Set the value distribution mode
  • Immediately preview data that will be generated

Data Generator suggested the following generators:

  • IDs for the ContactID Additionally, I selected the Random by timestamp value distribution mode for getting more complex numbers.
  • Honirific Prefix (Title) for the Title column
  • First Name (male) for the FirstName column
  • Last Name for the LastName column
  • Email for the EmailAddress This generator is based on the following regular expression \c{3,8}(\d{0,3}|.\c{3,10})@nowhere.com that can be customized right in the Fill settings section of Column generation settings. For instance, you can modify the regular expression, so that the domain name would be “mailservice.com” instead of “nowhere.com”.

dbforge 5 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

Data Generator also allows to modify existing generators and create your own ones, so in future you won’t need to modify the same generator over and over again. I opened the Email.xml file, that is located at C:\Users\Public\Documents\Devart\dbForge Data Generator for SQL Server\Data Generators,  modified regular expression so that the domain name would consist of combination of letters, updated description, name, and saved the file as MyEmail.xml:

dbforge 6 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

The following screenshot shows the data generation document with the result of my manipulations:

I was quite satisfied with the results showed in Preview, so I was ready to populate the target database with data. Data Generator offers several ways to do that, namely:

  • Open the data population script in the internal editor
  • Save the SQL script to a file
  • Run the script automatically

dbforge 7 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

I chose the first option and in few seconds, I got the following population script:

dbforge 8 SQL SERVER   Generating Meaningful Test Data with dbForge Data Generator for SQL Server

To sum up, I would like to say that the tool has done a fantastic job for me. In my opinion, the most strong points of Devart’s Data Generator are:

  1. Simple work-flow in a convenient interface. With dbForge Data Generator, the process takes just couple of minutes and basically, it is simplified to three simple steps:
  • Selecting required tables
  • Selecting and customizing a required generator
  • Populating tables with test data
  1. Impressive collection of generators. The tool includes over 100 generators that allow to populate tables with realistic-looking test data.
  2. Flexible Customization Options. Developers put really much effort for making data more customized and suiting specific needs.

I strongly recommend to give dbForge Data Generator for SQL Server a try and to evaluate its functionality on your own by downloading a 30-day free trial.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Using Power of Web to Query Databases / Sources With Skyvia

Web technologies are moving at a pace that we can hardly keep up in pace. More and more of investments are going on in the space of web that even database developers like me find it interesting. In the same context, recently I was exploring on Skyvia (SQL SERVER – Integrate Your Data with Skyvia – Cloud ETL Solution) from ETL to how one might query and was surprised to see how some of these can be made useful.

This blog stems from a simple idea that was in my mind for a long time and I was struggling for a suitable solution till I saw what was available here. Think that you are a DBA and are on a vacation to a far-away land. You didn’t take your laptop along because you wanted to really enjoy your vacation times. Everything looks normal till you get an SMS from your management that something is wrong on one of your servers and they need 10-15 mins of your time to quickly resolve. Now with no laptop and all the cool things that you would have normally installed on your machine, how can you help your management with a few query support? Is it even possible? Assume you get to an internet kiosk and you want to do some amount of troubleshooting. How will you do? Well, there are a number of solutions.

One of the many available including the use of Skyvia to do query on your servers once you have done the simple step setup. In this example, I have used an SQL Server 2014 version available on a Cloud provider so that we can see what is going wrong.

Let us start by connecting to Skyvia and setting up our connections.

 Using Power of Web to Query Databases / Sources With Skyvia

In my example, I am going to use the “SQL Server” source and will give all the credentials. Once done and tested, we must see this in our connections tab.

 Using Power of Web to Query Databases / Sources With Skyvia

Once the connection setting seem to be in place, we are all set to run our queries on the backend. We can then head to the Query tab as shown below:

 Using Power of Web to Query Databases / Sources With Skyvia

At this stage, I just linked the Query window to my connection that we created in our previous step. This started to list our all the tables that were available on my server. This was really a cool moment because I was able to work on my database in less than 5 mins via the web.

 Using Power of Web to Query Databases / Sources With Skyvia

Obviously based on the permissions that I used to connect, the tables and objects are displayed. If there are 100’s of tables to work, then we can surely use the search to find the objects.

The next stage or table is our awesome query window. It is here that we can type in any SQL-92 query that needs to be fired against our database. Since I was wanting to know what was happening in my SQL Server system and what operators were functioning of few long running query, I used a DMV from SQL Server 2014 to check if it works. For my surprise, I was not disappointed.

 Using Power of Web to Query Databases / Sources With Skyvia

This is the query result output based on the query that I was running in the backend.

node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS FLOAT)/SUM(estimate_row_count)
FROM sys.dm_exec_query_profiles
GROUP BY node_id,physical_operator_name
ORDER BY node_id;

To know more about sys.dm_exec_query_profiles you can read from MSDN. I also talk about this DMV in my course at Pluralsight – SQL Server 2014 Administration New Features. The course is exhaustive in understanding some of these new capabilities of SQL 2014.

Coming back to our query, based on the output I was able to find out what was the query that was running behind the scene. As you can see this is a Dummy query without any filters or restriction on the columns selected. Hence we are seeing a number of Clustered Index Scan in our query output via the web.

SELECT      *
FROM DimProduct
INNER JOIN DimProductSubcategory
ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
INNER JOIN FactInternetSales
ON DimProduct.ProductKey = FactInternetSales.ProductKey

Once the query was identified, you can start looking at what is firing up the query and take corrective actions. Any normal SQL syntax can be fired like this via the web. It is simple and quite powerful to say the least.

The results from Skyvia is powerful that you can also use them to export to CSV format for later offline consumption. As part of simple testing, I also fired few DDL statements and was surprised to have almost everything I do via SQL Server Management Script window here via the web, anytime and anywhere I like.

And another thing I’d like to mention is that Skyvia Query is not just for SQL Server and other databases. Skyvia Query supports SQL for cloud CRMs, such as Salesforce, Dynamics CRM, etc, and allows using SELECT, INSERT, UPDATE and  DELETE statements against them. This enables admins, knowing SQL, work with cloud data in the same way as with SQL Server data.

Currently Skyvia is free for use. You can find more information on how to use their online SQL editor on the following page: Online SQL Query Editor for Cloud and Relational Data.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Collect and Analyze SQL Server Data Efficiently

dbForge Event Profiler is one of the most useful SQL Server “build-in” tools. The Profiler records data associated with various SQL Server events for further analysis. This data is stored in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose SQL Server relates problems. The tool allows you to view communications between a client and SQL Server, and gives you an insight into its internal performance. To take full advantage of its potential, download dbForge Event Profiler for SQL Server for free now.

The tool offers a large spectrum of features that can assist you in:

  • analyzing trace results by grouping or aggregating them;
  • auditing user activity;
  • creating your own custom traces and save them for future use;
  • debugging T-SQL code and stored procedures;
  • executing quality assurance check;
  • identifying performance-related problems with front-end applications, queries, T-SQL, transactions, and so forth;
  • performing stress testing;
  • performing query analysis of execution plans;
  • viewing SQL Server performance when interacting with a client.

Essentially, the Event Profiler is designed to quickly and efficiently track down and fix many SQL Server related problems, such as poorly-performing queries, locking and blocking, excessive table/index scanning, and a lot more. For example, you can monitor the execution of a stored procedure to see whether it hampers SQL Server performance.

Using the Profiler, you can monitor the events that you are interested in. For example, you may want to capture events from a specific user or a given database. Smart filters allow you to collect only the events that you want, filtering out those of no interest. This reduces the amount of data that is stored in your trace.

dbForge Event Profiler provides a rich graphical user interface that can be used to create, analyze, and replay trace results. As the trace data is being collected, you can stop or pause the trace at a certain point and store the trace results to a physical file on a local hard disc. The saved SQL Server Profiler document has the “.*ssp” extension. This file may then be viewed to analyze data captured, share it with others, or compare the trace results to traces performed later.

Powerful Tool in Action

Below is an example of how you create a new trace using dbForge Event Profiler.

To create a new trace, you follow these steps:

  1. On the Start page, click Profile Server Events. The Profile Server Events wizard appears.
  2. Specify the connection.
  3. Optionally, select a profiling template, modify the trace file settings and the data storage settings. Click Next.

devarteventprofiler1 SQL SERVER   Collect and Analyze SQL Server Data Efficiently

  1. On the Events to Capture page, select the events you want to capture. Click Next.
  2. Optionally, on the Actions page, select the actions you want to capture in the current events session. Click Next.
  3. Optionally, on the Event Filters page, specify filter options to limit the tracing data.
  4. Click Execute. The Trace will start and the Server Event Profiler Document opens.

devarteventprofiler2 SQL SERVER   Collect and Analyze SQL Server Data Efficiently

Once you have collected enough data, stop the trace by clicking the Stop Trace button on the toolbar. At this point, you may review the collected data or save it to a file for future use.

Now that you have learned about many advantages of this smart tool, you can start mastering and making practical use of the dbForge Event Profiler for SQL Server by downloading it now for FREE.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Cloud Based Data Integration Made Easy – A Real World Scenario

If you are a DBA, once in a while, you will have a situation where you end up with some tasks which will be boring and annoying. Trust me in my life, I often come across similar scenarios quite often. Here is one such tasks I came across a few days ago.

A Task, I would like to Automate

Just the other day I was assigned a task where I had to take a CSV file from my network and insert into SQL Server which was hosted in remote locations. Well, you may think it as a pretty easy task and I agree with you this is very easy task. The challenge is not about its difficulty, but the challenge was rather about the entire process and my interest. The part which annoyed me the most was that I have to do this every day at 4 PM.

This means, every day I must be at my desk at 4 PM and take a file from the network and upload to remote SQL Server. What about weekends? What about when I have to step away from my desk at 4 PM? What about the situation, when I am busy doing something much more important than this task? Well, as I said, more than task, I have been just one-place with the routine which was associated with it. In simple words, this was an ETL task which needed to be automated, but I can’t depend on my machine always. I have to find a solution which was cloud based and runs efficiently.

Skyvia at Rescue

I was sitting miffed in office and suddenly I remembered that last year I blogged about the tool Skyvia. Here is the blog post Integrate Your Data with Skyvia – Cloud ETL Solution. I quickly referred to my own blog, post and realized I should give Skyvia a try.

What is Skyvia?

Skyvia is a powerful solution for cloud data integration (ETL) and backup. It is a complete online solution and does not require any local software installed except for a web browser. In Skyvia we can create integration packages that define the operations and then we can run them or schedule for automatic execution. An integrated package is a set of data ETL operations that can be stored for future use, executed, modified, or deleted. Skyvia provides several kinds of packages for different integration scenarios. They are Import, Export, Replication, and Synchronization packages.

How did I do it?

Well, here are few of the screenshots of the task which I was assigned.

First, I checked if the table where I have to export data exists or not. As the table was already created, I quickly checked if it contained data or not. The table contained no data.

1 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

Next we will open the Skyvia web interface. It is pretty simple and it will list three options on the left bar. We will click Integration there.

2 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

In the Integration section, click Create Now under Data Import.

3 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

In the data integration screen we will be presented with various options. We will load the CSV file from an FTP server, so we select source as a CSV from FTP and target as SQL Server.

4 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

As we will be connecting SQL Server for the very first time we will be creating new connection and that is pretty straight forward procedure.

5 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

Then we will configure an FTP connection

6 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

Next we will configure CSV options. Here will be providing various options, but in our case all the default options were good enough for us to move next.

7 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

Right after that we will select the target table. In our case the target table is actor table.

8 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

The next screen will present mapping and we will one more time review various mapping options. We will make sure that all the source and target columns maps correctly.

9 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

When we click finish it will bring up the following screen.

10 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

Click on Save and now we are back on the following screen. Over here we can execute our task and see if it works or not. Click on the RUN button on the right side of the screen.

11 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

In my case the task ran successfully and it shows that it has inserted 200 rows successfully. The time taken to complete this entire task was 35 seconds and it depends on my network connection to the destination server.

12 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

We can execute the same select statement which we had executed earlier and see if the table contains the valid data.

13 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

Once we commit that our task has worked successfully, we can create a daily schedule.

14 SQL SERVER   Cloud Based Data Integration Made Easy   A Real World Scenario

That’s it! I am done.

Now every day at specific time the task will automatically execute and will log history.

Next Action Items

Team Devart has created Skyvia a feature rich service. One has to experiment with various different options to fully see the vast capability of this amazing product. Here are few things you can consider doing it. Here is the link where you can sign up for Skyvia for totally FREE. Next I will be trying out Skyvia with Salesforce. Skyvia is an all-in-one cloud solution for various Salesforce data integration scenarios. In addition to the standard Salesforce data loader functionality – data import and data export – it offers powerful data replication and synchronization tools and backup functionality with preview and export of backed up data, data search, viewing data changes between backups, and one-click restore.

Sign up for Skyvia for totally FREE.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – I am Participating in Winter Discounts and Christmas Photo Fest

I often get asked which is my favorite tool from DevArt. They have many great tools, but I am really fan of the development productive tool – dbForget Studio for SQL Server. Devart’s dbForge Studio for SQL Server is a fantastic tool for SQL Server development, as soon as I installed it I found so many things to learn. First off, it will increase SQL coding almost instantly. There is very little to learn, you are not just memorizing codes to “cheat” off of. DbForge Studio provides code completion options and automatic SQL formatting, so that you know your code will work. One of my favorite feature is “snippets,” which stores parts of code that you use over and over to cut down on typing and searching – because you know they’re always a few commands you use again and again! Here is another blog post I wrote about Query Plan Analysis of Pivot and Unpivot with Profiler of dbForge Studio for SQL Server.

Every year in December the team at DevArt announces discounts and some fun contest. This year they have also announced similar fun contests as well as a Christmas photo fest.  The Christmas season is a great time to get 20% off on all Devart licenses until January 15th, 2015. If you want to get more discounts, take part in the Devart Christmas Photo Fest!

Share your Christmas desktop, workspace, office, laptop, tablet, etc. You can make a selfie, take a photo of your pets or any other kind of photo you wish. Share your Christmas mood and you will get a chance to win! The key condition is that any installed Devart product must get into the shot. Be creative and funny.

Please go to this blog post where it has given conditions and an example shot. I am going to take part in the contest and I encourage you to take part as well.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Story of Temporary Objects

SQL Server’s version of Transact SQL provides the ability to create and leverage temporary objects for use within the scope of your query session or batch. There are many reasons why you may decide to use temporary objects and we will explore them later in this article. In addition to meeting various data-tier requirements, temporary objects can also be beneficial from a performance and scalability perspective.

SQL Server provides a few temporary objects options:

  • Temporary tables both local and global
  • Temporary storage procedures (not commonly used)
  • Table variables

Why use temporary objects?

Some reasons for using temporary objects:

  • No requirement for persistent data storage for a result set.
  • Intermediate state needed only temporarily, for example when performing data transformation or aggregation.
  • Use of temporary storage only visible to the current session.
  • Reduce query plan complexity.
  • Portability of lookup-data by the containing modules, for example stored procedures.

The article will make heavy use of demos. I will be use demos to show syntax, overall capabilities, and usage scenarios. To write T-SQL code I opt for dbForge Studio for SQL Server. It is a powerful IDE for SQL Server management, administration, development, data reporting and analysis. The tool contains an excellent SQL code editor that provides SQL syntax check, snippets, automatic code formatting, and code completion.

Creating a temporary table

The following example demonstrates how to create a temporary table, insert some data from a permanent table into the temporary table, and select data from the temporary table.

create table 1 SQL SERVER   Story of Temporary Objects

The query returns the following result

temp table query results 2 SQL SERVER   Story of Temporary Objects

Note the # pound sign indicates that we work with the temporary object. To remove the temporary table explicitly, we can execute the following query:

removing temporrary table 3 SQL SERVER   Story of Temporary Objects

SELECT INTO a temporary table

You can use SELECT INTO statement to create a temporary table implicitly. The following example creates a temporary table and populates it with data from the permanent table.

select into temp table 4 SQL SERVER   Story of Temporary Objects

If you need an empty temporary table based on the schema of an existing table, and you do not want to populate it with rows, you can do the following:

empty temporrary table 5 SQL SERVER   Story of Temporary Objects

In this case, zero rows will be affected. If you try to select from the temporary table, no rows will be returned.

empty table 6 SQL SERVER   Story of Temporary Objects

Temporary table scope and lifecycle

Let us get back to the SELECT INTO example discussed above. If you disconnect and connect back to an SQL Server instance, the temporary table will no longer exist. If you then try to query from the temporary table you will get an error message – Invalid object name ‘#temp_contact’.

So why explicitly drop the temporary table? You can think about tembdb usage for larger modules with longer session durations. You can also think about numerous concurrent module executions as well as about modules with many temporary objects.

Viewing temporary table metadata

Once we have created a temporary table, we can execute a system-stored procedure that reports information about a database object, a user-defined data type, or a data type.

temp table metadata 7 SQL SERVER   Story of Temporary Objects

However, in this case we get an error. We can go ahead and try this in tempdb.

temp db temp table metadata 8 SQL SERVER   Story of Temporary Objects

We will see the table name and the value appended to the end. We can use this name to see an entry for this table in sys tables.

sys table query 9 SQL SERVER   Story of Temporary Objects

This query returns full information about our table.

Temporary table data types

In this example, we will create a temporary table with a list of supported data types. The following query executes successfully, which means all these data types are supported.

example data types 10 SQL SERVER   Story of Temporary Objects

However, there is an exception. Let’s try to create a new temporary table with user-defined data type. The following query will return an exception.

user data type 11 SQL SERVER   Story of Temporary Objects

If you need to use user-defined data-type, you need to use a temporary table. The following script executes correctly.

temp db for user data type 12 SQL SERVER   Story of Temporary Objects

Then you can use the tempdb and the DROP TYPE statement to cleanup.

Temporary tables and dynamic SQL

You can use dynamic SQL to create a temporary table. The following example demonstrates how to do it.

temp table via dynamic SQL 13 SQL SERVER   Story of Temporary Objects

However, if you try to execute the INSERT and then SELECT statements, you get an error.

insertinto temp table via dynamic SQL 14 SQL SERVER   Story of Temporary Objects

The above-mentioned query returns the “Invalid object name ‘#category’”. To avoid this, you need to include INSERT and SELECT within the dynamic SQL, as it shown in the following example.

include insert select in dynamic SQL 15 SQL SERVER   Story of Temporary Objects

In this case, you are able to create the temporary table, insert rows, and select from that table.

Local and global temporary tables

As you already know, a single pound sign is used to create a local temporary table (e.g. #localtemptable). If you try to insert some data to a local temporary table from within another session, you will get an error message saying “Invalid object name”. In order to create a global temporary table, you need to add an additional pound sign to a table name (e.g. ##globaltemptable). In this case, you are allowed to access this global table from a different session.

Table variables

SQL Server provides table variables start with 2000 as an alternative to temporary tables. In some cases, a table variable can be more useful than a temporary table.

Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement, as you can see in the following example.

table variable 16 SQL SERVER   Story of Temporary Objects

So here, we created a local variable. Note we have no GO in between the declaration statement and the ISERT statement. We inserted some data from the permanent table and then selected data from the variable. There is no need to explicitly DROP the table variable.

Table variable scope and lifecycle

Let’s brake the previous example into several steps. First, declare a table variable and execute the query.

declare table var 17 SQL SERVER   Story of Temporary Objects

It executes successfully. Now if we try to insert data into the variable

insert into table var 18 SQL SERVER   Story of Temporary Objects

We get an error message like “Must declare the table variable ‘@category’”. This is because the scope is for the duration of batch execution. So one of the main advantage is that there is no need to cleanup at batch completion, implies shorter lifecycle. The main cons is that it cannot cross batch boundaries.

Advantages and disadvantages of using temporary objects

Temporary tables advantages

  • Provide intermediate result-sets that can reduce query plan complexity for large or complex workloads. It is not a guaranteed solution, but a valid tool for query plan quality issues.
  • Ability to isolate a table’s data ‘per-user’ execution.
  • Provide column-level statistics like permanent tables.

Temporary tables disadvantages

  • Heavy tempdb usage via object creates/drops can lead to latch contention.
  • Not always optimal compared to inline, single-statement alternative.
  • Not supported in user-defined functions.
  • Inherit tempdb collation for non-contained databases.
  • System caching of temporary tables and associated statistics may cause unexpected query plan shapes and incorrect statistics.

Table variables advantages

  • Can be used within scalar and multi-statement table-valued functions.
  • Automatically inherit the current databases’ collation implicitly.
  • Will not directly affect recompilations.
  • Can be passed as input parameters to stored procedures.

Table variables advantages

  • Heavy tempdb usage via object creates/drops can lead to latch contention.
  • Non-column level statistics


One of the key factor influencing temporary objects usage is the performance and scalability. This means, for complex queries, sometimes breaking a query down into steps with temporary objects can improve query plan quality and increase performance.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Beginning with SQL Server Security Aspects

SQL Server Security Aspects

This article is the high-level overview of the SQL Server security best practices and aspects. Ensuring SQL Server security is an important task that may be successfully solved by applying best practices and proven solutions described further in this article.

Physical Security

SQL Server physical security aspects are often overlooked. Frequently DB admins focus on the software security and network security, while completely forgetting about the physical server itself. However, the possibility to physically reach the server, will lead to abrogation of all other security configurations that they apply to the software. As far as SQL Server is installed on a physical device, whether it would be server hardware, desktop PC, laptop, or other PC in a data center. The physical security main goal is to prevent someone from gaining access to the machine.

Shared data centers or server rooms provide a wide range of physical and environmental security to ensure that secure areas are protected by appropriate entry controls to ensure that only authorized personnel are allowed access. An appropriate, physical protection should be provided against damage from natural, or man-made disasters, such as fire, flood, explosion etc. All users are required to ensure that systems are not left open to access by intruders to buildings, or by unauthorized colleagues.

If the personnel in the data center  is unable to physically lock away the server, you must ensure that all USB ports on the server are disabled. Keep in mind that security is not complete if physical security is overlooked.

Network  Security

The network is the way that external threads may come to attack your SQL Server.

Firewalls provide efficient means to implement security. A firewall is a separator or restrictor of network traffic, which can be configured to enforce your data security policy. If you use a firewall, you will increase security at the operating system.

SQL Server Instance Security

Every SQL Server instance that is installed on a PC can be considered to be a security domain. Thus, at the instance level you could apply separate security considerations.

I will use the dbForge Studio for SQL Server v 4.0 for the demonstration purposes as it has an excellent tool called Security Manager.

The following picture demonstrates two separate instances of SQL Server on the same computer.  Each has its own specific name, the first one is MSSQLSERVER\Instance1, the second one is MSSQLSERVER\Instance2.

instanses horizontal SQL SERVER   Beginning with SQL Server Security Aspects

This picture gives us the ability to see how security applies at the instance level. SQL Server Security uses the concept of securables that will be discussed later in this article.

Securables are the objects to which the SQL Server authorization system controls access. For instance, a table is a securable. Some securables can be contained within others, creating nested hierarchies called “scopes”. The securable scopes are server, database, and schema.

An object that receives permission to a securable is called a principal. The most common principals are logins and users.

A login is a security principal, or an entity that can be authenticated by a secure system. Users need a login to connect to SQL Server. You can create a login based on a Windows principal (such as a domain user or a Windows domain group) or you can create a login that is not based on a Windows principal (such as an SQL Server login).

To control access to securables, a DB admin can grant or deny permissions, or add logins and users to roles that have access.

It is important to note that every individual instance has it’s own set of security considerations. The security folders on each instance contain the same users, application roles,(etc.) folders. However, if we expand the logins folder you can see the different number of accounts.

logins SQL SERVER   Beginning with SQL Server Security Aspects

This proves that we can have different security settings for individual users across different instances.

It is also important to note that when you do set up an SQL Server instance, you only install the necessary features that are necessary for that instance to operate. This reduces the possibility to attack of your specific SQL Server instance, by reducing the number of services and features that are available for malicious users to gain access to.

SQL Server Securables

Securables are the resources to which the SQL Server Database Engine authorization system regulates access. Essentially these are the database objects to which you can apply security on your instance of SQL Server. Some securables are standalone and others may be contained within another securable.

Each database also has its own security folder, where we can focus on users, which again, become the logins that we assign the permissions to for our database.

db level security SQL SERVER   Beginning with SQL Server Security Aspects

We also have database level roles and application roles. Currently, there are no application roles assigned on this server. There are another components that we do not see in the Database Explorer, however they still exist at the data base level. These components are assemblies which are typically DLL files that are used in the instance of sequel server for deploying functions stored procedures, or triggers. These components are managed by CLR.

SQL Server Principals

There are three high level categories of SQL Server security principals:

  1. Windows level (domain and local logins)
  2. SQL Server level (SQL Logins and server roles)
  3. Database level (database users, DB and application roles)

There are two SQL Server authentication types:

  • by using Windows account security token
  • by using an account defined inside SQL Server

You can tune the type of authentication during installation. Windows mode is the most secure, as it relies on Windows accounts as opposed to SQL accounts. The recommended setting is Windows Authentication and it is selected by default. This authentication type  simplifies administration and is more secure, because it uses Windows security tokens. There is no need to remember another password, and no password transits through the local network.

You cannot disable Windows authentication, however you can extend it with SQL Server authentication. Thus, in case you need to allow access to the users outside the local network, you should use SQL Server authentication. In this case, logins are defined and managed inside SQL Server. The authentication and password validation is managed by SQL Server also.

There are also default roles created during installation as well. One of which is known as the public role.

public role SQL SERVER   Beginning with SQL Server Security Aspects

SQL Server creates the public role as a way of assigning permissions to users who have accounts on the database, but who do not have any other currently assigned permissions. The public role serves the purpose, of providing limited permissions to user accounts on the database, until you’ve had the opportunity to assign the necessary permissions for those user accounts. SQL Server also includes a guest user account. This account is not a server level account, but exists at the database level.

If we expand the users folder within the database, we will notice a guest account, that is available for each database. The guest account is used to grant permissions to users, who might be able to access the database. But, who do not have a user account assigned, or created in the database itself.

guest user SQL SERVER   Beginning with SQL Server Security Aspects

The guest account cannot be dropped, but it can be disabled, through the revocation of the connect permission. User and application access to your SQL Server instance, will be controlled using these security principles.

Manage Logins and Users

As mentioned above in this article, SQL Server implements security through the use of securables, which are the database objects, and security principles, which are the user’s inner applications that will access the database.

If you know how to create the logins and user accounts – it is the first step in creating the authentication and authorization model for your SQL Server.

Before creating logins, you must know which authentication mode SQL Server instance is configured to use. In case of mixed mode authentication, we can create Windows accounts, or we can create local SQL Server accounts for authentication on the instance of SQL Server.

Keep in mind that SQL Server allows you to create logins through Windows accounts or SQL Server accounts at the server level. Also SQL Server permits the creation of local user accounts at the database level. If we expand the database, and expand the Security folder, we notice that SQL Server includes a Users folder at the database level.

Simply right click the Users folder, and then click New User. This allows us to choose a user type at the database level, as opposed to the server level. We can create SQL user with or without a login, what means that this would be a user name we create specifically in this database.

CreateLogin SQL SERVER   Beginning with SQL Server Security Aspects

A good example of such an account is the guest account, which is created at the database level, but does not exist at the logins level for the server itself.

You can set the authentication mode to Mix Mode at any time after the installation. And then you can create SQL Server accounts, or use Windows accounts, depending on your choice.

Understanding each type of account and how security is applied to these accounts will help you set the proper security for user access.

Understanding and using roles

SQL Server roles allow you to group users or logins into a specific container for assigning permissions to securables on your SQL Server instance. Permissions assigned to a role are applied to any user or login that is associated with that role. SQL Server provides nine fixed server roles. You can find these server roles in the Security folder of the SQL Server instance. Outside of the nine fixed server roles, SQL Server also provides database level roles.

db level roles SQL SERVER   Beginning with SQL Server Security Aspects

SQL Server Permissions

Accessing any of the secureables in SQL Server requires that the login or user have permissions. The permissions in SQL Server follow certain naming conventions. The security check algorithm used by SQL is complex. And the complexity comes from the need to evaluate group or role membership, as well as explicit and implicit permissions.

There are three core elements of SQL Server permission checking:

  1. Security context. This is related to the user, the login or the role.
  2. Permission space. The permission space will focus around the securable, such as the database object that the user is attempting to access. SQL Server checks the permissions that the principle has been assigned according to that securable.
  3. Required permissions. This is an important step because some tasks require more than one permission. For instance, if a user attempts to execute a stored procedure, the execute permission would be required in the stored procedure itself.


SQL Server security is too huge topic to be discussed in a single article. However, this article provides an overview of core principles and concepts of SQL Server security.

SQL SERVER – A Practical Use of Backup Encryption

 Backup is extremely important for any DBA. Think of any disaster and backup will come to rescue users in adverse situation. Similarly, it is very critical that we keep our backup safe as well. If your backup fall in the hands of bad people, it is quite possible that it will be misused and become serious data integrity issue. Well, in this blog post we will see a practical scenario where we will see how we can use Backup Encryption to improve security of the bakcup.

Feature description

Database Backup Encryption is a brand new and long expected feature that is available now in SQL Server 2014. You can create an encrypted backup file by specifying the encryption algorithm and the encryptor (either a Certificate or Asymmetric Key).

The ability to protect a backup file with the password has been existing for many years. If you use SQL Server for a long time, you might remember the WITH PASSWORD option for the BACKUP command. The option prevented unauthorized access to the backup file.

However this approach did not provide reliable protection. In that regard, Greg Robidoux noted on MSSQLTIPS: “Although this does add a level of security if someone really wants to crack the passwords they will find a way, so look for additional ways to secure your data.

To protect a backup file, SQL Server 2008 introduced the transparent data encryption (TDE) feature. Thus, a database had to be transparently encrypted before backup. Therefore, start with SQL Server 2012 the PASSWORD and MEDIAPASSWORD parameters are not used while creating backups. Even so, data encryption and backup files encryption are two different scenarios.

In case a database is stored locally, there is no need to encrypt it before backup. Fortunately in SQL Server 2014 there are two independent processes. Along with data encryption it is possible to encrypt a backup file based on a certificate or an asynchronous key. Supported encryption algorithms are:

  • AES 128
  • AES 192
  • AES 256
  • Triple DES

Practical use

To illustrate above mentioned, I will create an encrypted backup of the Adventureworks database. Also, you can back up directly to Azure. If needed, you may restore the encrypted back up file on Azure.

I will use dbForge Studio for SQL Server to create the encrypted backup file.

To protect the backup file we need to create an encryptor: either a Certificate or Asymmetric Key. Then, we need to pass this encryptor to the target SQL Server to restore the backup. For this, the encryptor must be exported from the source SQL Server and imported to the target SQL Server. There are no problems with the certificate in this regard. It is more complicated with asymmetric keys.

Taking into account that the BACKUP ASYMMETRIC KEY command is not available, and we can not just create a duplicate for an asymmetric key (compared to symmetric key), the only approach is to create the asymmetric key outside the SQL Server. Then we can use the sn.exe utility to transfer it inside SQL Server (CREATE ASYMMETRIC KEYkeynameFROM FILE = ‘filename.snk‘). After that we can use this asymmetric key to encrypt the backup file on the source instance. Further we need to use the same *.snk file to create the asymmetric key on the target instance (and restore the backup file).

In our example we will not use asymmetric keys. We will use a certificate. Moreover the certificate (behind the scene) is the pair of open/closed keys.

Let’s create the server certificate and use it to encrypt the backup file.

devbackup1 SQL SERVER   A Practical Use of Backup Encryption

The certificate will be protected with the database master key, because we didn’t specify the ENCRYPTION BY statement.

This is exactly what we need. Only certificates signed with the database master-key can be used for the encryption purposes. Otherwise, If we for instance, protect the certificate with the password ENCRYPTION BY PASSWORD = ‘strongpassword‘, the following error appears while attempting to encrypt the backup file:

“Cannot use certificate ‘CertName’, because its private key is not present or it is not protected by the database master key.”

Encrypted backups (along with usual backups) can be traditionally created locally on the hard drive and in Azure Storage.

Instead of writing tons of SQL code I will use the convenient dbForge Studio for SQL Server Back Up wizard. The wizard allows to create the database backup in several clicks.

Step 1: Setup the DB Connection and the backup file location.

devbackup2 SQL SERVER   A Practical Use of Backup Encryption

Step2: Setup mediaset

devbackup3 SQL SERVER   A Practical Use of Backup Encryption

Step 3: Select the encryption algorithm and certificate.

devbackup4 SQL SERVER   A Practical Use of Backup Encryption

In case you don’t want to pay extra attention to transferring the backup file to the Windows Azure, you can backup directly to Azure.

devbackup5 SQL SERVER   A Practical Use of Backup Encryption

After the script execution in the required container the blob (with the backup) appears.

devbackup6 SQL SERVER   A Practical Use of Backup Encryption

In case you had already created a backup with the same name in the same container, you can get the following error: There is currently a lease on the blob and no lease ID was specified in the request.

Further, you can restore the back up file on the Windows Azure.


Obviously, it is a good practice to encrypt a backup file while transferring. This, for instance, allows to avoid data leak while transferring backups from one DPC to another.

Reference: Pinal Dave (http://blog.sqlauthority.com)