Wednesday, September 26, 2018

How to Create and Delete Checkpoints (Snapshots)

Checkpoints, or snapshots in previous versions of Hyper-V, can be a great tool for either a test environment or when making significant changes to a VM. The process of creating or removing checkpoints is fairly simple.

How to Create Checkpoints

In our example we will pretend we have a 3rd party application running on the VM TGLAB-WSUS01. The 3rd party application is going to receive a major upgrade, so we want to create a checkpoint to save the state of the VM in case the upgrade goes wrong.
To create a checkpoint, simply right click on the VM and select Checkpoint:
1-create
The checkpoint will be created and the status column will show the status of “Creating Checkpoint” followed by the progress percentage of the task:
2-status
When you left click on the VM the checkpoints will be displayed in the checkpoint window pane:
3-checkpoint
If we browse to the location of the VHD file, we can see that a AVHDX file has been created. The virtual machine is then configured to use the AVHDX file and the contents of the VM’s memory are written to it:
4-cpdir
Inside the contents of the snapshots folder, we can see the checkpoint configuration file (.xml) and the virtual machine saved state files (.bin and .vsv).
5-cf
6-vmst
Looking at the hard drive settings of the VM we can see that it is now configured to use the AVHDX file instead of the VHD:
7-edit
Also notice that the Edit button is now greyed out because there is now an existing checkpoint for this VM. This is for good reason, because you can potentially damage the VHD file if changes are made while a checkpoint is in play.
Checkpoints can also be renamed to provide organization. In the example we will right click on the checkpoint we just created and click Rename. We will rename it to “Before Upgrade”:
8-newcp
Note: You should never create checkpoints on a VM that is running the Domain Controller role. Restoring those checkpoints can cause damage to the Active Directory environment.

How to Apply Checkpoints

Lets say our 3rd party application upgrade went horribly wrong. Now we need to restore the VM back to the state of the previous checkpoint we created. To do this, we simply right click on the checkpoint we want to restore to and select Apply:
9-apply
A window will appear asking if you’d like to apply the checkpoint for sure. Since we don’t want to save the current state of the VM we will just click Apply:
10-applywindow
The VM will then be restored back to the state of the checkpoint selected, which in our example is before we did the upgrade. Also note that applying the checkpoint does not cause the checkpoint to be deleted.

How to Delete Snapshots

After a successful application upgrade on TGLAB-WSUS01, we no longer need to save the previous state of the VM and can remove the checkpoint. This step can be easily missed, but it’s important to remove the snapshot and not leave it out.  To remove the checkpoint, we simply right click on the snapshot we want to remove and select Delete Checkpoint. The checkpoint will be removed:
11-delete
If we browse to the location of the VHD, we can see that the AVHDX file has been removed and the contents have been merged with the parent (which is the VHD file in our example since we only had one checkpoint):
12-nocpfile
Also, if we open up the snapshots folder we see that the checkpoint configuration file and virtual machine saved state files have been removed:
13-nosnapshot

Working with Multiple Checkpoints

