MS SQL Server

ByDr. SubraMANI Paramasivam


  • Business Intelligence
  • Database Development
  • Database Administration
  • SQL Server Administration & Management
  • Reporting & Dashboards
  • Performance Tuning
  • High Availability
  • Custom solutions
ByDr. SubraMANI Paramasivam

SQL Server 2008 New Features

With SQL Server 2008 version there are quite a number of new features are added in. Those are listed below. (I know this is pretty old update, but new when SQL 2008 was released).

  1. Installation of SQL Server 2008 with more options
  2. Compressed Backups
  3. Enhanced configuration
  4. Management of Audits with CDC (Change Data Capture)
  5. New Table Value Parameter
  6. Filestream Data types
  7. Sparse Columns
  8. Encryption Enhancements
  9. High Availability
  10. Performance
    1. Performance Data Management
    2. Resource Governor
    3. Freeze Plan
  11. Declarative Management Framework (DMF)
  12. Development Improvements
    1. LINQ Supports
    2. MERGE Statement
  13. Spatial Data type
  14. Analysis Services Improvements
  15. ETL Enhancements
  16. Few on Reporting Services
    1. No longer use of IIS
    2. Better Graphing
    3. Export to Word Support
  17. Some of the Deprecated & Discontinued Features
ByDr. SubraMANI Paramasivam

SQL Server Advanced Options

Part -II – Configurations – Management Studio and sp_configure

In my previous article you should have been familiar with the query sp_configure to see the default values of all the list

of 16 options. Let us see the Advanced options here.

By default the show advanced options will have 0 in its value. To see and set the advanced options this has to be changed

to 1 and it can be changed back to 0 once done. The query for changing the option is
sp_configure ‘show advanced options’, 1

This query will change the config_value option to 1 and reconfigure will send the command to reload all of the values. Once

the value is changed to 1 and by running sp_configure again will display an extended list of 68 in SQL Server 2008 and 70

in SQL Server 2008 R2.

I am going to take you through some of the most commonly used options.


ByDr. SubraMANI Paramasivam

SQL Server 2008 Tools & Components

For every installation of SQL Server, we have to customize according to what has to be installed and for what purpose. The following are the list of components available in SQL Server 2008.

1. Database Engine:
This is the core component which stores, manipulates & manage data. Other features include relational, XML, full text search and replication.

2. Integration Services:
Integration services is responsible for ETL Layer. ETL stands for Extraction, Transformation & Loading. This component is used for extracting the data from the source system, transform accordingly and load it into the database in the 1st component.

3. Analysis Services:
Once the data is available in 1st component via the ETL layer in the second component, not all the shcema is in ready to us and hence those are processed and managed by creating Online Analytical Processing (OLAP) analysis and data mining model for Business Intelligence purpose.

4. Reporting Services:
Whatever transformation is done in 2nd component & 3rd component we need an end user component to see the result and this is achieved as interactive, tables, graphical representations, charts through Reporting services.

The following are the available tools in SQL Server
1. SQL Server Management Studio (SSMS)
This is the management tool to manage all the core components which we saw above.

2. Business Intelligence Development Studio (BIDS)
This tool allows a developer to develop cubes (Analysis Services), integration service packages, reports.

3. SQL Server Configuration Manager
Managing the connectivity components such as Server Network Utility, Client Network Utility, Service Manager is done using this tool. All the connectivity components was separate features in 2005 version and backwards.

4. SQL Server Profiler
All the events occured in SQL Server can be traced and saved for future use using this tool.

5. Database Engine Tuning Advisor
Shortly called DTA is used too analyse the load on the engine. The recommendations provided by DTA can be used to improve the query performance for the same query.

6. Command Prompt Utilities
This is another powerful utility that can be accessed via DOS prompt. You can go to cmd.exe and execute the following commands that have its unique magic work done without the knowledge of T-SQL.

A. bcp – bulk copy of data

B. dta – Database Engine Tuning Advisor

C. dtexec – configure and execute SSIS packages

D. dtutil – Manage SSIS package

E. sqlcmd – command line to execute T-SQL statements

F. rs – Manage Reporting services server

G. rsconfig – manage & configure report server connection

H. rskeymgmt – manage encryption keys on report server

I. sqlagent90 – to start SQL Server Agent

J. SQLdiag – Diagnostic collection by Microsoft

K. sqllogship – Operational tasks like copy, restore, backup operations

L. sqlservr – start and stop the database engine for an instance

M. Ssms – to open SSMS

N. sqlps – to run PowerShell commands

O. tablediff – capable of comparing data from 2 tables in replication topology.