SQL Server Express is a free version of Microsoft’s primary relational database management system (RDBMS) – the SQL Server. Essentially, the SQL Server is a database management system that can be used to store and access the information stored in many different databases. SQL Server comes with an impressive range of features like business intelligence, reporting, and in-depth advanced analytics.

The enterprise edition of SQL Server competes against enterprise-oriented systems like Oracle Database (DB) and MySQL. SQL Server Enterprise comes loaded with features and can be too expensive for smaller sized companies to maintain. Microsoft does, however, offer SQL Server in some different editions including:

• SQL Server Enterprise
• SQL Server Business Intelligence
• SQL Server Standard
• SQL Server Web
• SQL Server Express

SQL Server Express is the most basic offering available. It is a full database engine you can deploy to a server or embed into an application. Express is free and comes with many of the same features as the enterprise edition. SQL Server Express is probably most suited to supporting production applications for smaller to midsize businesses. A typical SQL Server Express use case would be a deployment by developers who do not want to create applications with a database hosted on a server. Using Express, they would be able to develop apps with their SQL Server database.

What is SQL Server?

SQL Server 2012 is a basically a relational database management system that offers a variety of administrative tools to affluence the problems of database development, maintenance, and administration. Some of the regularly used tools in SQL Server 2012 are SQL Server Management Studio, SQL Profiler, SQL Server Agent, SQL Server Configuration Manager, SQL Server Integration Services, and Books Online. With SQL Server currently on a 2-year release cycle, each release is more of an evolutionary change from the previous release than a revolutionary change. Therefore, Microsoft SQL Server 2012 is not a quantum leap forward from SQL Server 2008 R2, but it does provide a number of interesting new features and enhancements that further extend the performance, reliability, availability, programmability, and ease of use of SQL Server. This chapter introduces the major new features provided in SQL Server 2012 and covers a number of the enhancements to previously available features.

SQL server constantly monitors memory usage to assess resource contention (or availability); its job is to make sure that there is a certain amount of free space available at all times. As part of this process, when it notices any such resource contention, it triggers Lazy Writer to free up some pages in memory by writing out dirty pages to disk.

SQL Server 2012 components

There are four major components in SQL Server database engine. The protocol layer, query processor, storage engine, and SQLOS. Every instruction must interact with the four components.

The protocol layer receives the request and translates it into the form the relational engine can work. It takes the results from the queries, translates it in the form the client can understand. The query processor accepts T-SQL batches and checks for the functionality.

Once the batch is executed, if the data is required, the request for the data is passed to the engine. The storage engine manages the data access. The SQLOS layer maintains the activities considered as the responsibility of the operating system. They can be synchronization, deadlock detection, memory management, thread management.

Protocols

The application used the protocols for the communication with the database engine. The tabular data stream packet is used. The SQL Server network interface ( SNI ) protocol layer encapsulates the TDS packet in a standard communication protocol.

SQL Server supports multiple protocols simultaneously from various clients. The following protocols are available to the user.

1. Shared Memory – The protocol does not need any configuration settings. Client using the shared memory protocol connects to the SQL Server instance on the same machine.
2. Named Pipes – The protocol is developed for the local area network ( LAN ). A part of memory is used by one process for passing the information to another process.
3. TCP/IP – It is the common protocol for the internet. It communicates across different interconnected computer networks. It consists of standards for routing network and security features.

Query Processor

The query processor is also known as the relational engine. It consists of the server components that check for the query requirements. It contains two components as query optimization and query execution.

The processor manages the query execution as the data requests from the engine and the results are returned.

Query optimizer

The query optimizer accepts the query tree as input and prepares for the optimization. The Data Definition Language ( DDL ) statements are compiled in the internal form. The Data Manipulation Language ( DML ) statements are processed in one or more ways. The query optimizer produces a execution plan.

There are some steps involved in normalizing the query. The first step is to normalize the query and convert a single query into multiple queries. The optimizer normalizes the query. It determines the plan for query execution. It is cost based. The optimizer selects a plan for query execution. It considers the type of statement requested by the user. The amount of data in tables affected, type of statement requested, indexes in every table, sampling of data values in the query are checked.

The query optimizer considers the access methods for solving a query and selecting the most effective plan. Once the normalization and optimization is completed, the normalization tree is produced. The command specifies the table is affected, indexes used, criteria for the selection of data.

The storage engine

The storage engine consists of components needed for accessing and managing the data. The storage engine consists of access methods, locking and transaction services, and utility commands.

Access methods

The access methods codes are called when the server needs the data. It sets up and requests the data pages and index pages and creates the OLE DB rowset for returning the relational engine. The access methods contain code to open table, retrieve qualified data, and update data. The pages are not retrieved, the request to the buffer manager is made. The manager serves the page in the cache or reads from the disk.

Transaction services

The SQL server checks the atomicity of transactions. It means that either all transactions are completed or none. The transactions must be durable. It means that if the transaction is committed, it must be recoverable by the server. The ACID properties must be adhered by the transactions.

In SQL Server, if there is a system failure before the transaction is committed, all the work is rolled back. Write ahead logging has the ability to roll back work in progress or roll forward committed work. It ensures that the record of transaction is added in a disk in the log before it is committed. The writes to the log are always synchronous. The writes to the data pages are asynchronous.

The component co ordinates with the locking code considering the lock release, depending on the isolation level. The code versioning for removal of the older versions not in need is checked.

The two concurrency models consisting of the ACID properties of transactions are:

1. Pessimistic concurrency: It ensures the correctness and consistency by locking the data so that it is not modified.
2. Optimistic concurrency: It provides a consistent data by placing the older versions of rows with committed values. They are placed in the temdb also known as version store.

Locking operations

Locking function is useful in a multi user database system as SQL Server. The server helps user manage multiple users simultaneously. It checks that the transactions follow the isolation level.

SQL Server 2012 configuration

Configuration manager is used for managing the services associated with the SQL Server, configuring the network protocols, managing the connectivity configuration for system connected to SQL Server. The manager can be accessed by clicking Start, All Programs, Microsoft SQL Server 2012, Configuration tools, SQL Server configuration manager.

Network protocols

A specific protocol must be enabled on client and server side for the client to connect and communicate with the server. It server listens to the requests on all the protocols. The TCP/IP protocol must be installed on the client and server. They are installed on the windows setup.

On the client system, the SQL native client is installed and configured to use the network protocol enabled to the server. The client tools connectivity setup is used. The native client is a standalone data access application programming interface. It is used for both OLE DB and ODB connectivity.

Managing services

The configuration manager is used for start, pause, resume, or stop the server related services. The services availability depends on the SQL Server components. User must have SQL server service and SQL server agent service.

It is preferred over windows service management tools. When the user uses SQL Server tool as configuration manager to modify the account used by the SQL server or agent service, the tool automatically adds additional configurations as settings permissions for the registry.

Nonessential services

Once the service is not required, they must be disabled by the user. In Windows server, click Start, right click computer, and select Manage. Expand the services and applications node in the computer management, click services. User can change the service startup by right clicking the name and selecting Properties. Unnecessary services add overhead to the system. They should not be marked as Startup.

Firewall setting

Firewall system helps prevent unauthorized access to the computer resources. If user needs to access the system resources, the firewall must be configured on the system SQL server is running.

The following steps are used for firewall settings

1. Configure the SQL Server instance to use the TCP/IP port. The default port of the server can be modified.
2. Configure the firewall to allow access to the specific port for the authorized users
3. Once the SQL server is configured for listening to a specific port and opening the port, the SQL server executable can be listed.

SQL Server configuration settings

SQL server automatically configures the system; it dynamically adjusts the important options. The default values can be accepted easily. The sp_configure system stored procedure is used for setting the configuration. The sys.configurations view helps user check the column is_advanced. The options which are advanced can be viewed.
The Server properties sheet is used to set the configurations options.

If the user uses the sp_configure stored procedure, no changes will be effected till the RECONFIGURE command executes. The RECONFIGURE WITH OVERRIDE command is useful when the option is outside the specified range.

Memory Options

1. Min Server memory and Max Server memory – The SQL server adjusts the total amount of the memory resources to be used. The Min and Max server options are used for the manual control. The default value for the min server memory is 0 MB and max server is 2147483647. The sp_configure stored procedure is used to change both the options to same value.
2. User connections – SQL Server 2012 dynamically adjusts the number of simultaneous connections to the server if the setting is set to 0. The SQL server does not allocate the complete memory for each connection. The server allocates the pointers with many entries for user connections.

Scheduling options

The SQL server contains a special algorithm for scheduling user process through SQLOS. It manages the scheduler per logical processor and checks that only one process can run the scheduler at a time. The SQLOS manages the user connections and maintain the CPU balance.

Lightweight Pooling

The SQL server operates in thread mode. The server lets the user connections work in fiber mode. The fibers are less expensive to manage than threads. The lightweight pooling option has a value 0 which states that the server should execute in fiber mode.

Max Worker Threads

SQL Server uses the operating system thread services by keeping the pool of threads that takes request from the queue. The threads are divided evenly among the SQLOS schedulers such that the number of threads available for every scheduler is the Max Worker Threads setting divided by the number of CPU.

They have the default value as 0. The number of threads are configured by the SQL Server.

Affinity I/O Mask and Affinity64 I/O Mask

The Affinity I/O Mask and Affinity64 I/O Mask options control the affinity of the processor for I/O operations. User can set value 0 for Affinity I/O Mask and Affinity Mask for CPU, 1 for Affinity I/O Mask option and 0 for Affinity I/O Mask and 1 for Affinity Mask. User must ensure that the bits don’t overlap.

Filestream access level

The database engine integrates with the NTFS file system for storing binary large object data as files on the file system. The user can access data using the Windows system cache for caching the file data for reducing the effect of filestream data on the server performance.

The filestream object must be enabled using the SQL Server configuration manager. In the configuration manager, right click on the server, select Properties. Check to enable the filestream for T-SQL access.

Once the filestream is enabled, set the configuration value. The following values can be added.

1. 0: It disables the filestream support for an instance
2. 1: It enables the filestream for T-SQL access
3. 2: It enables the filestream for Win32 streaming access

Querying processing options

1. Min memory per query – The configuration option allows user to specify the minimum amount of memory granted by any of the operations before executed. The operations like sort, merge and hash receive memory dynamically.
2. Query wait – It controls the wait for the query for the memory till it is available. If the value is -1, the query waits 25 times than the estimated execution time. A value 0 or more, specifies the number of seconds the query waits.
3. Blocked Process threshold – The option allows the administrators to request notification when the user task has been blocked for more than the configured number of seconds. When the value is set to 0, n notification is provided. The value up to 86,400 seconds can be set.
4. Index Create Memory – The Min Memory per Query option applies to the sorting and hashing used during the query execution. It is not applied during the index creation.
5. Query Governor Cost Limit – It is used to specify the maximum number of seconds the query can run. If user specifies the non zero value, the server disallows the execution of any query that has estimated cost exceeding the value.

Usage of SQL Server:

• To create databases.
• To maintain databases.
• To analyze the data through SQL Server.
• To generate reports through SQL Server
• To carry out ETL operations through SQL Server

SQL Server Components

SQL Server works in client-server architecture and it supports two types of components:−

• Workstation componentsare installed in every device/SQL Server operator’s machine. These are just interfaces to interact with Server components. Example: SSMS, SSCM, Profiler, BIDS, SQLEM etc.
• Server componentsare installed in centralized server. These are services. Example: SQL Server, SQL Server Agent, SSIS, SSAS, SSRS, SQL browser, SQL Server full text search etc.

SQL Server Express Benefits

Some benefits come with an SQL Server Express deployment.

• Free: One huge advantage of SQL Server Express is that it is free. Your only outlay is the time investment you make downloading and setting up the system. If you only want to learn how to use SQL Server, then Express is for you. There is nothing to lose by downloading the system and getting used to how it works.
• Scalability: SQL Server Express is an ideal starting point for smaller independent software vendors (ISVs) since it can be used with any smaller application. The licensing allows Express to be included as part of an app or product. While there are limitations on memory and socket usage, they are not as restrictive as some might think. Express is not limited to a single user which is a commonly held misconception. There is a 10GB database restriction, but that is a maximum size per database meaning you can have multiple databases that store up to 10GB of data. If you are an ISV and your company experiences high growth rate resulting in increased database demands, then you can only upgrade to a paid version of SQL Server.
• Security: Within SQL Server Express there is the option of free online backup that will help to protect your valuable business data if anything goes wrong. Administrators should still follow security best practices like restricting access to backup folders and following Windows password policies.
• Features: While Express is the “lite” version of SQL Server, there is still an impressive range of features that you would have to pay for with other systems. Express supports Full-Text Search, native XML, and the SQL Common Language Runtime. Other key features include a reporting component and report designer enabling custom report creation.

SQL Server Express Limitations:

Before deploying SQL Server Express, you should make yourself aware of its limitations which include:

• 1GB maximum memory used by the SQL Server Database Engine
• The maximum size of each relational database is 10GB
• SQL Agent is not included in Express. The SQL Agent is a background tool which enables administrators to automate tasks like backing up data, database replication setup, job scheduling, user permissions, and database monitoring.
• The limit on the buffer cache for each instance is 1MB of RAM.
• The relational database engine is restricted to the lesser of 1 socket or 4 cores.

Versions of SQL Server Express

The 2016 version of SQL Server Express was released in three different editions:

SQL Server 2016 SP1 Express: This is the core express offering and comes with the features we have already laid out. In addition, capabilities like dynamic data masking and row-level security are now available in Express.

SQL Server 2016 Express with Advanced Services: This edition of Express contains the most features. Included are the relational database engine, Management Studio, reporting, and full-text search. Ideal for developers working on smaller applications which need to implement reporting on their applications.

SQL Server 2016 Express LocalDB: This edition is mainly used as a development tool. It is a lighter version of Express that runs as a local app. It does support all the main programmability features of the other editions. The big advantage of LocalDB for developers is that it allows them to create a local environment that is much easier to install and manage. Users can just start up an instance of LocalDB runtime as they need it and do not need to install a service.

Installing SQL Server 2012 Express

Before installing SQL Server 2012 make sure you have the below components installed or enabled on your Windows server.

1. Download and install .NET Framework 4.0. If the computer has Internet access, SQL Server setup will download it when needed.
2. Enable .NET Framework 3.5 using Control Panel -> Programs and Features -> Turn on/off features

Now go through the next steps for the installation.

1. Login to your Windows server with the administrator user.
2. Download the proper SQL server set up from the link https://www.microsoft.com/en-us/download/details.aspx?id=29062
3. Double click the installer file and then click on RUN
4.  The set up file will start extracting the installation files
5.  On the next screen choose the top option to install a new stand-alone installation and click on it.
6. The set up will process the basic operations and you will see the licensing screen where you have to check the box for “I accept the license terms” click on next.
7. The next screen will be Product Updates. SQL server will check for and install any updates. Leave it as it is and click on next.
8. On The next screen you will see that the set up is getting ready for installations.
9. On the Feature Selection page, please select the features you would like to install. A description of each feature will appear on the “Feature description” area when you click on a feature. Click on Next.
10. On the Instance Configuration page, choose a name for the instance or select a default instance. SQLExpress is the name of the instance as we have selected Named Instance. Click on Next.
11. The next step is server configuration. From here you can change the Windows services associated with this instance of SQL Server. You can keep the defaults here unless you want to specify different user accounts for the services. You can also change the default collation settings if you are not in the United States. Click Next to continue.
12. The next step is Database Engine configuration. On the Server Configuration tab you can select whether SQL Server will only authenticate using Windows accounts or you can choose Mixed Mode which will allow Windows accounts and SQL accounts. Mixed mode authentication is generally used so we will select it. Provide the password for “sa” user and then click on next.
13.  On the Data Directories tab you can change the locations of log files and other files that SQL will use. Depending on your server configuration and the load that will be put on SQL, you may want to put the database and log folders on separate drives. For most users, you can leave the default values. Click on next.
14. The next screen is of Error Reporting option. You can choose whether or not you would like to send error reports to Microsoft. Just click on next.
15. At this point, SQL server will install on your computer. This could take a while to complete depending on the computer you are using.
16. Once the installation has completed, you will receive a screen showing the details of what was completed and if there were any problems.

–SQL Server 2012 Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications. It is the best choice for independent software vendors, developers, and hobbyists building client applications.

If you need more advanced database features, SQL Server Express can be seamlessly upgraded to other higher end versions of SQL Server. New in SQL Server 2012 is SQL Server Express LocalDB, a lightweight version of Express that has all of its programmability features, yet runs in user mode and has a fast, zero-configuration installation and a short list of prerequisites.

Cognosys provides hardened images of SQL Server 2012 Express edition on all public cloud ( AWS marketplace and Google Cloud Platform).

Deploy SQL Server 2012 Express on Cloud securely on cloud i.e. AWS marketplace and Google Cloud Platform (GCP) with Reliable Services offered by Cognosys at one-click with easy written and video tutorials.

Secured SQL Server 2012 SP2 Express on Windows 2012 R2

SQL Server 2012 on cloud for AWS

 New Features and Enhancements of SQL Server 2012:

Hadoop Integration:

Hadoop integration allows organizations to move data back and forth from Hadoop to SQL Server. This allows the strong data analysis tools included in SQL Server 2012 to be used to analyze and report on unstructured Hadoop data.SQL server Hadoop connectors allow the import of unstructured data from Hadoop into SQL Server tables where it can be integrated with data warehouse and BI solutions. The ability to submit a MapReduce or Hive request through SQL Server Integration Services substantially improves the ability to take advantage of a NoSQL database.

Enhanced PowerShell Support:

PowerShell gives companies a rich scripting language that has capabilities built on .Net. Using PowerShell allows automation of many tasks that would previously have required user interaction with the servers.XTIVIA can help you write, troubleshoot and implement PowerShell scripts.New PowerShell cmdlets enhance the ability to manage SQL Server with PowerShell.

User Defined Server Roles:

User Defined Server Roles provide companies with a fine-grained permission control at a server level rather than at a database level. This will help to maintain consistent application of security policies across all databases on a server. Earlier Server roles were fixed and no additions could be made. With this change, a Server role may be created that will apply to all databases on the server. This will substantially simplify management where users need to access multiple databases with the same capabilities.

Master Data Services:

SQL Server Master Data Services provides a single location for all data. It is designed to resolve issue with inconsistent data from multiple sources including non-SQL Server databases. Master Data Services allows for data alignment of data from multiple applications. This allows a “single version of the truth”. It also allows management of data consistency across multiple database platforms like Oracle, SQL Server and IBM Db2.

Power View:

Power View opens up ad hoc reporting for consumers of data. It provides access to data based on PowerPivot workbooks or tabular models deployed to SQL Server Analysis Services 2012.Power View is a feature of SQL Server reporting Services. It provides ad hoc reporting capability. Power View improves on Self-Service BI allowing presentation and data visualization of live data.

Contained Databases:

Contained databases are partially isolated from other databases on an instance of SQL Server. User authentication can be handled within the database and don’t require a SQL Server login. Many activities such as migrating a database to another server can be simplified with a contained database. There are objects that can’t be contained – things like linked servers, jobs, and stored procedures that are in databases external to the contained database.

Columnstore indexes:

Columnstore Indexes store data from a column in sets of pages. This is different from a normal SQL Server index where data from rows of data are stored in a set of pages. It is common to see a 10x performance improvement with a dramatic increase in IO.

SQL Server Data Tools:

SQL Server Data Tools build on the abilities of the Business Intelligence Design Studio. It adds capabilities for doing development and maintenance of databases with Visual Studio.SQL Server Data Tools is a replacement for Business Intelligence Development Studio. It allows you to do everything that you could do in BIDS as well as schema and data comparison, SQLCLR development, stored procedure debugging and tools for database development.

Major Features of SQL SERVER 2012 

1. Cross-Box Scale Limits
2. High Availability
3. Scalability and Performance
4. Security
5. Replication
6. Management Tools
7. RDBMS Manageability
8. Development Tools
9. Programmability

Installation Instructions For Windows

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

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

Step 4) Other Information:

1.Default ports:

• 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 Step by Step Screenshots

Installation Instructions For Windows

Installation Instructions for Windows

Step 1) VM Creation:

1. 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. For getting password , Please follow below.

Step 2:  SQL sa Password details.

Step 3: Click on Custom Metadata for sa Password details.

Step 4: Connect to SQL Management Studio Using the credentials as provided in Custom Metadata above.Please change password at earliest.

Step 5 ) Other Information:

1.Default ports:

• Windows Machines:  RDP Port – 3389
• sql server port :1433: By default, this is blocked on Public interface for security reasons.

Secured SQL Server 2012 SP2 Express on Windows 2012 R2:

Introduction to MS SQL Server 2012 Administration: