Featured Database Articles
Posted December 24, 2015
Getting Started with Amplitude Database Functionality in SQL Server 2016 – Part 2
By Arshad Ali
SQL Server is one of the best broadly acclimated RDBMS about the world, and one of the facts of this advanced acceptance comes from Microsoft’s charge to add new capabilities to baby to the charge of the alteration mural year over year. Along the aforementioned line, SQL Server 2016 introduces several new appearance and one of them is Amplitude Database, which lets you move or annal your algid abstracts from a bounded SQL Server database to the Azure SQL Database clearly (without autograph cipher for abstracts movement) so that your bounded SQL Server database has beneath aerial in advancement them (and appropriately all-embracing accretion the performance) and the aforementioned time, continued abstracts is online acceptance applications to admission it like any added table.
In the aftermost commodity of this series, we abstruse how to get started with this new affection either by application SSMS or application T-SQL scripting. In this commodity I am activity to explain and authenticate the appulse of enabling the StretchDB affection on advancement and restore, and means to pause, resume and attenuate this affection altogether.
Pausing and Resuming Alien Abstracts Archival for a Table
If necessary, you can abeyance the abstracts clearing to troubleshoot any issues (or to abbreviate the amount on arrangement bandwidth) and resume it already you are done. To abeyance or resume abstracts clearing you can either use the astrologer in SSMS (to abeyance it, right-click on the table in Object Explorer of SSMS and baddest Tasks, and again baddest Stretch, and again baddest Pause; likewise, to resume it, right-click on the table in Object Explorer of SSMS and baddest Tasks, and again baddest Stretch, and again baddest Resume.) or use the T-SQL command as accustomed below:
–Pause alien abstracts archivalALTER TABLE [dbo].[SalesOrderDetailHistory] SET(REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OFF))GO–Resume alien abstracts archivalALTER TABLE [dbo].[SalesOrderDetailHistory] SET(REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = ON))GO
SQL Server 2016 CTP 2 does not acquiesce afterlight and deleting rows from a table enabled for alien abstracts archival although you can admit data. This additionally applies aback you accept paused alien abstracts archival. If you try accomplishing DELETE or UPDATE you will get an absurdity agnate to that apparent below:
delete from SalesOrderDetailHistory area SalesOrderID = 68536Msg 14826, Level 16, Accompaniment 1, Band 80Update and annul of rows acceptable for clearing in table ‘SalesOrderDetailHistory’ is not accustomed because of the use of REMOTE_DATA_ARCHIVE.Disable StretchDB Affection for a DatabaseRelated ArticlesGetting Started with Amplitude Database Functionality in SQL Server 2016 â Part 1Getting Started with Amplitude Database Functionality in SQL Server 2016 – Part 2Row Level Security with SQL Server 2016: Part 1 â Acquiesce Admission to Alone a Subset of Rows Application Row Level SecurityRow Level Security with SQL Server 2016: Part 2 – Blocking Updates at the Row LevelAltering an Existing Table to Abutment Temporal DataGetting Started with JSON Abutment in SQL Server 2016 â Part 2Getting Started with Temporal Table in SQL Server 2016 â Part 2Getting Started with Temporal Table in SQL Server 2016 â Part 1
If you appetite to briefly stop abstracts clearing and re-start later, you can chase the admission as discussed for pausing and resuming but if you appetite to attenuate this affection absolutely again you charge to accomplish these two activities:
First, you charge to abeyance abstracts clearing for all the tables that accept been enabled for alien abstracts archival (referred as stretch-enabled table) in the database. Next, for anniversary of these tables you charge to actualize a bounded table with the aforementioned action as the stretch-enabled table, again archetype the abstracts from the stretch-enabled table to the anew created bounded table (you can use the INSERT INTO … SELECT FROM command). Finally bead the stretch-enabled table and rename the anew created table with the name of the stretch-enabled table you dropped.Second, now you can attenuate the StretchDB affection for the database aback it no best contains any stretch-enabled table with the command accustomed below.
As you apprehension with the beneath script, aback you try disabling the StretchDB affection on a database that contains alike a distinct table with alien abstracts archival enabled, it fails and appropriately you charge to aboriginal about-face them off afore disabling the StretchDB affection on the database.
–Disable StretchDB affection for a databaseALTER DATABASE [StretchDBDemo] SET REMOTE_DATA_ARCHIVE = OFF;Msg 14802, Level 16, Accompaniment 1, Band 70Cannot attenuate REMOTE_DATA_ARCHIVE because the database contains at atomic one table accepting REMOTE_DATA_ARCHIVE enabled.Msg 5069, Level 16, Accompaniment 1, Band 70ALTER DATABASE account failed.Backup and Restore for Stretch-enabled Database
Backup and restore for a database, enabled for the StretchDB feature, works hardly differently. Aback you booty a advancement of a amplitude enabled database, it includes alone bounded abstracts and acceptable abstracts (data not yet migrated and still accessible in the bounded stretch-enabled table) including advice about the alien endpoint area the database’s alien abstracts resides, at the point in time aback you run your advancement command. The advancement archetype of stretch-enabled database is alleged “shallow backup”. Currently, SQL Server 2016 Community Technology Preview 2 (CTP2) does not abutment demography a “deep backup” that contains all abstracts in the database, from both bounded as able-bodied as from remote.
Though the action to booty a advancement of a database charcoal the same, abating it aback is hardly altered for a stretch-enabled database. Afterwards the database is restored, your database contains alone bounded abstracts and acceptable abstracts (data not yet migrated and still accessible in the bounded amplitude enabled table) from the advancement but clearing will not alpha automatically as it ability not accept the appropriate accreditation to affix to the alien endpoint. Appropriately you charge to aboriginal actualize accreditation on your adequate database (script provided beneath to actualize credential if it does not exists) and re-establish the affiliation amid the bounded database and its alien endpoint by calling sys.sp_reauthorize_remote_data_archive as approved in the script.
—-Create adept to encrypt and defended credential and affiliation information–CREATE MASTER KEY ENCRYPTION BY PASSWORD = ” —-Create credential to affix to alien Azure SQL Database–CREATE DATABASE SCOPED CREDENTIAL Media — IDENTITY = ”, — sysadmin user name for the SQL Database server– SECRET = ” — sysadmin countersign for the SQL Database server–GO–Switch to adequate databaseUSE –Restore the accurate affiliation amid a StretchDB enabled locally –restored database and Azure SQL databaseEXEC sp_reauthorize_remote_data_archive @azure_credentialname = Media alien database ‘RDAStretchDBDemoAD8E496D-8CFE-4A8F-A90C-9DA4C5682ADE’ to alien database ‘RDAStretchDBDemo156C5CB16-0488-4865-B840-CF3720E68950’.Waiting for alien database archetype to complete.Remote database ‘RDAStretchDBDemo156C5CB16-0488-4865-B840-CF3720E68950’ has completed copying, and is now online.
As you can see in the achievement bulletin for the aloft script, already the affiliation is re-established, stretch-enabled database creates a archetype of the alien database on the aforementioned server and links it with the bounded database. It again attempts to accommodate acceptable abstracts in the bounded database with alien abstracts afterwards any chiral intervention. Already afterwards the adaptation completes, your abstracts will be in a constant accompaniment and at that time you can cautiously annul the antecedent archetype of the alien database.
Note – The affection mentioned and approved in this commodity is based on SQL Server 2016 CTP 2.4 and ability change aback RTM is accessible or in approaching releases.
Stretch Database is a new affection in SQL Server 2016 that lets you move or annal your algid abstracts from a bounded SQL Server database to the Azure SQL Database clearly (without autograph cipher for abstracts movement) so that your bounded SQL Server database has beneath aerial in advancement them (and appropriately all-embracing increases the performance) and at the aforementioned time, continued abstracts is online acceptance applications to admission it like any added table.
In this commodity of this series, I explained and approved the appulse of enabling the StretchDB affection on advancement and restore, means to abeyance and resume, and attenuate this affection altogether.
Stretch Database Documentation
What’s New in SQL Server 2016
See all accessories by Arshad Ali
MS SQL Archives
0 Comments (click to add your comment)Comment and Contribute
LatestForum ThreadsMS SQLForumTopicByRepliesUpdatedSQL 2005: SSIS: Absurdity application SQL Server accreditation abjection 3 August 17th, 07:43 AM Charge advice alteration table capacity nkawtg 1 August 17th, 03:02 AM SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM