MS SQL 2014


SQL Server R Services

So, here we go. I think we have a very interesting fly of though here. First of all, the first question, Still i am trying to undestand how R benefits SQL Server is a bit of a strange question. R does not have to benefit SQL Server. R is a standalone language with all of its perks and downside’s and it seems to work quite well in its own niche.

R Services (In-Database) provides a platform for developing and deploying intelligent applications that uncover new insights. You can use the rich and powerful R language and the many packages from the community to create models and generate predictions using your SQL Server data. Because R Services (In-Database) integrates the R language with SQL Server, you can keep analytics close to the data and eliminate the costs and security risks associated with data movement.

R Services (In-Database) supports the open source R language with a comprehensive set of SQL Server tools and technologies that offer superior performance, security, reliability and manageability. You can deploy R solutions using convenient, familiar tools, and your production applications can call the R runtime and retrieve predictions and visuals using Transact-SQL. You also get the ScaleR libraries to improve the scale and performance of your R solutions.

SQL Server setup, you can install both server and client components.
R Services (In-Database): Install this feature during SQL Server setup to enable secure execution of R scripts on the SQL Server computer.
R scripts, and a new service is created, the SQL Server Trusted Launchpad, to manage communications between the R runtime and the SQL Server instance.

Microsoft R Server (Standalone): A distribution of open source R combined.Both R Services (In-Database) and Microsoft R Server (Standalone) include the base R runtime and packages, plus the ScaleR libraries for enhanced connectivity and performance.

Microsoft R Client is available as a separate, free installer. You can use Microsoft R Client to develop solutions that can be deployed to R Services running on SQL Server, or to Microsoft R Server running on Windows, Teradata, or Hadoop.

SQL Server, on the other hand, is a fairly good RDBMS system, which has everything a user might need from ACID to supporting the storage of fairly large data volumes. What SQL Server lacks, and in my opinion will not gain anytime soon, is a sophisticated way to crunch data for analysis and a way to do machine learning, not even to start speaking of deep learning. It is just not designed for this. As a matter of fact, many RDBMS systems are just not designed for this.

The other problem is that SQL Server costs. A lot, if you don’t know what you are doing or if you know what you are doing but have a lot of data and big intentions.

And now we get to the second discussion – the one on the analytical part of the RDBMS. It does not have to be hard to do, it is just matter of some strategy. Think about it this way – how often does it happen for a user to be needing to analyze ALL of their data? Not really so often. And the actual RDBMS system should not be scaled for this either, since most of them either can’t scale up, or cost a lot to do so. (Yep, some of the vendors make big money out of this – think about SQL Server, for example – functional columnstores in PDW, and just silly remains of functionality in SQL Server)

So, here is an idea: keep your data in RDBMS, if you need to. Fine, RDBMS will guarantee you transactions, recovery, speed and storage. For the analytical part do this: get an appliance server – it is like a toaster, it does one thing but it does it well – and make sure you load plenty of memory on it. Memory is cheap.

Then do some data cleaning and pre-aggregations in your RDBMS – after all you are paying crazy amounts of money for it, so might as well make it work for it
then load the prepared data in the other cheap server, in memory with R and do the real heavy lifting there. If needed, return some results to be written in your RDBMS for later use.
The idea is fairly simple – just give the data owners the option to get a fairly cheap way to crunch the data and they will be happy. Sure, you can count on it, but knowing Microsoft, it will take a long while before it’s useful, and with other providers you still have to do your own heavy lifting. Might as well do the heavy lifting here and now, and get some competitive advantage here and how (instead of waiting for it to come to you later).

Important: Microsoft R Server (Standalone) is a separate option designed for using the ScaleR libraries on a Windows computer that is not running SQL Server.
However, if you have Enterprise Edition, we recommend that you install Microsoft R Server (Standalone) on a laptop or other computer used for R development, to create R solutions that can easily be deployed to an instance of SQL Server that is running R Services (In-Database).

Here is some useful link:

Monitoring-R-Services

What is new in SQL Server Machine Learning Services?

SQL Server R Services

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

Mehboob
— Microsoft Certified Solutions Associate (MCSA)

05/01/2017

What are the steps involved in a simple upgrade to SQL Server 2014?  I am currently running an older version of SQL Server and want to do an in place upgrade.

Solution

In this tip, we will demonstrate an upgrade to SQL Server 2014.

Note: Before starting make sure you are connected to a network and have Internet access.

After you launch the SQL Server setup, the SQL Server Installation Center window will display. Along the left side of the window are the categories showing how the SQL Server Installation Center is organized. On the right side of the window are different actions the installer can take. Notice that some of the items allow the installer to view documentation while other items will make changes to the system. By default the Planning category is displayed.

Quick Guide to Upgrading to SQL Server 2014

Clicking on Installation on the left side will display the different installation options. For this tip, we will click on “Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012”. This will launch the Upgrade to SQL Server 2014 application.

Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012

The following pop-up window might appear several times throughout the installation process.

Upgrade to SQL Server 2014

The Upgrade to SQL Server 2014 application lists the steps it will follow on the left side of the window. Enter the product key and click “Next >”.

MSSQLTips.com Sample Image

Review the license terms, click on “I accept the license terms”, and then click “Next >”.

"I accept the license terms"

The upgrade application will download, extract and install files needed to carry out the installation process. Click on “Next >” to continue.

The upgrade application will download, extract and install files needed to carry out the installation process.

Select the SQL Server instance to modify. Click on “Next >” to continue.

Select the SQL Server instance to modify

Sometimes a “Please Wait…” pop-up box might appear.

Sometimes a "Please Wait..." pop-up box might appear.

The next step is the feature selection. In the example shown below, all of the features will be upgraded. Click on “Next >” to continue.

In the example shown below, all of the features will be upgraded

The Instance Configuration screen allows the installer to specify the instance ID. This screen will also display other installed instances. In this example, the MSSQLSERVER instance on SQL Server 2012 will be upgraded to SQL Server 2014.

the MSSQLSERVER instance on SQL Server 2012 will be upgraded to SQL Server 2014.

The Server Configuration screen shows the services to be installed, the service account name and the Startup Type. If you are installing SQL Server 2014 on a device with limited resources then the Startup Type should be set to Automatic (Delayed Start) instead of Automatic. Click on “Next >”.

The Server Configuration screen shows the services to be installed

The Full-text Upgrade screen provides three Full-Text upgrade options (import, rebuild and reset).

The Full-text Upgrade screen provides three Full-Text upgrade options (import, rebuild and reset).

The Feature Rules screen will execute a rule check to make sure the upgrade process should successfully.

The Feature Rules screen will execute a rule check to make sure the upgrade process should successfully.

The Ready to Upgrade screen displays all of the features and prerequisites to be installed or upgraded. At this point, the installer can still go back to make changes or quit the process. Click on “Upgrade” to begin the upgrade of the SQL Server 2014 components.

Click on "Upgrade" to begin the upgrade of the SQL Server 2014 components.

The upgrade progress bar tracks the status of the upgrade.

The upgrade progress bar tracks the status of the upgrade.

Upon completion of the installation, click on “Close” to exit the Upgrade to SQL Server 2014 application.

Upon completion of the installation, click on "Close" to exit the Upgrade to SQL Server 2014 application.

The SQL Server Installation Center can now be closed.

 

MS SQL database backup file

SQL Server 2016 Community Technology Preview 2 (CTP2) through current version).

To make your database secured and protective; it helps you to restore your inaccessible files of the main database when any type of corruption or damage occurs. But, what if backup also corrupt, while attempting to restore database from backup file. There could be multiple reasons behind such disaster situation, here in this write-up you will get to know about the reason and solutions behind such case, where SQL Server user faces corruption.

— SQL Server database backup and not able to restore their databases.

Transact-SQL Syntax Conventions

SQL backup files are basically a replica of your original SQL database, which can be located in different locations on the system. There could be multiple reasons of inaccessible backup file. Here are some most common causes of damaged SQL BAK files:

— Virus attack
— Abrupt system shutdown
— Use of a wrong driver
— Bad sectors in your system’s hard disk
— Sudden removal of a selected tables, records, and procedures
— unconventional functioning of Hard disk
— Improper shutdown of application
— Wrong database synchronization
— System crash
— corrupt database system rules and tables

The most common error message during restoration of database is: ‘Backup or restore operation terminating abnormally.’ A Backup restoration error occurs when a filemark in the backup device could not be read. There could be multiple causes of when a user encounters a filemark error. The most common reasons are:

— A media failure may arise on the same device where the backup is stored
— A write failure may occur while creating the backup file
— Loss of connectivity may arise while creating a network backup
— A failure in the Input/Output path occurs in the disk just after successful write to the disk

Manual Solution:-
After backup restore error the first thing you could do is to check whether all the sets of backup have issues or just some sets have issues. It might be possible that only some sets of backup have issues due to which you are getting restore error. In order to retrieve other backup sets from the device, you need to specify the file number. In case, there are multiple backup sets available on a single device, then to determine the usable backup, you can run the following query:

RESTORE HEADERONLY FROM DISK='<Backup Location>’

If you got the usable set from the disk, copy it to another drive for usage and try to restore the damaged files with the help of SQL restore commands. Here are some of the SQL commands that you can use to restore corruption in your SQL database backup, the following essential backup concepts:

Backup Types

Transaction Log Truncation

Formatting Backup Media

Working with Backup Devices and Media Sets

Restoring SQL Server Backups

To recover a database use the following command. This will put your database in the “restoring” state

RESTORE DATABASE <DB Name> FROM DISK='<Backup Location>’ WITH FILE = <FileNumber>

— Write the backup set number instead of ‘FileNumber’ that you want to restore.

The following command will take the database, which is in ‘restoring’ state and make it available for end users.

RESTORE LOG <DB Name> FROM DISK = ‘<Backup Location>’
WITH RECOVERY

“Before you can create the first log backup, you must create a full backup ”

The above mentioned commands are used to restore corrupt backup file of SQL database. However, these corrupt backup recovery solutions provided by Microsoft are not applicable for deep corruption cases. In order to restore your highly damaged or corrupt SQL backup database you can always choose a third party SQL backup recovery software. These professional utilities are designed to restore data from a corrupt (.BAK) SQL backup file.

BACKUP DATABASE AdventureWorks2012
TO DISK=’X:\SQLServerBackups\AdventureWorks1.bak’,
DISK=’Y:\SQLServerBackups\AdventureWorks2.bak’,
DISK=’Z:\SQLServerBackups\AdventureWorks3.bak’
WITH FORMAT,
MEDIANAME = ‘AdventureWorksStripedSet0’,
MEDIADESCRIPTION = ‘Striped media set for AdventureWorks2012 database;
GO

Third party applications have functions to restore SQL backup file due to all above mentioned reasons. Before buying any professional backup recovery tool, you need to choose the most reliable one. For that you should use the online demo versions of the backup recovery applications to test their efficiency.

For more information, see: Full File Backups (SQL Server) and Back Up Files and Filegroups (SQL Server).

For more information and examples, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. For a tutorial, see Tutorial: SQL Server Backup and Restore to Windows Azure Blob Storage Service.
Security: Beginning with SQL Server 2012, the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. It is still possible to restore backups created with passwords.

Permissions: BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Ownership and permission problems on the backup device’s physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Such problems on the backup device’s physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

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

Mehboob
— Microsoft Certified Solutions Associate (MCSA)

 

 

MS SQL Server 2014 HotfiX’s  use syntax i.e. Select @@version

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)

Back to top

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

 

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

 

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:Backupaddarr.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