SSIS Series: Insert/Update a SQL Server table using Merge Join in SSIS

I was asked a question awhile back on the easiest way to do an incremental load from one SQL Server table into another SQL Server table. This is common in data warehouses or reporting tables as you wouldn’t want to truncate a large table and perform a full insert. Instead, you would want to only copy the changes from the source to the destination. This could be an insert or an update. The easiest way, IMO, is SSIS. Let’s take a look.

Here’s how my environment is setup. I have two databases appropriately named Transactional (for transactional data) and Reporting (for static report data). Two tables, Source and Destination.

I also added some dummy data as you see below. I’ve highlighted what is different and will need to be changed in the destination table.

Let’s open SSIS and create a new SSIS project.
Drag and drop a Data Flow task into the design window, right click, Edit:

Next, drag and drop two OLE DB source components into the design window. I’m going to rename mine Source and Destination to match my table names.

Configure the two OLE DB sources to match the source table and the destination table. Below is a screenshot of my source connection manager.

Once your connection managers are configured let’s drag and drop two Sort components below each OLE DB Source and connect them to each source. We will be using a merge join next and the merge join component needs to have data sorted in ASC or DESC order. I always choose to sort on the Primary Key for each table.

In my example, I’ll choose to sort on my primary key, ID.

Next, drag the Merge Join transformation into the Design window and drag the data path from the Sort component to the Merge Join. When you attach the arrow to the transformation, the Input Output Selection dialog box appears, displaying two options: the Output drop-down list and the Input drop-down list. The Output drop-down list defaults to Source Output, which is what we want. From the Input drop-down list, select Merge Join Left Input, as shown below. We’ll use the other option, Merge Join Right Input, for the other connection.

Next, connect the data path from the other Sort component to the Merge Join transformation. This time, the Input Output Selection dialog box does not appear. Instead, the Input drop-down list defaults to the only remaining option: Merge Join Right Input.

Now, let’s configure the Merge Join transformation.

The first setting in the Merge Join Transformation Editor is the Join type drop-down list. From this list, you can select one of the following three join types:

  • Left outer join: Includes all rows from the left table, but only matching rows from the right table. You can use the Swap Inputs option to switch data source, effectively creating a right outer join.
  • Full outer join: Includes all rows from both tables.
  • Inner join: Includes rows only when the data matches between the two tables.

For our example, we want to include all rows from left table but only rows from the right table if there’s a match, so we’ll use the Left outer join option.

You now need to select which columns you want to include in the data set that will be outputted by the Merge Join transformation. For this exercise, we’ll include all columns. To include a column in the final result set, simply select the check box next to the column name in either data source.

Almost finished, but first let’s add a Conditional Split transformation. This will allow us to insert new records or update previous records.

In the Conditional Split Editor, I created two outputs. If (Destination) ID is NULL then the record doesn’t exist so we’ll perform an INSERT. If the ModifiedDate is different between the two tables then we know something has been updated since the last execution and we need to update the record. See below.

Since we can perform and INSERT or an UPDATE we’ll need two destinations. First, for the INSERT, we’ll simply be doing an INSERT into the table so we can drag the OLE DB Destination component into the window, choose “INSERT” in the Input Output selection window, and use the Reporting connection manager.

I’m going to check the “Keep Identity” box since the ID column is an identity column.

Next, for the UPDATE statement we’ll drag the OLE Command component into the window and configure it. Select “UPDATE” in the Input Output Selection window.

In the Connection Managers tab, assign the connection manager for Reporting.

In the Connection Managers tab, assign the connection manager for Reporting.

In the column mappings tab, assign parameters:

Final package should look like the following:

Save and execute. You can see that we updated two records and inserted one record:

Going back to our query you can see that everything matches up now:

Backup PostgreSQL to AWS s3 in Linux CentOS

This post walks you through how to backup a PostgreSQL database to an AWS s3 bucket.

