Definition of SQL Server Express

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.

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.

SQL Server SP2 Express  edition with IIS delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premise and cloud — enabling effective database management with minimal IT resources.

Core data management and business intelligence capabilities with minimal IT resources.

Internet Information Services (IIS, formerly Internet Information Server) is an extensible web server created by Microsoft for use with Windows NT family.IIS supports HTTP, HTTPS, FTP, FTPS, SMTP and NNTP. It has been an integral part of the Windows NT family since Windows NT 4.0, though it may be absent from some editions (e.g. Windows XP Home edition) and is not active by default.

Internet Information Services (IIS, formerly Internet Information Server) on Cloud runs on Amazon Web Services (AWS) and Google Cloud Platform (GCP) and is built to provides a secure, easy-to-manage, modular and extensible platform to test websites and visual means of creating, configuring, and publishing sites on the web.

SQL Server Editions

The following table describes the editions of SQL Server.

Major Features of SQL Server Express Edition

• Microsoft has introduced SQL Server 2012 to the world and it’s time for IT professionals to start to come to speed on what’s new in this highly anticipated version of SQL Server.1. AlwaysOn Availability Groups — This feature takes database mirroring to a whole new level. With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable, and can be used for database backups. The big win is that your DR environment no longer needs to sit idle.2. Windows Server Core Support — If you don’t know what Windows Server Core is, you may want to come up to speed before Windows 8 (MS is making a push back to the command line for server products). Core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported for SQL Server.

  3. Columnstore Indexes — This a cool new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

  4. User-Defined Server Roles — DBAs have always had the ability to create custom database role, but never server wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.

  5. Enhanced Auditing Features — Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.

  6. BI Semantic Model — This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It’s a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics

  7. Sequence Objects — For those folks who have worked with Oracle, this has been a long requested feature. A sequence is just an object that is a counter — a good example of it’s use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.

  8. Enhanced PowerShell Support — Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of their server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.

  9. Distributed Replay — Once again this is answer to a feature that Oracle released (Real Application Testing). However, and in my opinion where the real value proposition of SQL Server is, in Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.

  10. PowerView — You may have heard of this under the name “Project Crescent” it is a fairly powerful self-service BI toolkit that allows users to create mash ups of BI reports from all over the Enterprise.

  11. SQL Azure Enhancements — These don’t really go directly with the release of SQL 2012, but Microsoft is making some key enhancements to SQL Azure. Reporting Services for Azure will be available, along with backup to the Windows Azure data store, which is a huge enhancement. The maximum size of an Azure database is now up to 150G. Also Azure data sync allows a better hybrid model of cloud and on-premise solutions

  12. Big Data Support — I saved the biggest for last, introduced at the PASS (Professional Association for SQL Server) conference last year, Microsoft announced a partnership with Hadoop provider Cloudera. One part of this involves MS releasing a ODBC driver for SQL Server that will run on a Linux platform. Additionally, Microsoft is building connectors for Hadoop, which is an extremely popular NoSQL platform. With this announcement, Microsoft has made a clear move into this very rapidly growing space.

  SQL 2012 is a big step forward for Microsoft — the company is positioning itself to be a leader in availability and in the growing area of big data. As a database professional, I look forward to using SQL 2012 to bring new solutions to my clients.

Cognosys Provides Hardened images of SQL Server SP2 Express  edition with IIS  on the cloud ( AWS marketplace, Azure and Google Cloud Platform).

Deploy SQL Server SP2 Express  edition with IIS  securely on cloud i.e. AWS marketplace, Azure and Google Cloud Platform (GCP)

Installation Instructions For Windows

Installation Instructions For Windows

Note: How to find PublicDNS in AWS

Step 1) RDP  Connection: To connect to the deployed instance, Please follow Instructions to Connect to Windows  instance on AWS Cloud

1) Connect to the 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

Note: Please change the password after the first login.

Step 4) Other Information:

1.Default ports:

• Windows Machines:  RDP Port – 3389
• Http: 80
• Https: 443
• SQL Server Port: 1433 this is by default not allowed on the firewall for security.

Configure custom inbound and outbound rules using this link

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 Google Cloud

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
• Http: 80
• Https: 443
• SQL Server Port: 1433 this is by default not allowed on the firewall for security.

Introduction To Microsoft SQL Server