Using multiple checkpoints can be very handy in a test environment. It is definitely more efficient from a storage perspective to have a test VM with multiple checkpoints consisting of various VM configurations as opposed to creating a bunch of test VMs for each configuration. However, managing multiple checkpoints can be a little confusing if you can’t find which checkpoint your VM is running from.
In our example we have a  VM called Test-VM. The purpose of this VM is to simulate the different workstation builds in the company so that we can run tests on each build before deploying out new software. The checkpoint tree structure is configured with the following setup:
14-cpbranch
There are checkpoints of each department’s workstation build ( Accounting, Engineers, HR, and Sales) and sub checkpoints of various configurations.
Lets say the accounting department is going to be using a new web application. We need the VM to be running the accounting workstation build in order to test the functionality of the app by applying the “AV Installed” checkpoint of the “Accounting” checkpoint branch. To do this, simply right click on the “AV Installed” checkpoint and select Apply:
15-cpbranchapply
After the checkpoint is restored, the checkpoint window pane now displays which checkpoint the VM is currently using and we are all set to perform testing:
16-newcpbranch
When deleting a checkpoint in a branch there are two options available. You can either delete the checkpoint and the sub tree, or you can just delete the checkpoint. In our example we want to delete the checkpoint “Before Update” but keep the checkpoint “After Update”. To do this, we simply right click on the “Before Update” checkpoint and select Delete Checkpoint. The checkpoint is removed and now the checkpoint hierarchy looks like this:
17-cpbranchdeleted
Now, lets say that the accounting department is going to use the same workstation build as HR and we no longer need the accounting checkpoint branch.  To delete a checkpoint and the sub tree checkpoints, right click on the checkpoint and select Delete Checkpoint Sub-tree:
18-cpbranchdeltree
A window will pop up asking if you are sure you want to delete the checkpoint subtree, click Delete. The entire checkpoint tree will be removed.
Deleting the checkpoint sub-tree deletes the configuration file and the virtual machine saved state files of all the checkpoints in that tree. If the VM running is currently using a AVHDX that is a checkpoint in that sub-tree, then that AVHDX file will be merged into the next AVHDX parent that is not in the deleted sub-tree. If the VM is not running from any checkpoints in that tree, then the checkpoints files are just deleted without any merging taking place.
In the example above since we deleted the “Accounting” sub-tree and were running off of a checkpoint in that tree, the AVHDX file was then merged with the next parent checkpoint that is not being deleted which is “Base Windows Image”. The “Now” display arrow is now showing as running from this parent checkpoint:
19-notree

Each VM can have up to a limit of 64 checkpoints. However, each checkpoint will still consume space so be sure to remove any unnecessary checkpoints.

Tuesday, September 18, 2018

Step by Step Installation of SQL Server 2014 on a New Failover Cluster

For our example, we have a two node cluster based on the Windows Server 2012 R2 operating system where we will be installing SQL Server 2014. The name of the nodes are SQL-NODE1 and SQL-NODE2. We need a unique and unused IP address and SQL Server Network Name to complete this installation. We will use MSSQLCLUSTER as the network name and the IP address 10.2XX.XX2.1X. (note: X represents a numeric value, I put X because I do not want to disclose my IP address due to security reasons). So let's start the SQL Server installation on node SQL-NODE1. Before we begin, make sure the cluster resources (like the shared drives) are accessible by SQL-NODE1.

SQL Server 2014 cluster Installation

Step 1: Go to the SQL Server setup file location. Right click on setup.exe and choose "Run as administrator". The SQL Server Installation Center will appear on your screen as shown in the screenshot below. Select the "Installation" tab from the left pane and click on "New SQL Server failover cluster installation" from the right pane.
SQL Server Installation Center
Step 2: Once you click on "New SQL Server failover cluster installation", the SQL Server installation window will appear and it will ask you to enter your product key for SQL Server. Enter the key or if it is an evaluation version choose the first option. Sometimes the product key option will be grayed out, so in that case you can just click the Next button.
Install a SQL Server Failover Cluster
Once you click on Next, another window will load and ask you to click on the check box to accept the license terms and conditions for the SQL Server product. Click on the Next button after accepting the license terms.
License Terms for SQL Server Cluster Installation
Step 3: After accepting the license terms, SQL Server will start checking the global rules and display the status of all the rules. If any rule fails, setup will not proceed further and you need to first fix the issue. You can see below the rules are successful, so click on the Next button to proceed.
Global Rules Check for a SQL Server Cluster Installation
Step 4: The next window will ask you to check for Microsoft Updates for Windows and SQL Server 2014. I have not checked this option, but if you want to check for updates from Microsoft you can click on the check box in the below screenshot.
Microsoft Updates Configuration for a SQL Server Cluster Installation
Once you click on the Next button of the above screen, the Product Update window will appear. As I have not chosen Microsoft updates in last window, the error is indicating that "SQL Server could not search for updates..." as shown in the below screenshot. Click on the Next button to proceed.
Windows Update Service Error during a SQL Server Cluster Installation
Step 5: The next window will check failover cluster rules. We can see all rules have been checked with both successful and warning results. We can skip warnings at this point in time as we can fix them post installation. Click on the Next button to proceed with the installation.
Failover Cluster Rules Check during a SQL Server Cluster Installation
Step 6: The next interface is the setup role window, where you choose the installation features. You will find two options on this page. One is to configure SQL Server feature installation and another is to configure SQL Server PowerPivot for SharePoint. Since I have to install the SQL Server database engine, I will choose the first option. Once you choose your setup role, click on the Next button.
SQL Server Feature Installation of PowerPivot for SharePoint
Step 7: The next step in the process is for feature selection. Choose the features you want to install on your cluster server. Do not select all features if you will not use them, as this will use server resources that could be used by the SQL Server database engine or Windows. I have selected the SQL Server Database Engine along with a few shared features as shown in the below screenshot. You can also change the root directory for each of the selected features and for the SQL Server binaries.
Feature Selection during a SQL Server Cluster Installation
Click the Next button after selecting the features you want to install. Another window will check the feature rules. Once every rule is checked, you can proceed again by clicking on the Next button.
Step 8: Our next interface is the Instance Configuration window. This is an important step because in this step you enter the SQL Server Network Name along with the Instance ID. I have chosen "MSSQLCLUSTER" as network name and default instance (MSSQLSERVER) as the instance id. Now click on the Next button to go to the next window.
Instance Configuration during a SQL Server Cluster Installation
Step 9: Next window is for cluster resource group. You can change the cluster resource group name, but I have kept it as and clicked on the Next button to proceed.
Cluster Resource Group during a SQL Server Cluster Installation
Step 10: The Cluster Disk Selection window is where you will see the list of all shared disks that you can include in the SQL Server cluster resource group. I have selected all three shared disks and clicked on the Next button to proceed. If you have multiple shared disks, make sure to select only those that will be part of this cluster.
Cluster Disk Selection for a SQL Server Cluster Installation
Step 11: Next window is to configure the cluster network. Here you need to enter your unique and unused IP address in the address column as shown in below screenshot. This is the same IP address which I talked about in the beginning of this tip. Enter the IP address and click on the Next button.
Cluster Network Configuration for a SQL Server Cluster Installation
Step 12: Once you click Next in the above step, it will ask you to enter the SQL Server service accounts and their passwords to run the SQL Server services. I used two service accounts, one for SQL Server and another for SQL Server agent. Enter the credentials and click on the Next button to go to the database engine configuration page.
Server Configuration for a SQL Server Cluster Installation
Step 13: Next window is very important and here setup will ask you to configure the SQL Server database engine. There are three tabs in this window. First is 'Server Configuration', second is 'Data Directories' and third is 'FILESTREAM'. We will start configuring with the first tab that is server configuration. The below screenshot explains this window. We need to select the authentication mode of the SQL Server. I have selected "Mixed Mode" and entered the sa password. Make sure to enter a complex password otherwise it will not allow you to proceed. Now click on "Add Current User" to add yourself as an administrator on SQL Server. You can add other accounts as well which should be admins in SQL Server.
Database Engine Configuration for a SQL Server Cluster Installation
Now click on "Data Directories" to configure the data directories where the system and user databases along with backup files will be placed. I have placed system databases except TempDB, user databases and backups on shared disks, but left TempDB on a local drive (C drive).
SQL Server failover cluster installation supports Local Disk only for installing the TempDB files. Make sure that the path specified for the TempDB data and log files exists on all the cluster nodes. If the TempDB directories are not available on the failover target node during failover, the SQL Server resource will fail to come online. Another advantage of placing TempDB on a local disk is that it creates separate paths of traffic by having your data and log files on the SAN while TempDB is on the local disk.
Database Engine Configuration for a SQL Server Cluster Installation
I do not need FILESTREAM to be enabled, so I will not configure it and click Next to proceed. If you need FILESTREAM click on the third tab and follow the process to setup.
Once you click Next, the below screen will appear to have you confirm that you are putting TempDB files on local drives.
Tempdb File Configuration Warning on a Local Disk
Click on Yes and it will check the configuration rules of this installations. Once all rules have passed, click on Next to reach the final installation screen.
Feature Configuration Rules for a SQL Server Cluster Installation
Step 14: Here you can check the features and configurations you have selected. You can also see the configurationFile.ini file in the highlighted area that will be used by the setup to install SQL Server.
Ready to install window for a SQL Server Cluster Installation
Now click on the Install button to start the installation process of SQL Server. You can see the installation is running based on the progress in the below screenshot.
SQL Server Cluster Installation Progress
Once the installation successfully completes, you will get the below screen with confirmation that all the features you have selected are installed.
Completed SQL Server Cluster Installation

Validate the Installation

Now we have installed SQL Server 2014 on cluster node SQL-Node1. So let's validate that SQL Server has been successfully installed. You can open the SQL Server Failover Cluster Manager and click on Roles from the left pane. You will be able to see that the SQL Server default instance has been added. You can also see the SQL Server Network Name "MSSQLCLUSTER" along with the IP address in the right pane.

Review the Installation in the Failover Cluster Manager

Sunday, September 16, 2018

Sample Oracle Database for Learning SQL

1- Introduction

LearningSQL is a small database, used as an example in the instructions of learning SQL on the website of  o7planning. There are 3 version on Databases:
  • Oracle
  • MySQL
  • SQLServer.
In this document, I will instruct you to create this database on Oracle.
This database is used in the documentation to learn SQL (Oracle) at:

2- Download Script

Download script at:
Direct Mediafire
Download Download
With Oracle, you just need to care about the file:
  • LearningSQL-Oracle-Script.sql

3- Run Script

There are two ways to create SCHEMA: LearningSQL
  1. Using SQL Plus
  2. Using visual tools.

3.1- Create SCHEMA LearningSQL using SQL Plus

Run 2 commands:

1
2
3
4
5
6
-- Create Schema named learningsql,  password 1234
Create user learningsql identified by 1234;
 
-- For learning purposes:
-- Grant highest privilege DBA (DB Admin) for the newly created user.
grant dba to learningsql;
Connect to the newly created SCHEMA:

1
2
3
4
-- Connect to Schema
-- Syntax: connect /@
 
connect learningsql/1234@db11g;

Run Script:


1
2
3
-- Run Script file in SQL Plus
 
@F:\LEARNING_SQL\LearningSQL-Oracle-Script.sql
Script run successfully:

3.2- Create SCHEMA LearningSQL using visual tool

On the visual tools (Here I use PL/SQL Developer), connected to the user with DBA authority (for example: system).
See more:
Execute script to create schema: learningsql.

1
2
3
4
5
6
7
8
-- Create schema named learningsql
 
Create user learningsql identified by 1234;
 
-- For learning purposes:
-- Grant highest privilege DBA (DB Admin) for the newly created user.
 
grant dba to learningsql;
Connect to Schema you just created:
Copy content of LearningSQL-Oracle-Script.sql into PL/SQL Developer and execute.
Create Schema success.

4- Overview LearningSQL Database

LearningSQL is a small database simulating the data of a bank:
Table Name Description
ACCOUNT The table saves the bank accounts. Each of customer can register multiple accounts each of which corresponds to a product type provided by the bank.
(See more: PRODUCT)
ACC_TRANSACTION The table saves the transaction history between the bank and a certain account.
BRANCH The branch of bank.
BUSSINESS  
CUSTOMER The table of customers
DEPARTMENT The department table of bank
EMPLOYEE The employee table of bank
OFFICER  
PRODUCT The products and services of bank, such as:
  • Savings account
  • The business lines of credit
  • The loans to small business
  • .....
PRODUCT_TYPE The types of products and services, such as:
  • Bank acounts
  • Loans to individual and business
  • The provider of insurance.

5- The structure of the tables


5.1- ACCOUNT

5.2- ACC_TRANSACTION

5.3- BRANCH

5.4- BUSINESS

5.5- CUSTOMER

5.6- DEPARTMENT

5.7- EMPLOYEE

5.8- INDIVIDUAL

5.9- OFFICER

5.10- PRODUCT

5.11- PRODUCT_TYPE