Microsoft SQL Server 2012 Enterprise Description
Microsoft SQL Server 2012 Enterprise is an information platform and server which can present the safety and scalibility.Microsoft SQL Server 2012 Enterprise is a cloud-ready platform which signifies that the organizations can simply reap the benefits of safety whereas deploying in addition to synchronizing information throughout desktops and trendy units. With its energy to revive and retrieve information for threerd celebration software program that depends on managing the knowledge inside databases, SQL Server’s success charge is sort of excessive.
All In All Microsoft SQL Server 2012 Enterprise has been developed to handle a variety of audiences and it features a wealthy record of add-ons in addition to intensive processor help. Probably the most essential options of Microsoft SQL Server 2012 Enterprise is supplied by the server to cloud expertise, which is a fast and dependable structure for versatile utility deployment.
Features For Microsoft SQL Server 2012 Enterprise
A data platform and server which will provide the security and scalability.
Aimed at facilitating and easing the data warehousing and application development.
A cloud-ready platform meaning organizations can take advantage of protection while deploying data.
Developed to address a wide range of audiences and includes a rich list of add-ons and extensive processor support.
Usage of SQL Server
The SQL Server would act like a client-server database system, unlike most other desktop systems. These systems run on a central server or multiple central servers. This allows multiple users to access the data simultaneously from the network. The users can access the database through an application. The data is stored using the database and it is made available. There are some useful features in SQL Server that assist the CRM applications to offer its functionalities. The employees can be in different countries and access and update the data through the browser.
SQL Server Versions
There have been about 19 different versions of SQL Server. However, there have surely been some noticeable ones. They are stated below.
The first one came out in 1989 as a project to port the Sybase SQL Server onto OS/2.
The SQL Server made entry into Windows NT with the Server 4.2 for NT that was released in 1993.
The SQL Server 6.0 was released into 1995 and ended the collaboration with Sybase.
The SQL Server 7.0 was released in 1998 where the source code was converted from C to C++.
In 2005, the SQL Server 2005 was released where the old Sybase code was completely removed.
The SQL Server 2012 included new features like Availability Groups and Always On SQL Server Failover Cluster Instances, which offers options to improve database availability.
The SQL Server 2014 provided a new in-memory capability for tables that helped them to fit entirely in memory.
In 2017, the SQL Server 2017 or version 14.0, codenamed vNext, was released where support for Linux was added.
For An In-Depth Understanding And Practical Experience, Explore Online SQL Server Training
SQL Server Architecture
The SQL Server Architecture is based on the client/server architecture model where the data in the server is shared and accessed by the users through the client applications. The best approach to study technology is to understand the internal build or architecture.
General Architecture – The General Architecture cares about a few details like query, protocols, servers, and relational engine. It also involves the division of the queries into logical units and having clients for initiating requests. It also involves working with query optimizer and a proper execution plan.
Memory Architecture – One of the primary design goals of the memory architecture is to minimize the disk reads and writes. It is a vital part of the SQL Server architecture.
Data File Architecture – The most important part of Data File Architecture is that the database is grouped into database files, which are used for allocation and administration. A file cannot be a member of more than a single group.
Log File Architecture – Logical operation is performed by the SQL Server transaction log. Here, the transaction log acts as a string of log records. All the operations are logged. The SQL Server Architecture relies heavily on the Log File Architecture.
SQL Server Components
The modern versions of SQL Server are released every 18 months. Major enhancements often do not make the marketing headlines but still offer productivity and performance enhancements. Microsoft started to include feature releases in the cumulative updates.
Database Engine – The relational databases are created and driven by this part of the SQL Server.
SQL Server Analysis Services – This part handles the data-analysis of the SQL Server. Online Analytical Processing cubes are created by them, which are sophisticated programming objects that can organize data inside the relational database.
SQL Server Reporting Services – It is a component of the SQL Server that offers to report in spite of the operating system of the database.
SQL Server Integration Services – The Extract, Transform, and Load or ETL phase of the SQL Server is performed by the SQL Server.
Types of SQL Keys
The keys act as a combination of single or multiple fields in the database tables. They are used to retrieve the records or information from the rows in a table according to the requirements. These keys can also be used to dictate the relationship between tables. The types of keys include:
Super Key – A set of one or more than one key that would uniquely identify a record in the table.
Candidate Key – A set of one or more fields that would be able to uniquely identify a record. Multiple candidate keys can be present in a table, where each can be a Primary Key.
Primary Key – A set of one or more fields that would uniquely identify a record in the database table, which cannot accept null or duplicate values.
Alternate Key – A candidate key that is not acting as the Primary Key.
Foreign Key – A field in a database table that is acting as a Primary Key in another table and is used to create a relation between the tables. Null and duplicate values can be accepted.
The instance of SQL Server
The SQL Server instance actually refers to an instance of the Database Engine. It is a copy of the sqlservr.exe and can run on the operating system. Each of these instances would be able to manage several system databases. This means multiple instances of the Database Engine can be run on a computer independently of the other instances.
These instances can be installed at any time and even after the SQL Server has been running on the system for some time. The instances apply primarily to the database engine and the supporting components. They do not apply to client tools.
Advantages of Instances
When it comes to multiple instances, the sky is the limit. The reasons could vary across different organizations. Some of the uses are as follows:
Functional or Load testing – Load testing refers to having multiple code bases loaded and coordinating with the instances to perform load testing. The load testing is then to be conducted to restart all of the instances once complete. Functional Testing allows having multiple code bases to be tested independently with preallocated resources.
Server Sharing – A departmental solution can be considered with SQL Server. Multiple instances of Server 1 can be used to equally share the resources of the server. It is ideal for chargebacks to the departments.
Server Consolidation – Here, multiple instances are consolidated to a single server. It is possible to perform full SQL Server backups and restorations to the single instance and maintain the same functionality as the original server.
Security – Multiple instances are quite good for third-party applications. These require elevated SQL Server privileges where you would not like to commingle the applications on the same instance.
These are some of the advantages of the SQL Server Instances.
Client/Server Database Systems
The client-server applications are those programs that allow the user to access data stored in the server. The computer system does not matter. Both of these could be workstations and could be running on the same type of operating system. The servers would hold databases and the users would be able to access the database from the workstations. A client/server database application can be created by using any other database application.
Server – The server is a part of the client/server system that stores the database. The server software is also present, which also is a part of the database management system that interprets the commands that come from the clients. These commands are translated as operations in the database.
Client – The most important job of the client is to create a user interface. To the end users, the client machine is the computer itself and the user interface is the application. The users would generally be not able to realize that a server is involved in the process.
Database Management Tasks
Several database management tasks are involved in the process. They are as follows:
Modifying the SQL Server Configuration Settings – The major part of the configuration settings can be changed through the SQL Server Management Studio currently. The SP_CONFIGURE TSQL command also helps to change the required settings.
Managing the Database Engine Informational Reports – Proper management can be performed using the SQL Server instances. Several standard ones are already included in the system.
Detaching and Attaching Databases – Detachment of a database means complete removal of the SQL Server instance. The files would, however, be kept intact and they would reside on the file system. The state of the database would remain intact when the database is attached again.
Scripting the Database Objects – Two levels of scripting functionality assists in transferring the SQL Server task or the action to a TSQL script. This scripting functionality is a wonderful way to automate redundant administration responsibilities or settings. Solid scripts can be created easily without even being an expert.
Restoring the Database – Creating backups and the recovery strategy is the most important task. It is imperative to understand the recovery models associated with the database while creating backups.
SQL Server 2012 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.
Installation of SQL server 2012:
Steps for Installing SQL Server 2012 Enterprise
There are four steps to follow when installing SQL Server 2012 Enterprise.
1. Set up the Base Configuration test lab.
2. Install SQL Server prerequisites.
3. Install SQL Server 2012 Enterprise.
4. Verify the installation.
The following sections provide details about how to perform these steps.
Step 1: Set up the Base Configuration Test Lab
Set up the Base Configuration test lab for the Corpnet subnet by using the procedures in the
“Steps for Configuring the Corpnet Subnet” section of the Test Lab Guide: Base Configuration.
Step 2: Install SQL Server Prerequisites
In this procedure, you create an organizational unit in the corp.contoso.com domain named
ServiceAccounts and then create two service accounts that will be used by SQL Server 2012.
To create the ServiceAccounts organizational unit and the service accounts
1. On DC1, log on with the User1 account.
2. Click Start, click Administrative Tools, and click Active Directory Users and
3. In the tree pane of Active Directory Users and Computers, right-click
corp.contoso.com, select New, and then Organizational Unit.
4. In Name, type ServiceAccounts, and then click OK.
5. Right-click ServiceAccounts, select New, and then select User.
6. In New Object – User, in the Full Name box, type SQL Server Agent.
7. In User logon name, type SQLAgent, and click Next.
8. In Password and Confirm Password, type Pass1word$.
9. Clear User must change password at next logon, select Password never expires,
and click Next.
11.Right-click ServiceAccounts, select New, and then select User.
12. In New Object – User, in the Full Name box, type SQL Server Database Engine.
13. In User logon name, type SQLDatabase and click Next.
14. In Password and Confirm Password, type Pass1word$.
15.Clear User must change password at next logon, select Password never expires,
and click Next.
In this procedure, you install the .NET 3.5 Framework.
To install the .NET Framework 3.5
1. On APP1 (or the computer on which SQL Server 2012 Enterprise is being installed), log
on with the User1 account.
2. In Server Manager, click Features, and then click Add Features. This will launch the
Add Features Wizard and you will see the Select Features screen.
3. Select .NET Framework 3.5.1 Features. This will bring up a box that asks states: You
cannot install .NET Framework 3.5.1 Features unless the required role services
Test Lab Guide: Install SQL Server 2012 Enterprise April 2012
© 2011 Microsoft Corporation. All rights reserved. Page 6
To comment on this paper or request more documentation on these features, contact the Microsoft Office and
Servers Team (email@example.com).
and features are also installed. Click Add Required Features. The box will
disappear. On the Select Features screen, click Next.
4. On the Web Server (IIS) screen, click Next.
5. On the Role Services screen, click Next.
6. On the Confirm Installation Selections screen, click Install. This will begin the
7. On the Installation Results screen, click Close.
Step 3: Install SQL Server 2012 Enterprise
In this procedure, you install SQL Server 2012 Enterprise.
To install SQL Server 2012 Enterprise
1. On APP1 (or the computer on which SQL Server 2012 Enterprise is being installed),
navigate to the directory that contains the SQL Server 2012 Enterprise binaries and
double-click Setup.EXE. This will launch the SQL Server Installation Center.
2. On the SQL Server Installation Center, on the left, click Installation.
3. On the right, click New SQL Server stand-alone installation or add features to an
existing installation. This will launch the SQL Server 2012 Setup.
4. On the Setup Support Rules screen, click OK. This will close the Setup Support Rules
screen and will bring up the Product Key screen. From Specify a free version, select
Evaluation. Click Next.
5. On the License Terms screen, read the Licensing terms, place a check in the box next
to I accept the license terms and click Next.
6. On the Product Updates screen, click Next.
7. On the Install Setup Files screen, click Install. This will take a few moments to
complete. Once this completes the Setup Support Rules screen will appear again.
8. On the Setup Support Rules screen, click Next.
9. On the Setup Role screen, select SQL Server Feature Installation and click Next.
10. On the Feature Selection screen, under Instance Features place a check in
Database Engine Services, under Shared Features place a check in Management
Tools – Basic and Management Tools – Complete. Click Next.
11. On the Installation Rules screen, click Next.
12. On the Instance Configuration screen, click Next.
13. On the Disk Space Requirements screen, click Next.
14. On the Server Configuration screen, next to SQL Server Agent under Account
Name, enter CORP\SQLAgent, under Password, enter Pass1word$.
15. On the Server Configuration screen, next to SQL Server Database Engine under
Account Name, enter CORP\SQLDatabase, under Password, enter Pass1word$.
17. On the Database Engine Configuration screen, click Add Current User and click
18. On the Error Reporting screen, click Next.
19. On the Installation Configuration Rules screen, click Next.
20. On the Ready to Install screen, click Install.
21. On the Installation Progress screen, wait until the installation completes.
22. On the Complete screen, click Close.
23.Close the SQL Server Installation Center.
Step 4: Verify the installation
In this procedure, you verify that installed SQL Server services are running.
To verify installed SQL Server services are running
1. Click Start, select Administrative Tools, and click Services.
2. Scroll down to SQL Server (MSSQLSERVER) and verify that it has a status of Started.
3. In Services, right-click SQL Server Agent (MSSQLSERVER), and click Start. This will
start the SQL Server Agent.
4. When this completes, verify the SQL Server Agent (MSSQLSERVER) has a status of
5. Close Services.
In this procedure, you verify the SQL Server 2012 Enterprise installation by connecting to the
database server using SQL Server Management Studio.
To connect to SQL Server 2012 Enterprise using SQL Server Management Studio
1. Click Start, click All Programs, click Microsoft SQL Server 2012, and click SQL
Server Management Studio.
2. On the Connect to Server dialog box, under Server Type: select Database Engine.
3. On the Connect to Server dialog box, under Server name: select APP1 (or the
computer on which SQL Server 2012 Enterprise is being installed).
4. On the Connect to Server dialog box, under Authentication: select Windows
5. Click Connect. This should be successful and the database information will be displayed
on the left. The SQL Server Agent should have a green arrow.
6. Close Microsoft SQL Server Management Studio.
Snapshot the Configuration
This completes the installation of SQL Server 2012 Enterprise. To save this configuration so that
you can quickly return to a working configuration from which you can test other TLGs or test lab
extensions or for your own experimentation and learning, do the following:
1. On all physical computers or virtual machines in the test lab, close all windows and then
perform a graceful shutdown.
2. If your lab is based on virtual machines, save a snapshot of each virtual machine and
name the snapshots TestLabSQLServer. If your lab uses physical computers, create
disk images to save the test lab configuration.
There are three main advantages that Enterprise Edition will give you specific to CRM:
1. Data compression: The performance of CRM is dependent on fast disk I/O from SQL server, as the CRM application runs many small SQL queries under normal operation. SQL data compression offers the fastest possible performance by reducing the amount of disk I/O. It can also significantly reduce the size of your database. With SQL Standard you will still probably get acceptable speed, but sql compression can give you faster performance.
2. Encryption: CRM supports transparent data encryption, and that is only offered with SQL Enterprise. This is good to know if you have regulations requiring sensitive data to be encrypted.
3. Quick restore: SQL Enterprise offers data mirroring and quick restore that can be very useful for your disaster recovery strategy.
Those are the main three reasons why our customers who want true high availability performance usually go with SQL Enterprise. There are also some new capabilities in SQL Enterprise 2012, such as business intelligence features like PowerView and the new AlwaysOn features that are supported for use with CRM.
The Disadvantages of a Microsoft SQL Server:
Microsoft SQL Server is made to support millions of records across an enterprise. Because of its robust technology, it also comes with a high price. Enterprise editions of SQL Server can cost thousands of dollars, depending on the options and equipment needed to host the database application. For most small businesses, this price is too high and doesn’t fit the budget. For this reason, many small companies use MySQL for a database back-end. MySQL is free for the individual and it is widely known by Web and desktop programmers.
Microsoft SQL Server uses a core language, which is different from other database applications such as MySQL and Oracle. Companies that need programming or analysis services may find it more difficult to find a resource that hosts these services at a reasonable price. Programmers are also required to learn a different language and setup for the Microsoft SQL Server environment, so companies that want to upgrade need to teach current employees how to work with the application.
Older versions of SQL Server may install on older hardware equipment, but newer releases of the application require more advanced technologies to support the resources needed by the database. SQL Server also requires the .NET framework installed on a machine, which is a separate component used by developers. If the company plans to have a very large database, the hard drive also needs the appropriate amount of space in addition to the gigabytes of space required for the database engine alone.
–SQL Server 2012 Enterprise Edition also includes support for two-node AlwaysOn failover clusters. Microsoft will also continue to offer the free SQL Server 2012 Express Edition, which includes the new LocalDB feature for lightweight deployment.
Cognosys Provides Hardened images of SQL Server 2012 Enterprise edition on the cloud ( AWS marketplace, Azure and Google Cloud Platform).
SQL Server 2012 Standard Edition on Cloud for AWS
Features of SQL Enterprise
Performance Features of Microsoft SQL Enterprise
The Standard edition only has three of the eleven features in scalability and performance included in the Enterprise edition. Enterprise is the edition of choice if a company plans to scale in the near future. Generally, Enterprise supports up to 524 petabytes, but it can also support the maximum amount of RAM and number of core processors that the host system offers, compared to the Standard’s 128 GB.
Security Features of Microsoft SQL Enterprise
Another key component of Microsoft SQL Server Enterprise is security. Like the Standard edition, Enterprise includes basic auditing, contained databases, encryption and backups, and user-defined roles. It surpasses the Standard edition with its inclusion of fine grained auditing, transparent database encryption, and extensible key management. These are key tools to ensure maximum security.
Replication Features of Microsoft SQL Enterprise
In discussing the feature of replication, the Standard edition has most of the features including SQL Server change tracking, merge replication, and snapshot replication. Microsoft SQL Enterprise outperforms the Standard edition in Oracle publishing and peer-to-peer transactional replication.
Major Features of SQL SERVER 2012 Enterprise Edition
1. Cross-Box Scale Limits
2. High Availability
3. Scalability and Performance
6. Management Tools
7. RDBMS Manageability
8. Development Tools
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 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:
- 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 Step by Step Screenshots
Installation Instructions for Windows
Step 1) VM Creation:
- Click the Launch on Compute Engine button to choose the hardware and network settings
Step 2) RDP Connection: To initialize local DB server 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
The below screen appears after successful deployment of the image.
i) Please connect to Remote Desktop as given in step 2
ii) You can use SQL server instance as localhost. The SQL Server instance name to be used is “localhost” Connect to SQL Management Studio with username as sa and password provided in Custom Metadata.
If you have closed the deployment page you can also get the sa password from VM Details “Custom metadata” Section.
Step 5 ) Other Information:
- Windows Machines: RDP Port – 3389
- sql server port :1433: By default, this is blocked on Public interface for security reasons.