Archive for August, 2014

How To Upload and Download Files Using S3?

What is AWS S3?
Amazon S3: Simple Storage Service. Notice the 3Ses. It is an online file storage web service offered by Amazon Web Services (AWS).

Uploading and downloading files using S3 is remarkably easy. In this blog, we will explore how to upload a database backup file and then download it.

Uploading a file:
1. Logon to your AWS console and then click on S3:
S3

2. Create a new bucket to storage the file(s) to be uploaded:
Create Bucket

3. Name the bucket. No underscores”_”. It has a number of limitations. The best part of it is that it will tell you instantly if you violated the naming standards and unless you correct them, you will not be able to go to the next step.
Name the Bucket

4. If the bucket creation is successful, you will see the bucket in the “All Buckets” list:
Bucket Created

5. To upload a file to the designated bucket, click on the bucket name then click on the “Action” drop-down button, and then click on the “Upload” option:
Upload

6. Click on the “Add files” button:
Add Files

7. Browse to the file you wish to upload and then select it to open:
Select File to Upload

8. Click on the “Start Upload” button to upload:
Start Upload
9. You will see the file upload transfer in progress:
Upload in Progress

10. Once the upload is completed successfully, you will see the green check mark next to the “Done” button:
Upload Done

Downloading a file:
1. Browse to the corresponding bucket and select the file you wish to download:
Download

2. Click on the “Download” button on the following prompt:
Download Link

3. Download will start to download the file select to your specified location:
Download in Progress

Taking and Restoring a Snapshot for Migrations in the same AWS Account

You have created a Development SQL Server RDS instance and migrated all the required databases, logins, and jobs from another SQL Server instance. The hard work and heavy lifting are now done. The Development Team and the QA Team have tested, verified, and approved to create the Test SQL Server RDS with the exact same specifications as the Development SQL Server RDS instance. They opened the ticket for you on a Friday afternoon at 3:00PM and wish to have the Test SQL Server RDS instance ready by Monday morning at 9:00AM. They have also carbon copied everyone under the sky that it is a high priority item. You need to catch a 7:45PM flight to head out to the Midwest for a gathering. You need to be in the airport by 6:00PM. What do you do? Stopped getting frustrated for a second and head down to the nearest Starbucks and get yourself a cup of Mocha Frappuccino with no cream.  Once you are back to your office, take a snapshot of the Development SQL Server RDS instance and then restore it to create the Test SQL Server RDS instance. Yes. It is really that simple with a few caveats.

Assumptions:

  1. The Development and Test SQL Server RDS instance are all located on the same AWS account.
  2. The Mocha Frappuccino with no cream has helped to calm you down and you are focused on creating the Test SQL Server RDS instance rather than sending out angry emails.

Implementation Steps:

  1. Logon to the AWS Console, click on RDS and then click on Instances.  Select the Development SQL Server RDS instance. Click on the “Instance Actions” drop-down arrow and then click on “Take DB Snapshot”:
  2. Taking Snapshot

  3. Name the snapshot accordingly. Eg.: DBInstanceName-SS-08242014.
  4. Back on the RDS Dashboard, you will see the snapshot in creating progress.
  5. Creating Snapshot

  6. Once the snapshot creation is completed, select the snapshot and then click on the “Restore Snapshot” button.  Note: The duration of the snapshot creation will have a dependency on the size of the databases in the instance.
  7. Restoring from a Snapshot

  8. On the “Restore DB Instance” page, make sure you name the Test SQL Server RDS instance accordingly by keying in the “DB Instance Identifier” in addition to make sure all the “Network & Security” settings are correct as per requirement. Click on the “Launch DB Instance” to create the Test SQL Server RDS instance by cloning the Development SQL Server RDS instance. Yes. Everything in the Development SQL Server RDS instance will be copied over to the Test SQL Server RDS instance.
  9. Name the DB Instance Identifier

  10. Once the Test SQL Server instance is provisioned, on the RDS Dashboard click on “Instances” and then copy the endpoint.
  11. Locating the Endpoint

  12. Open SQL Server Management Studio and paste the endpoint in the “Server name:” textbox and make sure to replace the “:” with “,” in front of the port number. Key in the master user and password you had created for the Development SQL Server instance.
  13. Connecting to the RDS instance

  14. Once connected, browse to the Security node, select the Master User and rename it accordingly as per your internal naming standards for the Test environment and change the password. Warning: Even after you had renamed the master user in the SQL Server RDS instance, if you review it on the RDS Dashboard  it will remain and show the master user the same prior being renamed in the instance level.  You will be logging on using the master user name you had renamed rather than the one showing on the RDS Dashboard for the Test SQL Server RDS instance.

SQL Server RDS Instance Data Migration Between Separate AWS Accounts

Pop Quiz Again Hot Shot! You work for or consult for a company that has multiple development teams. Each team reports to their corresponding department heads. The organizational hierarchy is almost as complicated and just as political as the US government. Multiple AWS accounts have been opened and being used by multiple teams. You have been assigned with the critical tasks to migrate data from one SQL Server RDS instance in the AWS account belonging to Billy’s team to another SQL Server RDS instance in the AWS account belonging to Bob’s team. You need to get the migration completed fast before someone sets up a political storm and send it your way!

Warning: Don’t seek advice from the self-proclaimed AWS or “cloud” experts! They may point you to the misconception that you can take snapshot from on AWS account and restore it to another. AWS is still a relatively new technology. Similar many others whom are exploring AWS, I belonging to the novice group when it comes to AWS or “cloud.” But, I can share with you that as of today (August 23, 2014) and as per AWS support, you can’t take a snapshot from one AWS account and restore it to another.  Again, without sounding like a broken record, this is time sensitive information – this is a fact as of August 23, 2014. Please share with me once you figured out an alternative or once the feature for snapshot migration between different AWS accounts are available.

Assumptions:

  1. The SQL Server RDS instance belonging to Billy’s team (source) and the SQL Server RDS instance belonging to Bob’s team (destination) are located in separate AWS accounts.
  2. You are using BCP for the data transfer as you do not have the luxury of using a third party tool for the data migration.
  3. In the following example steps, for simplicity’s sake, we will only explore how to migrate the logins, database, and data.

Steps to BCP data out of the SQL Server RDS Instance (source) belonging to Billy’s team:

  1. Create a dedicated folder for the migration purpose. Eg.: RDSMigration.
  2. Create the corresponding folders in the dedicated folder for migration, RDSMigration, for each of the databases to be migrated over to the destination instance.  They will store the scripts and files for migration for each of the databases.
  3. Logon to source SQL Server RDS instance.
  4. Script out the logins. Save it to the RDSMigration folder.  Name the script accordingly. Eg.: SourceLogins.sql.
  5. For each of the databases to be migrated over to the Production RDS instance, generate the scripts for database objects and save it to the corresponding database name folder you had created in step 2.
  6. Connect to the source SQL Server RDS instance, right-click the database > Tasks > Generate Scripts…:
    Generate Scripts

    The “Introduction” screen for “Generate and Publish Scripts” will greet you. Click the “Next” button:
    Generate scripts for database objects

    On the “Choose Objects” page, make sure the default option “Script entire database and all database objects” is selected and then click on the “Next” button to continue:
    Script entire database and all database objects

    On the “Set Scripting Options” page, specify the location to save the script:
    Specify the folder to save the script

    On the “Save or Publish Scripts” screen, make sure you see all green marks. Otherwise, you will need to review and resolve the errors and retry. Click on the “Finish” button to wrap things up as we are seeing all green here.
    Generate and Publish Scripts - Confirmation Dialog Box

  7. To BCP each of the user tables data out of the source SQL Server RDS instance database to be migrated over to the destination SQL Server RDS instance, run the following TSQL on the database to generate the BCP command to be executed on the command prompt:
  8. select 'bcp ' + db_name() +'.'+ SCHEMA_NAME(schema_id) +'.[' + name + '] out "C:\RDSMigration\DatabaseName\' + name + '.dat" -E -n -S Billy.c2q1zlj4vmlz.us-east-1.rds.amazonaws.com,1433 -Umasteruser -PPassword -b 10000'
    from sys.tables where type = 'U'
    order by name

    Replace the output folder name, RDS instance name, master user, and password with the correct values.

  9. Copy the output generated on step 6 and paste into a text file. Make sure the SELECT clauses with all the required columns are in one line without wrapping to the next line. Else, error will be thrown when executed in the command prompt.
  10. Open the command prompt and execute the BCP commands from step 7. You will need to press enter for the last BCP command to execute it.
  11. Check the output folder to make sure the corresponding .dat files for each of the user tables are generated.

Steps to BCP data into Bob’s SQL Server RDS Instance database (destination):
1. Logon to the destination SQL Server RDS instance.

2. Run the login script, SourceLogins.sql, you had generated to create the logins on the destination SQL Server RDS instance.

3. Run the database and objects script you had generated from the Source SQL Server RDS instance to create the database and objects.

4. In SSMS, expand the database node, expand the database you had just created, expand the Tables nodes and make sure all the user tables exist as in the database in the source SQL Server RDS instance.

5. To BCP each of the user tables into the designation database, run the following TSQL on the database to generate the BCP command to be executed on the command prompt:

select 'bcp ' + db_name() +'.'+ SCHEMA_NAME(schema_id) +'.' +name + ' in "C:\RDSMigration\DatabaseName\' + name + '.dat" -E -n -S ProdInstance.c2q1zlj4vmlz.us-east-1.rds.amazonaws.com,1433 -Umasteruser -PPassword -b 10000' 
from sys.tables where type = 'U'
order by name

Replace the input folder name, RDS instance name, master user, and password with the correct values.

6. Copy the output generated on step 5 and paste into a text file. Make sure the SELECT clauses with all the required columns are in one line without wrapping to the next line. Else, error will be thrown when executed in the command prompt.

7. Open the command prompt and execute the BCP commands from step 6. You will need to press enter for the last BCP command to execute it.

8. Query the user tables to make sure the same data exist as in the source database. You can also write a script to do the row count and compare between them.

How to Create a New SQL Server RDS Instance?

I will take a stand here. Due to its limitations, as documented in here by AWS, SQL Server RDS is not my favorite. I have always advised whomever willing to listen to use AWS EC2 instances instead. What if there are good reasons due to budget or if the decision to use SQL Server RDS has been made for you by whomever cutting the checks? What do you do? Don’t sweat it. It’s not Rocket Science.

Pre-requisites and some caveats:

  1. Determine the edition that’s required. This is an easy one as opposed to the menus of the fancy restaurants. There are only two on the menu: SQL Server Standard Edition or SQL Server Enterprise Edition.   You will need to “bring-your-own-license”.
  2. Make a decision if the new SQL Server RDS instance will be in Production or Non-Production.
  3. Know the requirements of the new SQL Server RDS instance: CPU, RAM, and Storage Space.
  4. Determine the database instance identifier, master user, and the master user password. You can use the password generator to create a random password if you don’t feel like being creative at the moment: https://identitysafe.norton.com/password-generator.
  5. Know the inbound rules. You will need to configure it after the RDS instance is created. You will need to check with the application support and the network or system admin for the configuration of the inbound rules.
  6. Here’s a critical “gotcha”: If you are working on a migration of the development, test, staging, and production environments – don’t over prepare by creating all these instances ahead of time. The recommended approach is to create the development environment, migrate the data and all the associated objects over to the new RDS instance, once the development environment is validated and verified and in addition to all the stakeholders signed off, then take a snapshot of the development environment and then restore the snapshot to create a new RDS instance. Use this approach for all the environments after development with the development environment being the initial RDS instance.  You must remember and be aware that you cannot restore from a DB snapshot to an existing DB instance! A new DB instance is created when you restore from a snapshot.
  7. Here’s another critical “gotcha”: As per AWS support, you can’t copy a snapshot from one AWS account to another. This means that if you have an account for Production and another account for Development and you wish to create the Production RDS instance by taking a snapshot of the Development RDS instance, this can’t be done as of today (August 21, 2014). You must use BCP to BCP the data out of the Development instance and then BCP the data into the Production instance. If you have a third party tool on for the RDS instances data migration, use it instead of BCP. I will post a blog on how to use BCP in a later time.

Let’s get to it. Here’s how to create a new SQL Server RDS instance step by step:

  1. Logon to your AWS console, under the “Database” section, click on the “RDS” link:
  2. RDS

  3. You will be redirected to the “RDS Dashboard.” Click on the “Launch a DB Instance” button:
  4. Launch a DB Instance

  5. You will then be presented with the option to select the database engine. In this case, we are selecting “sqlserver-ee”:
  6. Selecting DB Engine

  7. On the next screen, you will need to make the decision on the purpose of the instance: Production or Non-Production? In this case, we are choosing the first option and then click on the “Next” button:
  8. Specifying DB Environment

  9. On the next step, you will need to specify the SQL Server RDS instance specifications and settings. You will need to select the License Model, DB Engine Version, DB Instance Class, Mutli-AZ Deployment, Storage, and if Provisioned IOPS is required. In addition, you will need to specify the DB Instance Identifier, Master Username, Master Password. Then, click the “Next” button to continue:
  10. Specifying DB Details

  11. On the next screen, you will see the instance is being created:
  12. Status of Creating

  13. After the instance is created. You will need to change the inbound rules. Click on the drop-down button next to the instance name and then select the “Modify” option. Click the “Security Group Name”:
  14. Click on the Security Groups to Modify the Inbound Rules

  15. On the “Security Group” section, click the “Inbound” tab and then click on the “Edit” button to add the required inbound rules. Note: you will need to check with your network admin on the required inbound rules.
  16. Configuring Inbound Rules

  17. Browse to the instance then copy the endpoint.
  18. Locating the Endpoint

  19. Test and verify your RDS instance by logging in. Open SQL Server Management Studio (SSMS), paste the endpoint and be sure to replace the “:” with a comma “,” before the port number. For authentication, select “SQL Server Authentication” and then key in the login name and password. Click the “Connect” button:
  20. Connecting to the RDS instance

  21. If you are able to see the RDS instance on “Object Explorer,” you are connected and you have successfully created your RDS instance:
  22. Connected