MS SQL 2014

Dynamic Management Views T-SQL : Applies only to SQL Server 2014, SQL Server 2016 

SQL Server Operating System (SQLOS)

sys.dm_os_buffer_descriptors (Transact-SQL) sys.dm_os_memory_pools (Transact-SQL)
sys.dm_os_child_instances (Transact-SQL) sys.dm_os_nodes (Transact-SQL)
sys.dm_os_cluster_nodes (Transact-SQL) sys.dm_os_performance_counters (Transact-SQL)
sys.dm_os_dispatcher_pools (Transact-SQL) sys.dm_os_process_memory (Transact-SQL)
sys.dm_os_hosts (Transact-SQL) sys.dm_os_schedulers (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL) sys.dm_os_stacks (Transact-SQL)
sys.dm_os_loaded_modules (Transact-SQL) sys.dm_os_sys_info (Transact-SQL)
sys.dm_os_memory_brokers (Transact-SQL) sys.dm_os_sys_memory (Transact-SQL)
sys.dm_os_memory_cache_clock_hands (Transact-SQL) sys.dm_os_tasks (Transact-SQL)
sys.dm_os_memory_cache_counters (Transact-SQL) sys.dm_os_threads (Transact-SQL)
sys.dm_os_memory_cache_entries (Transact-SQL) sys.dm_os_virtual_address_dump (Transact-SQL)
sys.dm_os_memory_cache_hash_tables (Transact-SQL) sys.dm_os_volume_stats (Transact-SQL)
sys.dm_os_memory_clerks (Transact-SQL) sys.dm_os_wait_stats (Transact-SQL)
sys.dm_os_memory_nodes (Transact-SQL) sys.dm_os_waiting_tasks (Transact-SQL)
sys.dm_os_memory_objects (Transact-SQL) sys.dm_os_windows_info (Transact-SQL)
sys.dm_os_workers (Transact-SQL)

The following SQL Server Operating System–related dynamic management views are Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

sys.dm_os_function_symbolic_name
sys.dm_os_ring_buffers
sys.dm_os_memory_allocations
sys.dm_os_sublatches
sys.dm_os_worker_local_storage

Tutorial: Writing Transact-SQL Statements, just click and learn.

 

MS SQL Server 2014 HotfiX’s

Build File version KB / Description Release Date
12.00.4050 2014.120.4050.0 SQL Server 2014 Service Pack 1 (SP1) April 15, 2015 
12.00.2495 2014.120.2495.0 3046038 Cumulative update package 7 (CU7) for SQL Server 2014 April 23, 2015 
12.00.2488 2014.120.2488.0 3048751 FIX: Deadlock cannot be resolved automatically when you run a SELECT query that can result in a parallel batch-mode scan April 1, 2015
12.00.2485 2014.120.2485.0 3043788 An on-demand hotfix update package is available for SQL Server 2014 March 16, 2015
12.00.2480 2014.120.2480.0 3031047 Cumulative update package 6 (CU6) for SQL Server 2014 February 16, 2015
12.00.2472 2014.120.2472.0 3032087 FIX: Cannot show requested dialog after you connect to the latest SQL Database Update V12 (preview) with SQL Server 2014 January 28, 2015
12.00.2464 2014.120.2464.0 3024815 Large query compilation waits on RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014 January 5, 2015
12.00.2456 2014.120.2456.0 3011055 Cumulative update package 5 (CU5) for SQL Server 2014 December 18, 2014
12.00.2436 2014.120.2436.0 3014867 FIX: “Remote hardening failure” exception cannot be caught and a potential data loss when you use SQL Server 2014 November 27, 2014
12.00.2430 2014.120.2430.0 2999197 Cumulative update package 4 (CU4) for SQL Server 2014 October 21, 2014
12.00.2423 2014.120.2423.0 3007050 FIX: RTDATA_LIST waits when you run natively stored procedures that encounter expected failures in SQL Server 2014 October 22, 2014
12.00.2405 2014.120.2405.0 2999809 FIX: Poor performance when a query contains table joins in SQL Server 2014 September 25, 2014
12.00.2402 2014.120.2402.0 2984923 Cumulative update package 3 (CU3) for SQL Server 2014 August 18, 2014
12.00.2381 2014.120.2381.0 2977316 MS14-044: Description of the security update for SQL Server 2014 (QFE) August 12, 2014
12.00.2370 2014.120.2370.0 2967546 Cumulative update package 2 (CU2) for SQL Server 2014 June 27, 2014
12.00.2342 2014.120.2342.0 2931693 Cumulative update package 1 (CU1) for SQL Server 2014 April 21, 2014
12.00.2254 2014.120.2254.0 2977315 MS14-044: Description of the security update for SQL Server 2014 (GDR) August 12, 2014

 