There are a few installations we’ll need to make before allowing our on-prem Postgres server to communicate with AWS.

Install pip

  1. Use the curl command to download the installation script. The following command uses the -O (uppercase “O”) parameter to specify that the downloaded file is to be stored in the current folder using the same name it has on the remote host:

    curl -O https://bootstrap.pypa.io/get-pip.py
  2. Run the script with Python to download and install the latest version of pip and other required support packages:

    python36 get-pip.py --user

    When you include the --user switch, the script installs pip to the path ~/.local/bin.
  3. Ensure the folder that contains pip is part of your PATH variable.

    ls -a ~
  4. Add an export command at the end of your profile script that’s similar to the following example.

    source ~/.bash_profile
  5. Now you can test to verify that pip is installed correctly.

    pip3 --version

Install the AWS CLI with pip

  1. Use pip to install the AWS CLI.

    pip3 install awscli --upgrade --user
  2. Verify that the AWS CLI installed correctly.

    aws --version

Now that we have AWS CLI installed, we can configure our new client. You will need AWS Access Key ID, AWS Secret Access Key, Default Region Name and Default Output Format This information you can go to the IAM AWS Section.

aws configure

To view your s3 buckets use the following:

aws s3 ls

Now that AWS is configured and we can view our s3 buckets, let’s make a backup:

PGPASSWORD="password" ./pg_dump --no-owner -h localhost -U databasename > ~/databasename.sql

To view the backup file use the following:

cd /root
dir

Now that we have a backup, let’s create an AWS s3 bucket to store them in:

aws s3api create-bucket --bucket postgres-backups --region us-west-2 --create-bucket-configuration LocationConstraint=us-west-2

Back in AWS, you can see the new bucket:

Once the new bucket has been created, let’s push the backup we took earlier to this bucket.

aws s3 cp databasename.sql s3://postgres-backups/

Install SQL Server on Windows 10 using Docker

Starting with SQL Server 2017, Microsoft announced you can run SQL in Docker containers. This is great news for DBA’s because we tend to run multiple installations of SQL Server locally and with Docker we can spin up (or remove) a SQL instance fairly quickly.

I’m not going to go into what Docker is because there’s an entire website that goes into much more detail than I ever could. This post is going to focus on a simple SQL installation on Windows 10 using Docker. Let ‘s gooooooo!

First, let’s download and install the latest version of Docker. To find the latest version, open an internet browser and navigate to https://docs.docker.com/docker-for-windows/install/. From here, click “Download from Docker Hub”

You will need to create a docker ID and password if you don’t have one. Once you have this info let’s login.

Next, let’s click “Get started with Docker Desktop” from the home page and “Download Docker Desktop for Windows”

After the download is complete, go ahead and start the install. On the Configuration screen, I’m going to Use Windows containers instead of Linux. Click OK.

Docker Desktop is installing:

 After installation succeeds, you must log out so make sure to save everything!

Upon logging back in, you will be greeted with this message, Hit OK and reboot.

After reboot, let’s open Powershell (as administrator) and issue the following statement. This will show you that Docker is installed properly and it will give you the version we are running.

Docker Version

Next, let’s do a search for a SQL Server image. Back in Docker Hub, in the search box, let’s type SQL Server and click on Microsoft SQL Server:

This page will give you all sorts of valuable information.

You can also use the following PowerShell statement to pull SQL Server images:

Docker search Microsoft

Again, from Powershell, let’s download the Microsoft SQL Server Developer Edition images. To do this use the following:

Docker pull microsoft/mssql-server-windows-developer

This will take a few minutes so grab some coffee…..or a beer.

Once the download is complete you can run the following statement to see the images downloaded:

Docker images

OK, we have Docker installed and our SQL Server image downloaded. Now the fun part….install SQL Server! In PowerShell, let’s run the following:

Docker run –name DockerSQL -d -p 14331:1433 -e sa_password= St0ngP@SSw0rd! -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

  • –name – this is the name of the container you are creating
  • -d – Runs the container in the background otherwise the container will take over your cmd when it starts
  • – p port:port – Maps a TCP port on the host environment (first value) with a TCP port in the container (second value). In this example, SQL Server is listening on TCP 14331 in the container and this is exposed to the port, 1433, on the host. I’m using 14331 because I’ll create multiple containers and each container needs a different port number.
  • -e – Creates environment variables under the container execution runtime, for example, sa password and End User License Agreement (EULA)
  • sa_password – Create sa password for SQL instance (Hint, don’t use a $)
  • ACCEPT_EULA – Yes

It will take a few minutes to finish the install, but it’s much shorter than a regular SQL install. Once finished, run the following to get the IP address of the container (make sure to use your container name after docker inspect):

$Container_1 = docker inspect DockerSQL |
ConvertFrom-Json |
select -ExpandProperty SyncRoot |
select -expand netWorkSettings |
select -ExpandProperty Networks |
Select -ExpandProperty nat |
Select IPAddress
$Container_1

As you can see, the IP for my container is 172.31.29.174. If I open SQL Server Management Studio and connect to this IP using the sa account and password I created I can connect successfully:

If you have any issues connecting try using the following docker statement to view the logs:

Docker container logs –details DockerSQL

To remove the container, simply stop the container

Docker stop DockerSQL

And remove.

Docker rm DockerSQL

Introducing SIOS high availability for SQL Server 2008/R2 in Azure

Support for SQL Server 2008 and 2008 R2 ended on July 9, 2019. That means the end of regular security updates. However, if you move those SQL Server instances to Azure or Azure Stack, Microsoft will give you three years of extended security updates at no additional cost. If you’re currently running SQL Server 2008 or 2008 R2 and you are unable to update to a later version of SQL Server, you will want to take advantage of this offer rather than running the risk of facing a future security vulnerability. An unpatched instance of SQL Server could lead to data loss, downtime, or a devastating data breach.

If you have a SQL Server failover cluster instance (FCI) or use hypervisor-based high availability, or other clustering technology on-premises for high availability, you’ll probably need the same in Azure. If you need to migrate to Azure/Azure Stack at end of support, and you require high availability for SQL Server 2008/2008 R2, there’s only one solution recommended by Microsoft: SIOS DataKeeper. SIOS DataKeeper enables clustering in the cloud, including the creation of a SQL Server 2008/2008 R2 failover cluster instance, allowing you to achieve your high availability goals.

Click here to view the rest of this post.

Planning and Building Microsoft SQL Server on Amazon RDS

Planning and building SQL Server in RDS doesn’t have to scare you. It’s actually pretty easy and in this post will go over planning a SQL Server deployment in RDS, creating SQL Server in RDS, and last but not least configuring the new instance of SQL Server.

Let’s jump in…

Planning the deployment is important because if your SQL Server instance needs a feature that RDS doesn’t support, then RDS isn’t an option. Here are a few, but you can get the full list here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

  • Log Shipping
  • SSRS, SSIS, SSAS, Data Quality Services, or Master Data Services
  • Database Mail
  • Maintenance Plans
  • SQL Server Audit
  • FILESTREAM support
  • Policy-Based Management
  • Replication

RDS also has some limitations that don’t exist with an on-premise SQL Server. Let’s look at a few:

  • As of this post, Amazon only supports SQL 2017 RTM, SQL Server 2016 SP2, SQL Server 2014 SP2, SQL Server 2012 SP4, and SQL Server 2008R2 SP3
  • You can only have a maximum of 30 databases per instance and 16TB of storage.
  • Linked Servers
  • Windows Authentication (must use AWS Managed Microsoft AD)

Again, the full list can be found here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html          

Pricing

Once you can verify that your environment will run properly in RDS you’ll need to look at the pricing model. When you setup RDS for SQL Server, the software license is included. AWS used to have a program called “Bring your own license” or “BYOL”, which allowed you to use a license that was already bought from Microsoft via an agreement or other. This has been rumored to expire on June 30, 2019. The software license that is included means that you don’t need to purchase SQL Server licenses separately. AWS holds the license for the SQL Server database software. Amazon RDS pricing includes the software license, underlying hardware resources, and Amazon RDS management capabilities. The pricing will depend on the selections such as size, edition, etc.

The following editions are supported in RDS:

  • Enterprise
  • Standard
  • Web
  • Express

Notice, Developer Edition is not included with RDS and Web Edition supports only public and internet-accessible webpages, websites, web applications, and web services.

Instance Type

You can also choose from On-demand or reserved instances. On-Demand DB Instances let you pay for compute capacity by the hour your DB Instance runs with no long-term commitments. This frees you from the costs and complexities of planning, purchasing, and maintaining hardware and transforms what are commonly large fixed costs into much smaller variable costs. This is good for development environments where you can power on and off the server as it’s being used.

Reserved Instances give you the option to reserve a DB instance for a one or three year term and in turn receive a significant discount compared to the On-Demand Instance pricing for the DB instance. Amazon RDS provides three RI payment options — No Upfront, Partial Upfront, All Upfront — that enable you to balance the amount you pay upfront with your effective hourly price.

You can see more about on-demand vs reserved instances and pricing here: https://aws.amazon.com/rds/sqlserver/pricing/   

RDS provides a selection of instance types optimized to fit different relational database use cases. Instance types comprise varying combinations of CPU, memory, storage, and networking capacity and give you the flexibility to choose the appropriate mix of resources for your database. Each instance type includes several instance sizes, allowing you to scale your database to the requirements of your target workload. View more details here: https://aws.amazon.com/rds/instance-types/       

Storage

Another item to look at when planning your deployment is storage. RDS uses Amazon Elastic Block Store (Amazon EBS) volumes for database and log storage. Depending on the amount of storage requested, Amazon RDS automatically stripes across multiple Amazon EBS volumes to enhance performance.

RDS offers three different storage types:

General Purpose SSD – also called gp2, this storage type offers cost-effective storage that can be used for a broad range of different workloads. These volumes deliver single-digit millisecond latencies and the ability to burst to 3,000 IOPS for extended periods of time. I would recommend putting small to medium sized databases on this type.

Provisioned IOPS – This storage type is designed for I/O intensive workloads, particularly database workloads that require low I/O latency and consistent throughput. This is also built on SSD and targeted for IO intensive, high performance databases. Cost wise, this is the highest of the three storage types.

Magnetic – This storage type is mostly used for backward compatibility. Amazon recommends using gp2 or Provisioned IOPS for any new builds. This is ideal for test and dev environments when performance isn’t a concern. This is the cheapest of the three storage types.

See more about storage here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html     

Network

One more item to consider when planning the deployment is network connectivity. Applications will more than likely need to connect to your RDS environment so there are a few import concepts to look at it.

Availability Zones – this is simply a data center in an AWS region. The following AWS regions exist.

RDS Availability Zones

Virtual Private Cloud – also called VPC, this is an isolated virtual network that can span multiple Availability Zones. It’s used to group different types of resources to the network that need to talk to each other.

Virtual Private Cloud – also called VPC, this is an isolated virtual network that can span multiple Availability Zones. It’s used to group different types of resources to the network that need to talk to each other.

DB Subnet Group – this is a collection of subnets inside a VPC that contains the RDS instance addresses. See more about VPC and DB subnet groups here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.WorkingWithRDSInstanceinaVPC.html

Create SQL Server RDS via AWS Console

Now that we’ve outlined some of the deployment planning tasks, let’s build an instance through the AWS console.

Once inside the console, we’ll click on RDS under the Database heading:

RDS

