What is SQL Server?
SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is primarily designed and developed to compete with MySQL and Oracle database.
SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL (Transact-SQL).
T-SQL is a Microsoft propriety Language known as Transact-SQL. It provides further capabilities of declaring variable, exception handling, stored procedure, etc.
SQL Server Management Studio (SSMS) is the main interface tool for SQL Server, and it supports both 32-bit and 64-bit environments.
SQL Server Express 2014 is a powerful and reliable free data management system that delivers a rich and reliable data store for lightweight Web Sites and desktop applications. The Express edition is free and ideal for learning, developing, powering desktop, web & small server applications. The SQL Server 2014 Express release includes the full version of SQL Server 2014 Management Studio.
SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft.
Similar to other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interacting with the relational databases. SQL server is tied to Transact-SQL, or T-SQL, the Microsoft’s implementation of SQL that adds a set of proprietary programming constructs.
SQL Server works exclusively on Windows environment for more than 20 years. In 2016, Microsoft made it available on Linux. SQL Server 2017 became generally available in October 2016 that ran on both Windows and Linux.
SQL Server Architecture
The following diagram illustrates the architecture of the SQL Server:
SQL Server consists of two main components:
- Database Engine
The core component of the SQL Server is the Database Engine. The Database Engine consists of a relational engine that processes queries and a storage engine that manages database files, pages, pages, index, etc. The database objects such as stored procedures, views, and triggers are also created and executed by the Database Engine.
The Relational Engine contains the components that determine the best way to execute a query. The relational engine is also known as the query processor.
The relational engine requests data from the storage engine based on the input query and processed the results.
Some tasks of the relational engine include querying processing, memory management, thread and task management, buffer management, and distributed query processing.
The storage engine is in charge of storage and retrieval of data from the storage systems such as disks and SAN.
Under the relational engine and storage engine is the SQL Server Operating System or SQLOS.
SQLOS provides many operating system services such as memory and I/O management. Other services include exception handling and synchronization services.
SQL Server Services and Tools
Microsoft provides both data management and business intelligence (BI) tools and services together with SQL Server.
For data management, SQL Server includes SQL Server Integration Services (SSIS), SQL Server Data Quality Services, and SQL Server Master Data Services. To develop databases, SQL Server provides SQL Server Data tools; and to manage, deploy, and monitor databases SQL Server has SQL Server Management Studio (SSMS).
For data analysis, SQL Server offers SQL Server Analysis Services (SSAS). SQL Server Reporting Services (SSRS) provides reports and visualization of data. The Machine Learning Services technology appeared first in SQL Server 2016 which was renamed from the R Services.
SQL Server Editions
SQL Server has four primary editions that have different bundled services and tools. Two editions are available free of charge:
SQL Server Developer edition for use in database development and testing.
SQL Server Expression for small databases with the size up to 10 GB of disk storage capacity.
For larger and more critical applications, SQL Server offers the Enterprise edition that includes all SQL server’s features.
SQL Server Standard Edition has partial feature sets of the Enterprise Edition and limits on the Server regarding the numbers of processor core and memory that can be configured.
SQL Server 2014
Before downloading SQL Server 2014 Express version, let’s understand the various files and its importance.
LocalDB is a lightweight version of Express that has all its programmable features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. Use this if you need a simple way to create and work with databases from code. It can be bundled with Application and Database Development tools like Visual Studio and or embedded with an application that needs local databases. You will see two files one for the 32-bit system (ENU\x86\SqlLocalDB.msi) and other for the 64-bit system (ENU\x64\SqlLocalDB.msi).
This package includes the SQL Server database engine only. Best suited to accept remote connections or administer remotely. You will see two files one for 32-bit system (SQLEXPR_x86_ENU.exe) and other for 64-bit system (SQLEXPR_x64_ENU.ex
Express with Tools (SQLEXPRWT)
This package contains everything needed to install and configure SQL Server as a database server including the full version of SQL Server 2014 Management Studio. Choose either LocalDB or Express depending on your needs above. You will see two files one for a 32-bit system (SQLEXPRWT_x86_ENU.exe) and other for a 64-bit system (SQLEXPRWT_x64_ENU.exe
SQL Server Management Studio Express (SQLManagementStudio)
This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure, a full version of SQL Server 2014 Management Studio, etc. If you already have the database and only need the management tools, download this one. You will see two files one for a 32-bit system (SQLManagementStudio_x86_ENU.exe) and other for a 64-bit system (SQLManagementStudio_x64_ENU.
Express with Advanced Services (SQLEXPRADV)
This package contains all the components of SQL Server Express including the full version of SQL Server 2014 Management Studio. This is a larger download than “with Tools,” as it also includes both Full-Text Search and Reporting Services. You will see two files one for the 32-bit system (SQLEXPRADV_x86_ENU.exe) and other for the 64-bit system (SQLEXPRADV_x64_ENU.ex
SQL Server In-Memory OLTP Overview
SQL Server 2014 includes an In-Memory OLTP engine code named Hekaton. This engine provides a lock and latch free environment for OLTP workloads. It is fully integrated into SQL Server and accessed using standard T-SQL. Contrary to other products in the market, Hekaton is not a separate system, it is part of the SQL Server Database Engine. Hekaton enables you to use both disk based tables and Memory-Optimized Tables together in the same queries and stored procedures.
Memory-Optimized tables can be defined as durable, so data persists on server restart and with schema only duration to preserve table definition alone, useful for ETL transformations and data staging.
Furthermore, Hekaton introduces Natively Compiled Stored procedures which are Transact-SQL Stored Procedures compiled to native code, to interact with Memory-Optimized Tables even more efficiently.
You can read more about Memory-Optimized tables and Natively Compiled Stored Procedures in my previous tips.
SQL Server 2014 Cloud Computing Enhancements
Since this version of SQL Server was designed with the premise of being a platform for a Hybrid Cloud it has some new and exciting features.
An on-premises SQL Server can have databases in which its data and log files are stored on Windows Azure Storage. This means that you can move your storage into the cloud while keeping all the transaction processing on your local server. Furthermore you can enable Transparent Data Encryption on databases while keeping the encryption key on the local server for added security.
This release also includes the possibility to Backup and Restore to/from a URL directly with SQL Server Management Studio.
SQL Server AlwaysOn Improvements
As I told you before, this version of SQL Server was conceived as a platform for a Hybrid Cloud. So the Engineers at Microsoft had the wonderful idea of allowing Hybrid High Availability solutions like the creation of Azure replicas for Availability Groups with a simple Add Azure Replica Wizard to guide you through the process.
Also readable secondary replicas now remain available for reading on cluster quorum loss or when a primary replica is down.
Furthermore, the maximum number of replicas has been increased from 4 to 8.
SQL Server 2014 includes the possibility to use Cluster Shared Volumes as cluster shared disks in Windows Server 2012 and above on Failover Cluster Instances.
Even new Dynamic Management Views have been added to increase ease of troubleshooting. You can read about them on my previous tip “Understanding Dynamic Management Views in SQL Server 2014”.
SQL Server Performance Enhancements
Several features have been added regarding performance. Please see the following items below.
SQL Server Transactions with Delayed Durability
In order to reduce latency, transactions can be defined as delayed durable, which means that transaction returns control to the client before the Transaction Log record is written to disk.
This can be defined at the database level, COMMIT level, or ATOMIC block level in Natively Compiled Stored Procedures. Also the following Stored Procedure sys.sp_flush_log is included to flush the Transaction Log to disk in order to make previously committed transactions durable with delayed durability.
Here is a sample code.
// Set DB option to allow transactions with delayed durability. USE [master] GO ALTER DATABASE [TestDB] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT GO //--------------------------------------------------------------- BEGIN TRANSACTION UPDATE dbo.SomeTable SET SomeColumn = @SomeData WHERE SomePk = @SomeID // set current transaction with delayed durability COMMIT TRANSACTION WITH(DELAYED_DURABILITY = ON ) // Flush transaction log to disk EXEC sys.sp_flush_log
SQL Server Query Optimizer
SQL Server 2014 substantially improved the component of the engine that creates and optimizes query plans. Stay tuned for more.
SQL Server Table and Index Operations
In SQL Server 2014 single partitions can be rebuilt and additional partition switching and index rebuild operations can be performed while the table is online.
Furthermore the ability to manage lock priority of online operations for tables and indexes has been added by allowing you to use WAIT_AT_LOW_PRIORITY option. This option enables you to specify operation locks time maximum duration and abort conditions. Here is some additional information:
|MAX_DURATION||Is the wait time in minutes the online operation will wait at low priority|
|ABORT_AFTER_WAIT||These are the actions to be taken by the online operation when it is blocked beyond MAX_DURATION value.
NONE: Continue waiting for the lock with normal priority.
SELF: Leaves current operation without taking any action.
BLOCKERS: Kills transactions that block the online operation.
Here is a sample code.
ALTER INDEX ALL ON SomeTable REBUILD WITH (ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES ABORT_AFTER_WAIT = BLOCKERS ) ) )
SQL Server 2014 Incremental Option for CREATE STATISTICS
SQL Server 2014 permits statistics creation per partition by setting the INCREMENTAL option to ON in the CREATE STATISTICS statement. Here is an example.
CREATE STATISTICS SomeStatistic ON dbo.SomeTable ( SomeField ) WITH FULLSCAN, INCREMENTAL = ON; GO
SQL Server 2014 Buffer Pool Extension
This feature enables SQL Server to be configured to use a SSD disk as an extension for the Database Buffer Pool in order to reduce latency.
SQL Server 2014 Resource Governor
With the SQL Server 2014 release, we can set constraints on the physical IO operations. The MAX_OUTSTANDING_IO_PER_VOLUME argument has been added to the ALTER RESOURCE GOVERNOR statement allowing us to set the maximum outstanding I/O operations per disk volume giving us the ability to tune the SQL Server instance IO according to the disk IO characteristics.
Also we can set the disk IO thresholds for disk volumes on Resource Pools with these two new settings: MAX_IOPS_PER_VOLUME and MIN_IOPS_PER_VOLUME. These options set the maximum and minimum IO operations per second respectively.
SQL Server 2014 Columnstore Index Improvements
SQL Server 2014 has added updatable Clustered Columnstore Indexes.
Another feature is the capability to compress Columnstore indexes, both clustered and nonclustered even more. For this, two arguments have been added to the REBUILD option of the ALTER INDEX statement in order to handle Columnstore Indexes compression:
|COLUMNSTORE||Default Columnstore indexes Compression.|
|COLUMNSTORE_ARCHIVE||Compress Columnstore indexes even more.|
Also improvements in Batch processing mode have been made. Stay tuned for more information.
SQL Server 2014 Security Enhancements
SQL Server 2014 includes the following security improvements: Backup Encryption and Permissions.
SQL Server 2014 Backup Encryption
Now SQL Server backup data can be encrypted during the backup creation with several new encryption algorithms like AES 128, AES 192, AES 256 and 3DES. I will cover this topic on a upcoming tip.
Permission Changes in SQL Server 2014
The following permission has been added in SQL Server 2014:
|CONNECT ANY DATABASE||Grants Connect permission for users on Databases that may be created in future.|
|IMPERSONATE ANY LOGIN||Allows or block Login impersonation. Is useful to block impersonation from high privileged logins.|
|SELECT ALL USER SECURABLES||Server level permission. Allows logins to perform SELECT Statements in all databases that the login has CONNECT permission.|
|ALTER ANY DATABASE EVENT SESSION||Database level permission|
Cognosys Provides Hardened images of SQL Server Express 2014 on the cloud ( AWS marketplace, Azure and Google Cloud Platform).
Default password for sa is set as Passw@rd123 for SQL Server.
Please change the password after first login.
Secured SQL Server 2014 Express Full IIS on Windows 2012 R2
Sql Server 2014 Express on cloud for AWS
Major Features of SQL Server Express 2014
Microsoft introduced some significant enhancements in SQL Server 2014—especially with In-Memory OLTP. However, as you might expect after such a short release cycle, not every subsystem has been updated; there are no major changes to SQL Server Integration Services (SSIS), SQL Server Replication Services, or SQL Server Reporting Services (SSRS). Nonetheless, there are plenty of significant enhancements. Here are 10 new features in SQL Server 2014.
1. In-Memory OLTP Engine
SQL Server 2014 enables memory optimization of selected tables and stored procedures. The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays. Microsoft states that customers can expect performance to be up to 20 times better than with SQL Server 2012 when using this new feature. For more information, check out “Rev Up Application Performance with the In-Memory OLTP Engine.”
2. AlwaysOn Enhancements
Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure.
3. Buffer Pool Extension
SQL Server 2014 provides a new solid state disk (SSD) integration capability that lets you use SSDs to expand the SQL Server 2014 Buffer Pool as nonvolatile RAM (NvRAM). With the new Buffer Pool Extensions feature, you can use SSD drives to expand the buffer pool in systems that have maxed out their memory. Buffer Pool Extensions can provide performance gains for read-heavy OLTP workloads.
4. Updateable Columnstore Indexes
When Microsoft introduced the columnstore index in SQL Server 2012, it provided improved performance for data warehousing queries. For some queries, the columnstore indexes provided a tenfold performance improvement. However, to utilize the columnstore index, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction with the new updateable Columnstore Index. The SQL Server 2014 Columnstore Index must use all the columns in the table and can’t be combined with other indexes.
5. Storage I/O control
The Resource Governor lets you limit the amount of CPU and memory that a given workload can consume. SQL Server 2014 extends the reach of the Resource Governor to manage storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool.
6. Power View for Multidimensional Models
Power View used to be limited to tabular data. However, with SQL Server 2014, Power View can now be used with multidimensional models (OLAP cubes) and can create a variety of data visualizations including tables, matrices, bubble charts, and geographical maps. Power View multidimensional models also support queries using Data Analysis Expressions (DAX).
7. Power BI for Office 365 Integration
Power BI for Office 365 is a cloud-based business intelligence (BI) solution that provides data navigation and visualization capabilities. Power BI for Office 365 includes Power Query (formerly code-named Data Explorer), Power Map (formerly code-named GeoFlow), Power Pivot, and Power View. You can learn more about Power BI at Microsoft’s Power BI for Office 365 site.
8. SQL Server Data Tools for Business Intelligence
The new SQL Server Data Tools for BI (SSDT-BI) is used to create SQL Server Analysis Services (SSAS) models, SSRS reports, and SSIS packages. The new SSDT-BI supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL Server 2014. In the pre-release version of SQL Server 2014, SQL Server Setup doesn’t install SSDT-BI. Instead, you must download SSDT-BI separately from the Microsoft Download Center.
9. Backup Encryption
One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.
10. SQL Server Managed Backup to Windows Azure
SQL Server 2014’s native backup supports Windows Azure integration. Although I’m not entirely convinced that I would want to depend on an Internet connection to restore my backups, on-premises SQL Server 2014 and Windows Azure virtual machine (VM) instances support backing up to Windows Azure storage. The Windows Azure backup integration is also fully built into SQL Server Management Studio (SSMS).
Installation Instructions for Windows
Step 1) RDP Connection: To connect to the deployed instance, Please follow Instructions to Connect to Windows instance on AWS Cloud
1) Connect to virtual machine using following RDP credentials:
- Hostname: PublicDNS / IP of machine
- Port : 3389
Username: To connect to the operating system, use RDP and the username is Administrator.
Password : Please Click here to know how to get password .
Step 2) SQL Connection: To Connect Microsoft SQL Server Management Studio in windows server, Please follow Instructions to Connect Microsoft SQL Server Management Studio
Step 3) Database Credentials: You can Login by below SQL Database credentials
SQL UserName : sa || Password : Passw@rd123
Note: You can reset ‘sa’ password by using windows authentication to connect to local SQL instance. Please use localhost in the server name when connecting from inside the RDC
Please change the password after the first login.
Step 4) Other Information:
- Windows Machines: RDP Port – 3389
- sql server port :1433: By default, this is blocked on Public interface for security reasons.
Configure custom inbound and outbound rules using this link
Installation Instructions for Windows
Step 1) VM Creation:
- Click the Launch on Compute Engine button to choose the hardware and network settings.
2.You can see at this page, overview of Cognosys Image as well as some estimated costs of VM.
3.In the settings page, you can choose the number of CPUs and amount of RAM, the disk size and type etc.
Step 2) RDP Connection: To connect to the deployed instance, Please follow Instructions to Connect to Windows instance on Google Cloud
Step 3) SQL Connection: To Connect Microsoft SQL Server Management Studio in windows server, Please follow Instructions to Connect Microsoft SQL Server Management Studio
Step 4) Database Credentials:
You can Login by below SQL Database credentials
SQL UserName : sa
Step 1. Please change password at earliest.
Step 2: Click on Custom Metadata for sa Password details.
Step 3: Connect to SQL Management Studio Using the credentials as provided in Custom Metadata above.For getting password , Please follow below.
Step 5) Other Information:
- Windows Machines: RDP Port – 3389
- sql server port :1433: By default, this is blocked on Public interface for security reasons.