SQL Database Engine Instances – 2014

Topic:                                                       Task Description

Describes how to configure the properties of an instance. Configure defaults such as file locations and date formats, or how the instance uses operating system resources, such as memory or threads.

Configure Database Engine Instances (SQL Server)

Describes how to manage the collation for an instance of the Database Engine. Collations define the bit patterns used to represent characters, and associated behaviors such as sorting, and case or accent sensitivity in comparison operations.

Collation and Unicode Support

Describes how to configure linked server definitions, which allow Transact-SQL statements run in an instance to work with data stored in separate OLE DB data sources.

Linked Servers (Database Engine)

Describes how to create a logon trigger, which specifies actions to be taken after a logon attempt has been validated, but before it starts working with resources in the instance. Logon triggers support actions such as logging connection activity, or restricting logins based on logic in addition to the credential authentication performed by Windows and SQL Server.

Logon Triggers

Describes how to manage the service associated with an instance of the Database Engine. This includes actions such as starting and stopping the service, or configuring startup options.

Manage the Database Engine Services

Describes how to perform server network configuration tasks such as enabling protocols, modifying the port or pipe used by a protocol, configuring encryption, configuring the SQL Server Browser service, exposing or hiding the SQL Server Database Engine on the network, and registering the Server Principal Name.

Server Network Configuration

Describes how to perform client network configuration tasks such as configuring client protocols and creating or deleting a Server Alias.

Client Network Configuration

Describes the SQL Server Management Studio editors that can be used to design, debug, and run scripts such as Transact-SQL scripts. Also describes how to code Windows PowerShell scripts to work with SQL Server components.

Database Engine Scripting

Describes how to use maintenance plans to specify a workflow of common administration tasks for an instance. Workflows include tasks such as backing up databases and updating statistics to improve performance.

Maintenance Plans

Describes how to use the resource governor to manage resource consumption and workloads by specifying limits to the amount of CPU and memory that application requests can use.

Resource Governor

Describes how database applications can use database mail to send e-mail messages from the Database Engine.

Database Mail

Describes how to use extended events to capture performance data can be used to build performance baselines or to diagnose performance problems. Extended events are a light-weight, highly scalable system for gathering performance data.

Extended Events

Describes how to use SQL Trace to build a customized system for capturing and recording events in the Database Engine.

SQL Trace

Describes how to use SQL Server Profiler to capture traces of application requests coming in to an instance of the Database Engine. These traces can later be replayed for activities such as performance testing or problem diagnosis.

SQL Server Profiler

Describes the Change Data Capture (CDC) and Change Tracking features and describes how to use these features to track changes to data in a database.

Track Data Changes (SQL Server)

Describes how to use the Log File viewer to find and view SQL Server errors and messages in various logs, such as the SQL Server job history, the SQL Server logs, and Windows event logs.

Log File Viewer

Describes how to use the Database Engine Tuning Advisor to analyze databases and make recommendations for addressing potential performance problems.

Database Engine Tuning Advisor

Describes how the production database administrators can make a diagnostic connection to instances when standard connections are not being accepted.

Diagnostic Connection for Database Administrators

Describes how to use the deprecated remote servers feature to enable access from one instance of the Database Engine to another. The preferred mechanism for this functionality is a linked server.

Remote Servers

Describes the capabilities of Service Broker for messaging and queueing applications and provides pointers to the Service Broker documentation.

Service Broker

Describes how the buffer pool extension can be used to provide seamless integration of nonvolatile random access storage (solid-state drives) to the Database Engine buffer pool to significantly improve I/O throughput.

Buffer Pool Extension File

 Back to top

Databases

Cursors

Back Up and Restore of SQL Server Databases

Tables

Sequence Numbers

Bulk Import and Export of Data (SQL Server)

