Getting started with SQL Server 2014 | Download and Install Free (Express) or Full version

May 19, 2015 2 comments

How to download SQL Server?
What are the available options and versions?
Isn’t there any Free version to play with, learn and practice SQL?

I get emails regarding these question many a times from my readers, sometime from my friends and old colleagues too. So I thought to collate all this information into this single post so that people won’t have to search here and there on internet and finally end up nowhere or to any junk sites.

 
Currently the latest version of SQL Server from Microsoft is SQL Server 2014 released last year on 1st April 2014, and its first Service Pack (SP1) was released just few days back (15th May 2015). You have following options to choose from:

1. SQL Server Full version contains the Database Engine with whole BI suit and is a paid version and you have to buy license key from your MSDN Subscriptions. It is also available for free download for evaluation of 180 days, but post that you have to buy a license key.

2. SQL Server Express is a Free version that can be used by anyone, like students, small setups and companies, etc. It is a lite version which contains the Database Engine with some limitations like: max size of a DataBase is set to 10GB (but you can create multiple databases), no SQL Agent, single CPU utilization & max 1 GB RAM allocation. I think this is a good package and configuration you are getting it for free. And a best option for students and for beginners in SQL if they want to learn and practice SQL Querying without investing anything.

 
–> Check this video on how to download SQL Server with above two options:

You can also directly download the Express bits from here:

– SQL Server 2014 Express and Tools (DB Engine with SSMS): x64 | x86

– SQL Server Management Studio (SSMS only) 2014: x64 | x86

– SQL Server 2014 Express and Tools with Advanced Services: x64 | x86

… with the Advanced Services option you get extra tools like Fulltext Search and Reporting Services.

 
After downloading the bits from Microsoft site you are now ready to install SQL Server.

–> Check this video on how to install SQL Server (its very easy, but just in case):

 
–> After install completes you can download the sample database AdventureWorks2014 for SQL Server 2014 from CodePlex and restore the Backup (.BAK) file to SQL Server, click on the image below:

AdventureWorks2014

SQL Recovery utility by SysTools | easily Recover your corrupt Database in few clicks


In business organizations, most of the application’s back end operations rely on database systems like Microsoft SQL Server. SQL Server helps to manage the database which ultimately houses all the storage information. SQL Server is responsible for all the back end operations for hundreds of applications and so we can easily retrieve information through a series of SQL queries. Nevertheless, if a SQL database gets corrupted; will the processes of the entire organization come to stand still? Well if the data maintained in the database are not recovered, then there are chances of the disruption of the processes concerned with the entire organization. However, if you are lucky enough to get through a software like SQL Recovery then things are really going to shape in a good way and you will be able to recover all your data items from any corrupted database.

–> Here is a closer look on the features of “SQL Recovery” utility to realize what it offers to users:

The SQL Recovery utility from [SysTools Group] proves to be a transformational approach for recovering the corrupted database of SQL Server. Being completely a Windows based tool, it has been developed with the basic motive of recovering damaged data from the MDF and NDF files of SQL server by saving the extracted data on Windows Operating system. Later, you can export the extracted data directly into SQL server. It is just simple to rely on this masterpiece utility that gets your work done in some seconds. You get to recover the tables, rows, columns while not worrying about any data loss. To take a lead on the recovery process, let us first get started with some notable features of the application.

–> Basic Potentialities of the Recovery Tool:
1. Provides a convenient option to save the scan of the corrupted database as .str file format that can be handy at times when there is any kind of interruption in the transaction process of SQL Server.
2. The automatic detection of SQL Server version helps to run the recovery process without any kind of confusion.
3. Often users or system administrators employ DBCC CHECKDB command to resolve the corruption related issues. However, there are occasions when the DBCC CHECKDB fails to perform and in such situations user can definitely trust on this recovery tool.
4. Supports the recovery of XML data if the user has created XML type variables or columns on SQL Server.

–> Functionality in Details:
Some of the functionalities of the program are completely beneficial to users. Have a look on these functional aspects, to know more about this software.

1. Scanning options: For recovering the MDF and NDF files, users are provided with two feasible options (as shown in image below):
– Quick scan: select this option, for faster scanning of MDF files.
– Advance scan: If your files are highly corrupted then choose this option.

scan-mode
 
2. Preview the Database Items: Once the MDF and the NDF database of SQL server are recovered, you will get a preview of all the contents of the database. Users will get to see all the repaired data items like tables, views, triggers, etc.

preview-sql-data
 
3. Flexible Export Options: For exporting the recovered database, users can select any one of the option
(1) SQL Server Database and
(2) SQL Server Compatible Scripts

– Export Directly To Live SQL Server: If you are interested to place your files directly into the Live SQL Server then provide the details of database such as Server name, username, password, etc. and export the data conveniently to the database of server without any data loss.

– Export as SQL Server Compatible Scripts: If you select this option for exporting the recovered database to SQL Server, then a .sql file is created that can be easily saved on your system. This file will help you to restore data into the server anytime.

export-option
 
4. Export MDF with/without schema: While exporting the recovered MDF database, the software facilitates users to export the database with only the schema of tables, stored procedures, etc. However, you also have the option to export MDF data with both schema and data.

schema-data
 
5. Export Deleted Records: Another attributed feature of the tool is that while exporting the database to the server, you have the option to export the deleted rows, tables, or column again to the database. A prompt appears on the screen, if you are interested for exporting the deleted data to the database, select yes otherwise click on no.

export-deleted-records
 
–> The SQL Recovery utility can be downloaded from the SysTools website (both Trial & Licensed version): http://www.systoolsgroup.com/sql-recovery.html

The demo version of the SQL Recovery utility is available for users to test its functionality in details. However, with the free trial version you will be able to recover all the items of the corrupted SQL database and onlypreview them. To export or save the recovered database, you need to go for the licensed version of the application.

The setup file is of 6.10 MB and it gets easily installed on the system. The latest version 6.1 has come up with features that are more promising and provides capabilities of improved speed and accuracy while working on the application. SQL Recovery tool is completely compatible with Windows 8.1 and all other previous versions.

–> Working Areas:
– You cannot add multiple MDF files with the software. However, there is an option to add multiple files and folders when you select a NDF file
– The software does not provide a process summary report of what all items are exported to the SQL Server. Users do not get an idea about the details of items exported to SQL Server database

–> Wrapping it up:
Offering an efficient recovery process, strong performance, and incredible operability the SQL Recovery Tool comes out successfully to delivers on all fronts. Further, the array of options and the user specific customizations provided by the software rightfully claims it as the best one among its competitors.

Microsoft announced SQL Server 2016 – New features and enhancements


On Monday, 4th May 2015 Microsoft at Ignite event announced the new version of SQL Server i.e. SQL Server 2016, which will be available for Public Preview this summer.
 

>> MSDN Blog announcement: http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx

>> Microsoft SQL Server 2016 official page: https://www.microsoft.com/en-us/server-cloud/products/sql-server-2016
 

This version of SQL Server is going to be a major release with new features and will also overcome some limitations of SQL Server 2014.
 

SQLServer2016_02
 

-> New Performance Enhancements:
– In-memory OLTP enhancements: Greater T-SQL surface area, terabytes of memory supported and greater number of parallel CPUs, provide up to 30x faster Transactions, more than 100x faster Queries than disk-based relational databases and Real-time Operational Analytics.
– Query Data Store: Monitor and optimize query plans with full history of query execution
– Native JSON: Parsing & storing of JSON as relational data & exporting relational data to JSON, as it is becoming a popular format to store NoSQL/Unstructured data.
 

–> Security Upgrades:
– Always Encrypted: Help protect data at rest and in motion with the master key residing with the application & no application changes required
– Row Level Security: Customers can implement Row-level Security on databases to enable implementation of fine-grained access control over rows in a database table for greater control over which users can access which data.
– Dynamic Data Masking: Real-time obfuscation of data to prevent unauthorized access
 

–> Even Higher Availability, with Enhanced AlwaysOn:
– Up to 3 synchronous replicas for auto failover across domains, for more robust High Availability and Disaster Recovery
– Round-robin load balancing of replicas
– DTC & SSIS support
– Automatic failover based on database health
 

–> Hybrid Cloud Solution:
– Stretch Database: Stretch operational tables in a secure manner into Azure for cost effective historic data availability, that lets you dynamically stretch your warm and cold transactional data to Microsoft Azure.
– Azure Data Factory integration with SSIS
 

–> Deeper Insights Across Data
– PolyBase: Manage relational & non-relational data with the simplicity of T-SQL
– Enhanced SSIS: Designer support for previous SSIS versions and support for Power Query
– Built-in Advanced Analytics: Bringing predictive analytic algorithms directly into SQL Server.
 

>> Check the SQL Server 2016 Datasheet here for more information on this: http://download.microsoft.com/download/F/D/3/FD33C34D-3B65-4DA9-8A9F-0B456656DE3B/SQL_Server_2016_datasheet.pdf
 

SQLServer2016_01


What is SQL, PL/SQL, T-SQL and difference between them

May 3, 2015 2 comments

Today I got an email from a student who is pursuing his Bachelors degree in Computer Application (BCA). He visited my blog and sent me an email regarding his confusion with terms like SQL, T-SQL, PL/SQL, and asked me what is differences between them and how are they related? I had a chat with him and told the basic differences, but he further asked me how they are related to Microsoft SQL Server, Oracle, MySQL, etc? As he is studying SQL only based upon Oracle in his course curriculum, these all terms were not clear to him, so I cleared all his doubts while chatting with him.

After a while I had a same reminiscence that when I was a student I also had these doubts and confusions, and there was nobody to guide me, but I gradually came to know about this and it took some time. Thus, I’am taking this opportunity to put all these things together here in a single blog post for my readers (specially students) and for my reference as well.

–> SQL: stands for Structured Query Language and is pronounced as Sequel, and in early days it was also known as SEQUEL only.
– IBM in early 1970s developed SEQUEL which stands for Structured English QUEry Langauge for their RDBMS. The acronym was later changed to SQL, as SEQUEL was already trademarked by some UK based aircraft company.

–> PL/SQL: stands for Procedural Language/Structured Query Language and is used with Oracle database to create PL/SQL units such as Procedures, Functions, Packages, Types, Triggers, etc. which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.
– Oracle in 1970s known as “Relational Software” saw SQL potential and influenced by Boyce, Codd and Chamberlin developed their own RDBMS product which was commercially available as Oracle Database. Oracle is supported on many Operating Systems like Windows, Linux, Solaris, AIX, OpenVMS, etc. [Oracle Database]

–> DB2: IBM during early 1980s made SQL commercially available with its product known as IBM DB2 from its prototype “System R”. [IBM DB2]

–> SQL Standardization: Later in 1986 SQL was adopted as a Standard by ANSI (American National Standards Institute) as SQL-86, and today the latest Standard is known as SQL:2011
– A SQL should support following:
– – Language elements: Clauses, Expressions, Predicates, Queries, Statements.
– – Operators: =, , >, =, <=, BETWEEN, LIKE, IN, NOT IN, IS, IS NOT, AS, etc.
– – Conditional expressions: CASE, IF ELSE
– – Queries: which include SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, etc.
– – Data Types: Numeric, Char, Bit, Date and Time
– – NULL or 3VL (Three value Logic)
– – DDL, DML, TCL

–> T-SQL: stands for Transact-SQL, and is Sybase & Microsoft’s proprietary extension. T-SQL is very similar to PL/SQL, one can create T-SQL units such as Procedures, Functions, Types, Triggers, etc. as mentioned above.
– In 1987 Sybase shipped their first RDBMS product known as Sybase SQL Server. [Sybase]
– In 1988-89 Microsoft had an agreement with Sybase and ported the Sybase RDBMS to OS/2 platform and marketed it as Microsoft SQL Server 1.0, which was equivalent to Sybase SQL Server 3.0. [Microsoft SQL Server]
– In 1993 Sybase and Microsoft dissolved their partnership, and Microsoft bought the SQL Server code base from Sybase and both went different streams to develop their own product.
– Till SQL Server 2000 Microsoft has Sybase code base, and this was completely written in SQL Server 2005.
– Microsoft SQL Server is only supported on Windows Operating Systems.

–> MySQL: was founded by a Swedish Company MySQL AB in 1995 and is the most widely used open-source RDBMS. The MySQL development project has made its source code available under the terms of the GNU General Public License. MySQL does not currently comply with the full SQL standard. In 2008 Oracle corporation completely acquired MySQL. MySQL is supported on many Operating Systems like Windows, Linux, Solaris, OS X and FreeBSD. [MySQL.com]

–> FoxPRO and dBase: were very popular DataBase Management System (DBMS) products in mid 1980s and 1990s. They lack some RDBMS features and are out of support now, but are still being used in various legacy systems.
– FoxPRO is supported by Microsoft and was a text-based Procedural programming language and DBMS, for MD DOS, Windows, and UNIX. Microsoft Visual FoxPRO 9.0 being the latest and probably the last version published in 2007. [FoxPRO msdn]
– dBase was a very popular DBMS package including core Database engine, a Query system, a Forms engine and a xBase programming language with *.dbf file format. [dBase.com]

–> There are many other popular SQL Products/System softwares available in market, and major of them are:
1. Oracle database
2. Microsoft SQL Server
3. IBM DB2
4. MySQL
5. PostgreSQL
6. Teradata

–> Here is a complete timeline that shows SQL and how it got evolved as different Products by different Vendors/Companies (click on the image to expand):

RDBMS_timeline.svg

Please let me know your thoughts and if you like this article or not and want to refine/add more information to it, thanks!

Preparing for 70-462 Exam : Administering Microsoft SQL Server 2012 Databases | Certification

May 1, 2015 2 comments

I’m from a Database development background and working mostly with T-SQL code only. I passed Exam 70-461 back in Nov-2012, and now I’m planing to give 70-462 Exam i.e. Administering Microsoft SQL Server 2012 Databases.

This exam is intended for Database Administrators who perform:
– Installation
– Maintenance
– Configuration tasks
– and other responsibilities like:
– – setting up Database systems
– – making sure those systems operate efficiently
– – regularly storing, backing up, and securing data from unauthorized access

Thus being a Database Developer (primarily) this exam is going to be the most toughest and trickiest exam in this series as I don’t have DBA experience and haven’t worked on anything in Database Administration yet.

For preparation I’m referring to Training Kit (Exam 70-462) Administering Microsoft SQL Server 2012 Databases book.

–> The Exam is divided into 6 modules:
1. Install and Configure
2. Maintain Instances and Databases
3. Optimize and Troubleshoot
4. Manage Data
5. Implement Security
6. Implement High Availability

You need to brush up on following Skills in the these Modules:
-:Check links with the below items for more information:-

1. Install and Configure | 19%
– Plan installation
– Install SQL Server and related services
– Implement a migration strategy
– Configure additional SQL Server components
– Manage SQL Server Agent
– PREP Links: Understanding surface area configuration | Hardware and software requirements for installing SQL Server 2012 | Quick-start installation of SQL Server 2012

2. Maintain Instances and Databases | 17%
– Manage and configure databases
– Configure SQL Server instances
– Implement a SQL Server clustered instance
– Manage SQL Server instances
– PREP Links: ALTER DATABASE file and filegroup options (Transact-SQL) | Contained databases | Data compression

3. Optimize and Troubleshoot | 14%
– Identify and resolve concurrency problems
– Collect and analyze troubleshooting data
– Audit SQL Server instances
– PREP Links: blocked process threshold server configuration option | Configure login auditing (SSMS) | Data collection

4. Manage Data | 19%
– Configure and maintain a back-up strategy
– Restore databases
– Implement and maintain indexes
– Import and export data
– PREP Links: Back up and restore of SQL Server databases | File restores (full recovery mode) | DBCC INDEXDEFRAG (Transact-SQL)

5. Implement Security | 18%
– Manage logins and server roles
– Manage database permissions
– Manage users and database roles
– Troubleshoot security
– PREP Links: Server-level roles | Permissions (database engine) | Database-level roles

6. Implement High Availability | 12%
– Implement AlwaysOn
– Implement Database Mirroring
– HADR (High Availability and Disaster Recovery)
– PREP Links: AlwaysOn Availability Groups (SQL Server) | Microsoft SQL Server AlwaysOn solutions guide for HADR | AlwaysOn architecture guide: Building a HADR solution by using AlwaysOn Availability Groups


-–> You can visit following Study materials to prepare for this Exam:

Microsoft Link for this Certification: http://www.microsoft.com/learning/en-us/exam-70-462.aspx

Book on AMAZON.com: Training Kit (Exam 70-462) Administering Microsoft SQL Server 2012 Databases


Will try to update this blog post or will put up a new post with my learning while preparing for this exam.

All The Best!!!

Follow

Get every new post delivered to your Inbox.

Join 440 other followers