Table of Contents


1. Introduction


2. Database Server Requirements


3. Installation Prerequisites


4. Installation of SQL on a Database Server

4.1 Verification of SQL Server Installation and Configuration

4.2 How to Reset Your DB Password



1. Introduction


A Database Server is required as a prerequisite to setting up the VIDIZMO application for On-Premise/Private Cloud deployment. Click here to learn more on How To Install VIDIZMO On-Premise/Private Cloud.


The Database Server should be separate from the Application Server where the VIDIZMO Application is installed, mainly because of the factors related to security and performance. If we use the Database Server for other services required in VIDIZMO, e.g. Reporting Services used in VIDIZMO reports, we will need to configure the Web Server Role on the Database Server also, adversely affecting the Server performance. Since we only want to run the database on the SQL Server, we will be using a separate Server.


For the Database Server, SQL version to use can either be SQL 2012/2014 or SQL 2016 Express or Enterprise Edition. When using the Express Edition, it will need to be installed with Advanced Services.


When installing SQL Server 2016, SQL Server Management Tools need to be installed separately, which you can download and install by clicking here SQL Server Management Studio (SSMS). You will also need to select Mixed Authentication Mode during SQL installation to allow authentication for both SQL and Windows.



2. Database Server Requirements

Following are the minimum requirements for installing an SQL Server:

  • Database Server: 
    • SQL Reporting Services
    • Windows Server 2012 R2/2016 Standard Edition VM/Physical Server with a minimum of 8GB RAM, 4 Core CPU, 256 GB HDD with 150 Mbps Throughput
    • Microsoft SQL Server 2012/2014/2016 Express, Standard or Enterprise Edition
    • SQL Server Management Studio (will be installed separately in case of SQL Server 2016)
    • Microsoft .Net Framework 4.5.2
    • Microsoft Windows Management Framework 5.1

  • Open Firewall Ports between application and database servers: 80, 443, 11211, 1433

  • All machines should be on the same Local or Virtual Network.


3. Installation Prerequisites

Prerequisites for installation and configuration of VIDIZMO in an On-Premise/Private Cloud environment are:

  1. Installation of .NET Framework 3.5
    This will be installed on the database server. The following URL will assist you in configuring .NET Framework 3.5: https://technet.microsoft.com/en-us/library/dn482071.aspx?f=255&MSPPError=-2147217396 
  2. Installation of .NET Framework 4.5.2
    This will be installed on the application server. You should have the offline installer of Microsoft's .NET Framework 4.5.2. Follow this link to install it: https://www.microsoft.com/en-pk/download/details.aspx?id=42642        



4. Installation of SQL Server (For Database Server) 

The Database Server should be separate from the Application Server. You can either install SQL 2012/2014 (Enterprise Edition) or SQL 2016 (Enterprise Edition).


For SQL Server 2016, SQL Server Management Tools need to be installed separately, which you can download and install by clicking here SQL Server Management Studio (SSMS). You will also need to select Mixed Authentication Mode during SQL installation to allow authentication for both SQL and Windows.



For SQL 2016 (Enterprise Edition) installation, select the following features:


  • Instance Features

Database Engine Services

SQL Server Replication
Full-Text and Semantic Extractions for search
Data Quality Services
Reporting Services – Native

  • Shared Features

Client Tools Connectivity



If you are installing SQL 2012/2014 (Enterprise Edition), select the following features during the installation:


  • Instance Features

Database Engine Services

SQL Server Replication
Full-Text and Semantic Extractions for search
Data Quality Services
Reporting Services – Native

  • Shared Features

Client Tools Connectivity

Management Tools – Basic

Management Tools – Complete



Note:

For local installations, you must run Setup as an administrator. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share.




Steps

Following steps will walk you through SQL Server 2016 Installation. However, we also recommend reviewing following articles to ensure optimum configuration for your SQL Server Installation:

1. Run the Setup for the SQL Server 2016. This setup will launch the SQL Server Installation Center.

By default, you will be in the "Planning" tab. Select "Installation" tab and click on Install SQL Server Management Tools. This link will take you to the download page that provides a link to install SQL Server Management Studio. 


SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.





2. After the SQL Server Management Tools completes setup, click on "New SQL Server stand-alone installation or add features to an existing installation" to launch a Setup Wizard to install SQL Server 2016.




3. From the Setup Wizard select and enter the product key for SQL Server 2016 in its respective field. This will install the Edition of SQL Server 2016 against which the Product is licensed. Click on the "Next" button to continue.




4. The next screen will display the License Terms of the application. Read the License Terms thoroughly and select the checkbox “I accept the license terms.” Click on the "Next" button to continue.





5. To keep the SQL Server up-to-date, select checkbox “Use Microsoft Update to check for updates (recommended),” and click "Next" to proceed.





6. The setup will automatically check for "Product Updates", and install relevant updates if required. If no updates are required, the setup will move on to "Install Setup Files". After that, it will move on to check "Installation Rules". Click "Next" if all these steps are successful.


Note that you should not move on to the next step if anything fails or you get errors. All errors must be resolved.






7. The next screen, "Setup Role" is to select the features for this SQL instance. Select "SQL Server Feature Installation" option bring up the SQL Server features that you wish to include. Click on the "Next" button to continue.





8. From the "Feature Selection" screen, manually select the features for the SQL Server. Select the listed features below. (The image below may not display all features listed here). Click on the "Next" button to continue


- Instance Features

  • Database Engine Services
    • SQL Server Replication
      • Full-Text and Semantic Extractions for search
      • Data Quality Services



  • Reporting Services – Native

  • Shared Features
    • Client Tools Connectivity
    • Management Tools - Basic
    • Management Tools - Complete




9. In "Instance Configuration" screen, you will have to specify the name and instance ID of your SQL Server. This can either be set as default, or you can decide a name according to your preference.


For "Named Instance", choose a name which you will remember as it will be required when you are connecting to the server.


Here, we are using "Default Instance." Click "Next".


CvuGXxyvu6ttJcNDZ1hNm9onyfvhRDQZkQ.png



10. No configuration is required in "Server Configuration" screen. Click "Next" to continue.


eMv_D27v4H8EUurVOA5DtLlt_yPPFaz4vg.png



11. In "Database Engine Configuration", you will have to specify the authentication security mode and administrators of your SQL Server. SQL Server can operate in one of two security (authentication) modes:

1. Windows Authentication mode or Login Instance level principals allow an entity to connect to the SQL Server instance. Windows logins do not, by their nature, grant any access to databases on the instance. The exception to this is a login with SysAdmin rights can use a database because they have SysAdmin level permissions. 


SQL Users, on the other hand, are Database level principals that allow an entity to connect to a SQL Server database. Users are associated with Windows logins via SIDs, creating a relationship between the two and allowing a Windows login user to connect to the instance and then use the associated user to connect to the database.


2. Mixed Mode which allows users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication.


In this case, we are selecting "Mixed Mode". Specify the password for your SQL Server System Administration account and click on "Add Current User" to provide the current user unrestricted access to the Database Engine. Note that the current user must be a local administrator. Click "Next".


-N5vcPlfa9MpgLsAOsMeYIqZWPtS9t8NOg.png



12. On the next screen, select "Install and configure" as a configuration option for Reporting Services. Click on "Next".


CcWgPUorDNbJS5bh3EmnPLDxFdCrJFrsPQ.png



13. The SQL Server Installation program will show a report of the configuration. Click "Next" to start the installation.


cpbdXDGm3rRb4e5LjVCkj6BqmbIcKRVqeQ.png



14. The SQL Server 2016 is ready to install. Click on the "Install" button to continue.


ynes-udXdrFC7U6znu60jcflDKN3A-4WAw.png



15. The installation process will begin. It will take few minutes to complete.


dOh7cP1CSNkAwlqH5S0rJZwoydkefqNOyg.png



16. SQL Server 2016 Standard Edition is successfully installed on your system. Click on "Close" to close SQL Server 2016 Setup and SQL Server Installation Center.


GYOIJ6RXaB2LkHoa5fym6DAse5S6UNj-Ng.png




4.1 Verification of SQL Server Installation and Configuration

Once the SQL Server 2016 is installed on your system, a simple verification process is required to make sure that the SQL Server and Reporting Service is installed and configured correctly. Perform this step before proceeding further.


Steps


1. Search "SQL SERVER 2016 Management Studio" in your system and open it.


j6HXJZEX-FM__bBHRoaaLHR0nDXQyVdgoQ.png



2. As soon as the SQL Server Management Studio opens, you will have to connect to the server by either authenticating with SQL Server Authentication or Windows Authentication. Enter the password and click on "Connect." Note that the user attempting to connect to the server must be a local system administrator.


uss8pexLAyVuiogKWzRMzbMO_18CjQdI-g.png



3. SQL Server is correctly installed if the connection with the server is successful. You will see "Object Explorer" screen when you are connected.


a5JmFebK20W6_3RxYzNjVuf0M6mn4VGu-g.png




4. Right click on server name and click Properties.




5. In Properties Click on Database Settings. In Database Default Locations default path is set to C Drive for all options. It is recommended to change this Path to any other Local drive instead of C Drive (for better performance), after changing the location press OK

Selecting a local drive other than C drive is also recommended since the VidizmoDB_log file, located inside the Data folder (highlighted below), needs sufficient space to grow as it continuously appends all the transactions taking place in the VIDIZMO DB, and saves it in this file.

If the allocated storage for the log file becomes insufficient to save the appended information, the VIDIZMO application slows down and may even stop responding.




6. Run the SQL Server setup on VIDIZMO application server and only install SQL Reporting Service Feature. After successful installation, type Reporting Services Configuration Manager in Search and open it.


Ah4V8_uKZ_bNz5os0agqzRv2ECH7yWbgRw.png



7. The "Reporting Services Configuration Manager" will require connection to the server as soon as it opens. Enter the Server Name and Report Server Instance in respective fields and click on "Connect."


t6k9CAXT2tNpCGZL-KL9c7uViIx6CTfJVw.png



8. From the left panel, select "Web Service URL" and click on the highlighted URL shown below.


0G9ATVh8xCadel4wNzRNdYbpUOMtASeHEA.png



9. This will take you to an open web page in a browser. If you see no errors, then it means that the "Reporting Services" are configured correctly. Close the browser.


IPH12aln3uT2SQR6teGb6EDrelhmggHhTg.png



10. Now in the Reporting Services Configuration Manager, select "Report Manager URL" from the left panel and click on the highlighted URL shown below:


eCnol6HNCruv-M62tr5GTEPZttjS_yxO4g.png



11. Another webpage will open up in a browser:


vacrBYL7QCYbuYzskya1yElfU6kXZzzpgw.png



Again, if you see no errors, then it means that "Reporting Manager" is configured correctly. Close the browser.With the completion of these steps, the SQL Server 2016 (Enterprise Edition) is ready to use.



4.2 How to Reset Your DB Password


If you need to change the password to restrict users or the password has been compromised, you can change your DB password using the SQL Server Management Studio or by executing the ALTER LOGIN query. To change the DB password, make sure of the following:


- You have access to the VIDIZMO Server where SQL is installed

- SQL Server Management Studio is installed

- You have Administrator (SysAdmin) level permissions for SQL. 

By default, the SysAdmin user is "sa" in VIDIZMO.



You can change the DB password using the following methods:


1. Changing the DB using the SQL Server Management Studio


i. Open the SQL Server Management Studio.


ii. Expand the Security folder to get to the Logins folder. Then right-click on "sa" and view properties.sa" and view properties.


iii. On the "General" page, you will be able to enter new password values for your "sa" user. Click on the Ok button save the information.



iv. You will also need to ensure that Grant and Login Enabled are selected on the "Status" page as shown below:



 Once "sa" Password has changed successfully you can log in immediately with the new Password without restarting SQL Server.



2. Change your SQL DB password using "Alter login" query.

Use the following syntax to run the query:


ALTER LOGIN <LoginName> WITH PASSWORD = '<enterStrongPasswordHere>';

        

Changing the SQL DB password in VIDIZMO using the query requires SysAdmin rights on the "sa" user.




Permissions


Subject: Central Administration

Action: Get, Update, Add



Roles


Administrator, Manager