In-Memory OLTP (In-Memory Optimization)

DDL Triggers

Data Compression

Indexes

DML Triggers

OLE Automation Objects in Transact-SQL

Partitioned Tables and Indexes

Synonyms (Database Engine)

Event Notifications

Views

XML Data (SQL Server)

Monitor and Tune for Performance

Stored Procedures (Database Engine)

Spatial Data (SQL Server)

Search (SQL Server)

Binary Large Object (Blob) Data (SQL Server)

User-Defined Functions

Data-tier Applications

Statistics

The Transaction Log (SQL Server)

Plan Guides

Database Checkpoints (SQL Server)

 

String Functions (T-SQL)

ASCII LTRIM SOUNDEX
CHAR NCHAR SPACE
CHARINDEX PATINDEX STR
CONCAT QUOTENAME STUFF
DIFFERENCE REPLACE SUBSTRING
FORMAT REPLICATE UNICODE
LEFT REVERSE UPPER
LEN RIGHT
LOWER RTRIM

Metadata Functions (T-SQL)

@@PROCID INDEX_COL
APP_NAME INDEXKEY_PROPERTY
APPLOCK_MODE INDEXPROPERTY
APPLOCK_TEST NEXT VALUE FOR
ASSEMBLYPROPERTY OBJECT_DEFINITION
COL_LENGTH OBJECT_ID
COL_NAME OBJECT_NAME
COLUMNPROPERTY OBJECT_SCHEMA_NAME
DATABASE_PRINCIPAL_ID OBJECTPROPERTY
DATABASEPROPERTYEX OBJECTPROPERTYEX
DB_ID ORIGINAL_DB_NAME
DB_NAME PARSENAME
FILE_ID SCHEMA_ID
FILE_IDEX SCHEMA_NAME
FILE_NAME SCOPE_IDENTITY
FILEGROUP_ID SERVERPROPERTY
FILEGROUP_NAME STATS_DATE
FILEGROUPPROPERTY TYPE_ID
FILEPROPERTY TYPE_NAME
FULLTEXTCATALOGPROPERTY TYPEPROPERTY
FULLTEXTSERVICEPROPERTY

 Back to top

XQuery is a language that can query structured or semi-structured XML data. With the xml data type support provided in the Database Engine, documents can be stored in a database and then queried by using XQuery. That can help in understanding the implementation of XQuery in the Database Engine.

XML Data (SQL Server) Explains the support for the xmldata type in the Database Engine and the methods you can use against this data type. The xml data type forms the input XQuery data model on which the XQuery expressions are executed.
XML Schema Collections (SQL Server) Describes how the XML instances stored in a database can be typed. This means you can associate an XML schema collection with the xml type column. All the instances stored in the column are validated and typed against the schema in the collection and provide the type information for XQuery.

 

XQuery Basics Provides a basic overview of XQuery concepts, and also the expression evaluation (static and dynamic context), atomization, effective Boolean value, XQuery type system, sequence type matching, and error handling.
XQuery Expressions Describes XQuery primary expressions, path expressions, sequence expressions, arithmetic comparison and logical expressions, XQuery construction, FLWOR expression, conditional and quantified expressions, and various expressions on sequence types.
Modules and Prologs (XQuery) Describes XQuery prolog.
XQuery Functions against the xml Data Type Describes a list of the XQuery functions that are supported.
XQuery Operators Against the xml Data Type Describes XQuery operators that are supported.
Additional Sample XQueries Against the xml Data Type Provides additional XQuery samples.

More info here W3C specification is available here

Data Encryption in SQL 2014

For the first time in SQL Server, you can encrypt your backups using the native SQL Server backup tool. In SQL Server 2014, the backup tool supports several encryption algorithms, including AES 128, AES 192, AES 256, and Triple DES. You will need a certificate or an asymmetric key when taking encrypted backups. Obviously, there are a number of benefits to encrypting your SQL Server database backups, including securing the data in the database. This can also be very useful if you are using transparent data encryption (TDE) to protect your database’s data files.

Encrypted backup:

To create an encrypted SQL Server backup, there are a few prerequisites that you need to ensure are set up on the SQL Server. Creating the database master key is important because it is used to protect the private key certificate and the asymmetric keys that are stored in the master database, which will be used to encrypt the SQL Server backup with T-SQL.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@$$@O123#123&-WORD’;
GO

