Dynamic Management Views T-SQL : Applies only to SQL Server 2014, SQL Server 2016
SQL Server Operating System (SQLOS)
The following SQL Server Operating System–related dynamic management views are Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
Tutorial: Writing Transact-SQL Statements, just click and learn.
MS SQL Server 2014 HotfiX’s
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.
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.
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.
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.
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.
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.
Describes how to perform client network configuration tasks such as configuring client protocols and creating or deleting a Server Alias.
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.
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.
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.
Describes how database applications can use database mail to send e-mail messages from the Database Engine.
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.
Describes how to use SQL Trace to build a customized system for capturing and recording events in the Database Engine.
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.
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.
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.
Describes how to use the Database Engine Tuning Advisor to analyze databases and make recommendations for addressing potential performance problems.
Describes how the production database administrators can make a diagnostic connection to instances when standard connections are not being accepted.
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.
Describes the capabilities of Service Broker for messaging and queueing applications and provides pointers to the Service Broker documentation.
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.
String Functions (T-SQL)
Metadata Functions (T-SQL)
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.
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.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@$$@O123#123&-WORD’;
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:
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’
( ALGORITHM = AES_256,
SERVER CERTIFICATE = T3DBBackupCertificate
STATS = 10
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
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
• 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.
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.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomePass@WordStronG123′;
2. Create a certificate or asymmetric Key to use for backup encryption.
CREATE CERTIFICATE CertforBackupEncryption
WITH SUBJECT = ‘Certificate for Backup Encryption ‘;
3. Backup the database with encryption:
BACKUP DATABASE [addarr]
TO DISK = N ‘D:\Backup\ ’
ALGORITHM = AES_256,
SERVER CERTIFICATE = CertforBackupEncryption
STATS = 10
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
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014
- Database Backup Encryption
- Idle Connection Resiliency
- In-Memory OLTP – Common Workload Patterns and Migration Considerations
- In-Memory OLTP – SQL Server In-Memory OLTP Internals Overview
- Introducing Microsoft Business Intelligence (BI) Tools
- Microsoft IT: A Case Study on “Hekaton” against RPM – SQL Server 2014 CTP1
- Migrating Content Between Report Servers
- Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
- SQL Server 2014 and Windows Azure Blob Storage Service: Better Together
- Using Power BI in a Hybrid Environment
- Books Online for SQL Server 2014
- Developer Reference for SQL Server 2014
- Installation for SQL Server 2014
- Setup and Servicing Installation
- Upgrade Advisor
- Tutorials for SQL Server 2014
- Microsoft JDBC Driver 4.0 for SQL Server
- Microsoft Drivers for PHP for SQL Server
- Microsoft ODBC Driver for SQL Server
- Microsoft OLE DB Provider for DB2 Version 5.0