Once we are on the home page for RDS, we can click Create Database under Get Started. There’s also info for Pricing and costs and some documentation on getting started:

RDS Homepage

Notice in the top right corner is the Availability Zone in which you are logged into. In my case, I’m logged into US East (Ohio) since I’m on the Central Time Zone and Ohio is located closer to me than any other zone:

RDS Availability Zone (selected)

Back to the Select Engine page. For this post, I’m going to install Microsoft SQL Server Express, but you can see the other database engine platforms that are available and the associated editions:

RDS Database Platforms

Next page we can see some of our database details. There are all items we discussed in the planning deployment section above. License model, DB engine version, DB instance class, Time Zone, Storage Type and allocated storage are all configurable on this page. Below are my selections:

RDS SQL Instance Specifications

Scroll down to Settings header and configure the DB instance identifier, Master username and password. The DB instance identifier is a unique name for your DB instances across the current region. For this RDS instance I’ll name it SQLFreelancer:

RDS Database Identifiers

On the Advanced Settings page we’ll configure Network and Security, Windows Authentication, Database Options such as port number, Encryption (where available), Backup retention, Monitoring, Performance Insights, Maintenance options, and Deletion protection. I’m going to choose all the defaults for this post, but this is a page where you want to make sure you choose what is best for your environment.

RDS Database Advanced Settings

Once you are finished on the Advanced Settings page, click Create Database.

Now how easy was that? Creating a new DB instance took about 2 minutes. Once your instance is created let’s click on View DB Instance details:

The details page gives you all sorts of info about your instance:

RDS Database Summary

Microsoft SQL Server on Amazon RDS in a Nutshell

As defined by Amazon, Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups. It frees you to focus on your applications so you can give them the fast performance, high availability, security and compatibility they need.

RDS is also referred to as a Database as a Service (DbaaS) or Platform as a Service (PaaS) not to be confused with Infrastructure as a Service (IaaS) which we’ll discuss in the next paragraph.

DbaaS vs. IaaS

DbaaS IaaS
You can choose any DB platform such as Oracle, MySQL, SQL Server, Amazon Aurora, PostgreSQL, and MariaDB You create a Virtual Machine and install OS and DB platform such as SQL Server
DbaaS takes care of backups, High Availability, Patching, OS, underlying hardware Iaas will only take care of the VM host layer and it’s hardware. You will need to manage patching, HA, security, etc. This is essentially like an on premise server.

Being an Operational DBA, there are a few tasks that RDS will take over freeing up time for the DBA to focus on other things. Some of those tasks include the following:

  • Backups: RDS will continuously take backups and allow point in time restore capabilities. We no longer have to worry about disk space or archiving backups to another location.
  • HA: RDS can automatically setup mirroring to another data center which allows for redundancy of databases.
  • Patching: RDS will automatically patch your SQL Server based on a maintenance window defined by you.
  • Add Resources such as CPU/Memory: RDS can increase CPU or Memory on demand as opposed to managing an on premise where the server might need downtime and you would have to orchestrate the change with Server Administrators.
  • Upgrade: With a push of a button you can automatically upgrade SQL Server and easily roll back if necessary.
  • Monitoring: Instead of buying a third party monitoring tool and running through the setup RDS provides a service called CloudWatch that can easily tap into SQL Server and alert when things go wrong.

Wow! All of these items make managing a SQL Server much easier for a DBA right? Do you even need a DBA if you’re running RDS? Of course you do! While it does make some tasks easier for a DBA, RDS will not do the following:

  • Write queries, tune queries, test queries: RDS has no knowledge about the data in each DB. Only a DBA knows the application and business processes to write and tune queries.
  • Manage DB security, change control, configuration settings: Only a DBA familiar with all of the procedures of his/her company can really make sure the environment is secure, that all changes are being documented, and that a specific configuration applies to what the databases are supposed to do.
  • Tune indexes or maintenance: Again, only the DBA knows what databases might need indexes or aren’t using specific indexes. You also know when to run maintenance procedures.

Now that we’ve discussed some of the pros and cons, why do businesses use DbaaS?

The speed of provisioning increases business value because instead of waiting weeks to bring a server online including purchasing software, servers, licenses, managing resources, etc. you can click a few buttons in the Amazon console and have a fresh SQL Server online in a matter of minutes.

The automation of regular tasks means there’s less possibility of human mistake and less hours spent by the admins patching and managing certain parts of the servers, which means no more late nights. Employees can now spend more time query tuning, deploying new functionality, and making sure the performance is the best. All of this leads to increased revenue for the business.

Using Bookmarks in Power BI

Using bookmarks in Power BI help you capture the currently configured view of a report page, including filtering and the state of visuals, and later let you go back to that state by simply selecting the saved bookmark.

You can also create a collection of bookmarks, arrange them in the order you want, and subsequently step through each bookmark in a presentation to highlight a series of insights, or the story you want to tell with your visuals and reports.

In this post we’ll quickly go over how to create a few bookmarks and view them as a slideshow if you will.

I’m going to use my March Madness Report I created in an earlier post. Once my report is opened in Power BI Desktop, I’m going to click on the View tab in the ribbon and select “Bookmarks Pane”

Bookmarks Pane

This should bring up a new Bookmarks pane inside PBI Desktop:

Bookmarks

Remember, bookmarks are used to capture the current view of the report so I’m going to use the default view where I’m showing all data and I’m going to name the bookmark “Home”. Make sure all filters are selected to show all data and click Add under the bookmark pane. This will create a new Bookmark, named Bookmark 1. Click the ellipsis and select rename to rename the bookmark appropriately.

Next, I like North Carolina, so I’m going to go to my Team Filter and choose North Carolina which will show me data for only this team.

Team Filtered Power BI Report

In my bookmark pane, I’m going to click Add again and rename to North Carolina.

Next, I want to view data on North Carolina from 2000 to present so I’ll change the Year Filter.

Team and Year Filtered Power BI Report

In my bookmark pane, I’m going to click Add again and rename to North Carolina 2000-present.

Now, if I click on any of bookmarks, it will take me to the data that was saved for each. This is a great way to present data in a meeting/conference so you don’t have to manually change the filters during the engagement.

We can also click the View button in the Bookmark pane to view a slideshow using the arrows at the bottom to navigate:

Create AWS Windows VM

I wrote a post a few weeks about creating an Azure Windows VM so wanted to follow up with a post about creating an AWS Windows VM to compare both platforms. I like Azure and AWS so I’m not going to throw either one under the bus. Both are great and easy to use.

Let’s create an AWS (EC2) Windows VM.

Log into the AWS portal and click on EC2 under All Services, Compute:

AWS Management Console

Next, click Launch Instance:

AWS Create Instance

Step 1 allows you to choose an Amazon Machine Image or AMI. There are tons of options here, but for this post, I’m going to use Microsoft Windows Server 2019 Base

Windows 2019 Base

Once I click Select, I’m brought to Step 2: Choose an Instance Type. Instance Types comprise varying combinations of CPU, memory, storage, and networking capacity and give you the flexibility to choose the appropriate mix of resources for your applications. Each instance type includes one or more instance sizes, allowing you to scale your resources to the requirements of your target workload. More info here: https://aws.amazon.com/ec2/instance-types/

For this post, and for cost sake, I’m going to use the free tier t2.micro type which is 1 CPU, 1GB RAM

Once I’ve selected my instance type I’ll click Next:Configure Instance Details.

Step 3: Configure Instance Details is where we’ll configure our new server. Let’s go down the list.

  • Number of Instances – This is the number of servers you want to create. If you need 5 of the same servers, this makes it easy.
  • Spot Instances – A Spot Instance is an unused EC2 instance that is available for less than the On-Demand price. Because Spot Instances enable you to request unused EC2 instances at steep discounts, you can lower your Amazon EC2 costs significantly. The hourly price for a Spot Instance is called a Spot price. The Spot price of each instance type in each Availability Zone is set by Amazon EC2, and adjusted gradually based on the long-term supply of and demand for Spot Instances. Your Spot Instance runs whenever capacity is available and the maximum price per hour for your request exceeds the Spot price.
AWS Spot Instances
  • Network – The network you want the new server to join. You can create an Amazon Virtual Private Cloud (VPC) and select your own IP address range, subnets, gateways, etc. Learn more here: https://us-east-2.console.aws.amazon.com/vpc/home?region=us-east-2
  • Subnet: the range of IP addresses in your VPC that can be used to isolate different EC2 resources from each other or the internet.
  • Auto-assign Public IP – requests a public IP address from Amazon’s public IP address pool, to make the server reachable from the internet.
  • Placement Group: You can launch or start instances in a placement group, which determines how instances are placed on underlying hardware. When you create a placement group, you specify one of the following strategies for the group:
    • Cluster – clusters instances into a low-latency group in a single Availability Zone
    • Partition – spreads instances across logical partitions, ensuring that instances in one partition do not share underlying hardware with instances in other partitions
    • Spread – spreads instances across underlying hardware
    • There is no charge for creating a placement group. Learn more: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/placement-groups.html
  • Capacity Reservations – enables you to reserve capacity for your Amazon EC2 instances in a specific Availability Zone for any duration. This gives you the ability to create and manage capacity reservations independently from the billing discounts offered by Reserved Instances (RI). By creating Capacity Reservations, you ensure that you always have access to EC2 capacity when you need it, for as long as you need it.
  • Domain join directory – enables you to join a domain that you’ve already created.
  • IAM role – automatically deploys AWS credentials to resources that assume it.
  • Shutdown behavior – specifies what happens when an OS level shutdown is performed.
  • Enabled termination protection – You can protect instances from being accidentally terminated. Once enabled, you won’t be able to terminate the instance until this option has been disabled.
  • Monitoring – Monitor the instance with Amazon CloudWatch.
  • Tenancy – You can select to run your server on a shared server or a dedicated server.
  • Elastic Graphics – Enables graphic acceleration.

For this post I’ll use defaults and click Next.

Step 4 is Add Storage.

I’m not going to go over each Storage option, but you can get more info here: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/Storage.html?icmpid=docs_ec2_console

AWS Storage

Selecting default and clicking next.

Step 5: Add Tags.

Like Azure, A tag consists of a case-sensitive key-value pair. For example, you could define a tag with key = Name and value = Webserver. A copy of a tag can be applied to volumes, instances or both. Tags will be applied to all instances and volumes

Click Next.

Step 6 is Configure Security Group

A security group is a set of firewall rules that control the traffic for your instance. On this page, you can add rules to allow specific traffic to reach your instance. For example, if you want to set up a web server and allow Internet traffic to reach your instance, add rules that allow unrestricted access to the HTTP and HTTPS ports. By default, the RDP port is added, but it allows all IP addresses to connect. Changing the Source column will allow you to filter what IP’s are able to RDP into the server. For this post, I’m going to change the Source column to allow “My IP”

AWS Security Groups

Next…and last page is a summary of the options selected. To finish configuring the instance, click Launch.

AWS EC2 Summary

After clicking launch, you will see a popup where you can create or use an existing key pair. A key pair consists of a public key that AWS stores, and a private key file that you store. Together, they allow you to connect to your instance securely. For Windows AMIs, the private key file is required to obtain the password used to log into your instance. For Linux AMIs, the private key file allows you to securely SSH into your instance.

Once the new VM is created, you can go back to the EC2 dashboard and click on Instances to see the new VM:

AWS EC2 Dashboard