The backup encryption process will need to make use of a certificate or asymmetric key to be able to take the backup. The following code creates a certificate that can be used to back up your databases using encryption:
Use Master
GO
CREATE CERTIFICATE T3DBBackupCertificate
WITH SUBJECT = ‘T3 Backup Encryption Certificate’;

You can now take an encrypted backup of your databases. The T-SQL statements back up the T3 database using the certificate you created in the preceding:

BACKUP DATABASE t3
TO DISK = N’E:\Backup\t3_enc.bak’
WITH
COMPRESSION,
ENCRYPTION
( ALGORITHM = AES_256,
SERVER CERTIFICATE = T3DBBackupCertificate
),
STATS = 10
GO

This is a local backup; it’s located in the E:\Backup folder, and the encryption algorithm used is AES_256.

Simple is that 🙂                                    Back to top

OLTP Vs. OLAP

ms-oltp

oltp-tbl

 

OLTP:  is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE).
The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model usually 3rd – NF.

OLAP: is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema) and one fact tbl. Fact constellation schema are two fact tbl.

BI tools :

• Oracle – Siebel Business Analytics Applications
• SAS – Business Intelligence
• SAP – BusinessObjects XI
• IBM – Cognos 8 BI
• Oracle – Hyperion System 9 BI+
• Microsoft – Analysis Services
• MicroStrategy – Dynamic Enterprise Dashboards
• Pentaho – Open BI Suite
• Information Builders – WebFOCUS Business Intelligence
• QlikTech – QlikView
• TIBCO Spotfire – Enterprise Analytics
• Sybase – InfoMaker
• KXEN – IOLAP
• SPSS – ShowCase

ETL tools:

• Informatica – Power Center
• IBM – Websphere DataStage(Formerly known as Ascential DataStage)
• SAP – BusinessObjects Data Integrator
• IBM – Cognos Data Manager (Formerly known as Cognos DecisionStream)
• Microsoft – SQL Server Integration Services
• Oracle – Data Integrator (Formerly known as Sunopsis Data Conductor)
• SAS – Data Integration Studio
• Oracle – Warehouse Builder
• AB Initio
• Information Builders – Data Migrator
• Pentaho – Pentaho Data Integration
• Embarcadero Technologies – DT/Studio
• IKAN – ETL4ALL
• IBM – DB2 Warehouse Edition
• Pervasive – Data Integrator
• ETL Solutions Ltd. – Transformation Manager
• Group 1 Software (Sagent) – DataFlow
• Sybase – Data Integrated Suite ETL
• Talend – Talend Open Studio
• Expressor Software – Expressor Semantic Data Integration System
• Elixir – Elixir Repertoire
• OpenSys – CloverETL

Let me know if you have any further question and your comments will be learning point.

Mehboob

Microsoft Certified Solutions Associate (MCSA)                                  Back to top

 

Encrypted Backup in SQL Server 2014

Encryption for Backups is a new feature introduced in SQL Server 2014 and the benefits of this option are
1.Encrypting the database backups helps secure the data.

2.Encryption can also be used for databases that are encrypted using TDE.

3.Encryption is supported for backups done by SQL Server Managed Backup to Windows Azure, which provides additional security for off-site backups.

4.This feature supports multiple encryption algorithms including AES 128, AES 192, AES 256, and Triple DES

5.You can integrate encryption keys with Extended Key Management (EKM) providers.

The following are pre-requisites for encrypting a backup:

1.Create a Database Master Key for the master database.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomePass@WordStronG123′;
GO
2. Create a certificate or asymmetric Key to use for backup encryption.

Use Master
GO
CREATE CERTIFICATE CertforBackupEncryption
WITH SUBJECT = ‘Certificate for Backup Encryption ‘;
GO
3. Backup the database with encryption:

BACKUP DATABASE [addarr]
TO DISK = N ‘D:\Backup\addarr.bak
WITH
INIT,
COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = CertforBackupEncryption
),
STATS = 10
GO
Restoring the encrypted backup:
SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key. If you are restoring the encrypted backup to a different instance, you must make sure that the certificate is available on that instance. More info

 

Microsoft SQL Server 2014 White Papers

 

Product Documentation

 Back to top

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: