Monday, 17 February 2020

AWS RDS SQL Server Announcements - 2020


AWS RDS SQL Server Announcements - 2020


1.   Amazon RDS for SQL Server Now Supports Joining a Domain Across AWS Accounts and VPCs using Managed Microsoft AD: https://aws.amazon.com/about-aws/whats-new/2020/02/amazon-rds-for-sql-server-now-supports-joining-domain-across-aws-accounts-and-vpcs-using-microsoft-managed-ad/
7.  Amazon RDS for SQL Server now supports SQL Server Analysis Services (SSAS): https://aws.amazon.com/about-aws/whats-new/2020/04/amazon-rds-sql-server-now-supports-sql-server-analysis-service/
9.   Amazon RDS for SQL Server supports Bulk Insert on highly available DB Instances using Amazon S3 Integration: https://aws.amazon.com/about-aws/whats-new/2020/05/amazon-rds-for-sql-server-now-supports-sql-server-bulk-insert/
10. Amazon RDS for SQL Server now supports SQL Server Reporting Services (SSRS): https://aws.amazon.com/about-aws/whats-new/2020/05/amazon-rds-for-sql-server-now-supports-sql-server-reporting-services/
11. Amazon RDS for SQL Server now supports SQL Server Integration Services (SSIS): https://aws.amazon.com/about-aws/whats-new/2020/05/amazon-rds-for-sql-server-now-supports-sql-server-integration-services/
12. Amazon RDS for SQL Server now supports Windows authentication in the AWS GovCloud (US) Regions: https://aws.amazon.com/about-aws/whats-new/2020/07/amazon-rds-for-sql-server-now-supports-windows-authentication-in-the-aws-govcloud-regions/
14. Amazon RDS for SQL Server now supports Service Master Key Retention https://aws.amazon.com/about-aws/whats-new/2020/07/amazon-rds-for-sql-server-now-supports-service-master-key-retention/










Wednesday, 29 January 2020

AWS RDS SQL Server Announcements - 2019

AWS RDS SQL Server Announcements - 2019


3.    Amazon RDS SQL Server now supports high availability in more regions (BJS, ARN, HKG, and BAH): https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-rds-for-sql-server-adds-high-availability-support-in-additional-aws-regions/ 
4.    Amazon RDS SQL Server S3 file transfers allowing for Bulk insert to work on Single AZ instances (multi-az support in Q1 020): https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/User.SQLServer.Options.S3-integration.html 
5.    Amazon RDS SQL Server supports outbound network access (cross vpc linked servers now supported):https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-rds-sql-server-supports-outbound-network-access/ 
8.    Amazon RDS SQL Server now supports 8xl and 16xl for nitro instances: https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-rds-for-sql-server-now-supports-additional-instance-sizes/
9.     Amazon RDS now supports Storage Auto Scaling: https://aws.amazon.com/about-aws/whats-new/2019/06/rds-storage-auto-scaling/
10.  Amazon RDS for SQL Server now supports additional instance sizes: https://aws.amazon.com/about-aws/whats-new/2019/06/rds-storage-auto-scaling/
11.  Amazon RDS Performance Insights Supports Counter Metrics on Amazon RDS for SQL Server: https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-rds-performance-insights-supports-counter-metrics-on-amazon-rds-for-sql-server/
12.  Amazon RDS SQL Server now supports changing the server-level collation: https://aws.amazon.com/about-aws/whats-new/2019/08/amazon-rds-sql-server-supports-changing-server-level-collation/
15.  Amazon RDS for SQL Server Now Supports Always On Availability Groups for SQL Server 2017: https://aws.amazon.com/about-aws/whats-new/2019/05/amazon-rds-for-sql-server-now-supports-always-on-availability-groups-for-sql-server-2017/
16.  Amazon RDS for SQL Server Increases the Database Limit Per Database Instance up to 100: https://aws.amazon.com/about-aws/whats-new/2019/05/amazon_rds_for_sql_server_increases/
17.  Performance Insights is Generally Available on Amazon RDS for SQL Server: https://aws.amazon.com/about-aws/whats-new/2019/03/performance-insights-is-generally-available-for-sql-server/

AWS RDS SQL Server Version List

AWS RDS SQL Server 2017 Builds



RDS SQL Server version SQL Server Build KB / Description
SQL Server 2017 14.00.3223.3.v1 14.0.3223.3 4508218 Cumulative update 16 (CU16) for SQL Server 2017
SQL Server 2017 14.00.3192.v1 14.0.3192.2 4505225 Security update for SQL Server 2017 CU15 GDR: July 9, 2019 
SQL Server 2017 14.00.3049.1.v1 14.0.3049.1 4483666 On-demand hotfix update package for SQL Server 2017 Cumulative update 13 (CU13)
SQL Server 2017 14.00.3035.2.v1 14.0.3035.2 4293805 Security update for the Remote Code Execution vulnerability in SQL Server 2017 CU: August 14, 2018 
SQL Server 2017 14.00.3015.40.v1 14.0.3015.40 4052987 Cumulative update 3 (CU3) for SQL Server 2017 – Security Advisory ADV180002 
SQL Server 2017 14.00.1000.169.v1 14.0.1000.169 Microsoft SQL Server 2017 RTM 



AWS RDS SQL Server 2016 Builds



RDS SQL Server version SQL Server Build KB / Description
SQL Server 2016 13.00.5426.0.v1 13.0.5426.0 4505830 Cumulative update 8 (CU8) for SQL Server 2016 Service Pack 2
SQL Server 2016 13.00.5366.0.v1 13.0.5366.0 4505222 Security update for SQL Server 2016 SP2 CU7 GDR: July 9, 2019
SQL Server 2016 13.00.5292.0.v1 13.0.5292.0 4488536 Cumulative update 6 (CU6) for SQL Server 2016 Service Pack 2
SQL Server 2016 13.00.5216.0.v1 13.0.5216.0 4458871 Cumulative update 3 (CU3) for SQL Server 2016 Service Pack 2
SQL Server 2016 13.00.4522.0.v1 13.0.4522.0 4293808 Security update for the Remote Code Execution vulnerability in SQL Server 2016 SP1 CU: August 14, 2018 
SQL Server 2016 13.00.4466.4.v1 13.0.4466.4 4057119 Cumulative update 7 (CU7) for SQL Server 2016 Service Pack 1 – Security Advisory ADV180002 
SQL Server 2016 13.00.4451.0.v1 13.0.4451.0 4040714 Cumulative update 5 (CU5) for SQL Server 2016 Service Pack 1
SQL Server 2016 13.00.4422.0.v1 13.0.4422.0 4013106 Cumulative update 2 (CU2) for SQL Server 2016 Service Pack 1
SQL Server 2016 13.00.2164.0.v1 13.0.2164.0 3182270 Cumulative update 2 (CU2) for SQL Server 2016



AWS RDS SQL Server 2014 Builds



RDS SQL Server version SQL Server Build KB / Description
SQL Server 2014 12.00.6293.0.v1 12.0.6293.0 4505422 Security update for SQL Server 2014 SP3 CU3 GDR: July 9, 2019 
SQL Server 2014 12.00.5571.0.v1 12.0.5571.0 4052725 Cumulative update package 10 (CU10) for SQL Server 2014 Service Pack 2 – Security Advisory ADV180002 
SQL Server 2014 12.00.5546.0.v1 12.0.5546.0 4013098 Cumulative update package 5 (CU5) for SQL Server 2014 Service Pack 2
SQL Server 2014 12.00.5000.0.v1 12.0.5000.0 SQL Server 2014 Service Pack 2 (SP2)



AWS RDS SQL Server 2012 Builds



RDS SQL Server version SQL Server Build KB / Description
SQL Server 2012 11.00.7462.6.v1 11.0.7462.6 4057116 Description of the security update for SQL Server 2012 SP4 GDR: January 12, 2018 – Security Advisory ADV180002 
SQL Server 2012 11.00.6594.0.v1 11.0.6594.0 4013104 Cumulative update package 8 (CU8) for SQL Server 2012 Service Pack 3
SQL Server 2012 11.00.6020.0.v1 11.0.6020.0 SQL Server 2012 Service Pack 3 (SP3)
SQL Server 2012 11.00.5058.0.v1 except US East (Ohio), Canada (Central), and Europe (London) 11.0.5058.0 SQL Server 2012 Service Pack 2 (SP2)



Date Information
July 12, 2019 The Amazon RDS team deprecated support for Microsoft SQL Server 2008 R2 in June 2019. 


April 25, 2019 Before the end of April 2019, you will no longer be able to create new Amazon RDS for SQL Server database instances using Microsoft SQL Server 2008R2.

Monday, 29 April 2019

SQL Server Management Studio 18.0 is available now.


SSMS 18.0 is available for download now.

Microsoft has released the new version of SSMS, the free client tool from Microsoft to manage to the SQL Server infrastructure from SQL Server to Azure SQL Database.

SSMS 18.0 Supported Operating Systems :

Windows 10 (64-bit) *
Windows Server 2016 *
Windows Server 2012 R2 (64-bit)
Windows Server 2012 (64-bit)
Windows Server 2008 R2 (64-bit)

Can be downloaded from the link below:

Download Here

Thursday, 13 December 2018

Difference Between Restore with Recovery & Restore with Norecovery

Every tech-conscious person is familiar with restoration process in the world of computing. This process is usually followed by the process of backup. In case of SQL Server, restoration procedure means copying the backup and move it to the recovery point. SQL Database recovery involves rolling forward uncommitted transactions and make the database online. However, SQL database restoration process can be done in two ways, “with recovery” and “with norecovery”. In this post, we will learn about the difference between restore with recovery and restore with norecovery. We also know the full procedure of these two approaches.

Difference between With Recovery and With No Recovery in SQL Server

“With recovery” and “NoRecovery” are actually the part of command that we apply during database restoration.

  • Restore With Recovery in SQL Server

This recovery process by default involves both undo and redo parts while no excess backup restoration is not allowed. If redo/ roll forward is consistent with the database, smooth data recovery is possible by making the SQL database online. Undo part will not appear if the redo has not been made enough to be consistent with database. This may even stop the process of recovery.

  • Restore With Norecovery in SQL Server

When restore with norecovery is used, the uncommitted transactions are kept by removing the undo part. Thus, it permits rolling forward to continue with next sequential statement and sequence restoration can let other backup restorations to take place to further roll forward the database. Restore with norecovery can sometimes roll forward data to where the database consistency is maintained.

SQL Server Restore With Recovery vs NoRecovery

Now that we know the difference between these two processes, Let’s go through the detail of those.

  • SQL Server Restore With Recovery

It is one of the many available options for database restoration. This makes the restored database accessible to the users. Restore with recovery is the default option if you are restoring a full backup and not multiple backups.

Use T-SQL Statements to Perform the Restoration

These T-SQL scripts can be used for applicable situations:

For Full Backup Restoration

Though this will be the automatic option for restoring a full backup, specify it with this command:

RESTORE DATABASE MotionWorks FROM DISK = 'C:\MotionWorks.BAK'
WITH RECOVERY
GO

Recovering a ‘Restoring’ State Database

The following script is used when we have to recover a database that is still in the restoring state If this script is used, every user including the end users can access the database.

RESTORE DATABASE MotionWorks WITH RECOVERY
GO

Use “With Recovery” in Case of Last Backup while Restoring Multiple Backups

The last backup restoration in the case of multiple backups will require with recovery command. Use this following T-SQL Script to do that:

RESTORE DATABASE MotionWorks FROM DISK = 'C:\MotionWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG MotionWorks FROM DISK = 'C:\MotionWorks.TRN'
WITH RECOVERY
GO
  • SQL Server Restore With NoRecovery

We see the use of No Recovery command when there is the requirement of multiple backup restoration. In that case, With Norecovery option is used in all instances except the last one. Since With Norecovery command enables ‘restoring state’ in a database, additional backups can be performed. In the meantime, users are prohibited from accessing that database.

Restore database with norecovery in sql server

Here are some examples of commands that need to be used

Restore Full Backup With Norecovery and One Transaction Log Backup

Here, Norecovery command has been used to perform additional backups later. This command will restore the database but that database will be in the restoring state. The second command is used for transaction log backup restoration using With Recovery and making the database online for end users.

Restore Full Backup With Norecovery and Two Transaction Log Backups

The first two commands will restore the database and log where we have used With NORECOVERY option. The last command “With Recovery” will restore the transaction log.

Using SQL Server Management Studio

With the help of SQL Server Management Studio, Restore With Norecovery command can be used. Click on Options-->Choose Restore With Norecovery and click OK.

Final Words

In this post, we have discussed the difference between restore with recovery and restore with norecovery. We have also shared the various commands in detail related to these two types of database restoration processes. Users should get a clear idea about these two concepts and be able to restore SQL database using both the approaches.