SQL Server Sp2 Express with IIS on Cloud

1-click AWS Deployment 1-click Google Deployment

Overview

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.

TABLE 1
SQL Server edition Definition
Enterprise The premium offering, SQL Server Enterprise edition delivers comprehensive high-end datacenter capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence – enabling high service levels for mission-critical workloads and end-user access to data insights.
Standard SQL Server Standard edition 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.
Web SQL Server Web edition is a low total-cost-of-ownership option for Web hosters and Web VAPs to provide scalability, affordability, and manageability capabilities for small to large-scale Web properties.
Developer SQL Server Developer edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server. SQL Server Developer is an ideal choice for people who build SQL Server and test applications.
Express editions 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. 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.

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 RUNScreenshot of SQL Server 2012 Express Installation window with "Run" button selected
  4.  The set up file will start extracting the installation filesWindow showing installation progress bar
  5.  On the next screen choose the top option to install a new stand-alone installation and click on it.SQL Server Installation Center window with "New SQL Server Installation" highlighted
  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.SQL Server 2012 Express Licensing agreement
  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.SQL Server 2012 product updates page
  8. On The next screen you will see that the set up is getting ready for installations.SQL Server 2012 Express setup page
  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.SQL Server 2012 Express setup page showing feature options and descriptions
  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.Instance Configuration page with "Named Instance" highlighted
  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.SQL Server 2012 Server Configuration page with "Collation" tab highlighted
  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.SQL Server 2012 Database Engine Configuration page
  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.Database Engine Configuration page with "Data Directories" tab highlighted
  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.SQL Server 2012 Express installation progress bar
  16. Once the installation has completed, you will receive a screen showing the details of what was completed and if there were any problems.Installation Complete page with installed features highlighted

Features

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 marketplaceAzure and Google Cloud Platform (GCP)

AWS

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

Google

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.

Videos

Introduction To Microsoft SQL Server

SQL Server Sp2 Express with IIS on Cloud

Related Posts