This Alter Database Statement Is Not Supported Correct the Syntax and Execute the Statement Again

I never wrote virtually data platform solutions on Azure so far. The fact is in Switzerland nosotros're definitely late about the Cloud adoption and Azure data platform solutions. At that place are dissimilar reasons that are more or less valid but I don't want to contribute to any argue here. In any case the terminal announcements in this field with Azure data centers in Switzerland could encourage customers to reconsider this topic in the most hereafter. Don't get me wrong here, information technology doesn't mean that customers in Switzerland must move all their database infrastructures in the Cloud but this is just an opportunity for them to consider rearchitecting some pieces of their information system including databases that may lead to hybrid scenarios. It will exist likely the starting time pace to the cloud adoption for data platforms solutions. At dbi services we didn't exclude Azure from our target but we just silently connected to proceed an eye on the information platform stack awaiting the correct opportunity to movement up a gear.

Why to begin with SQL managed instances (MI)? After all, this feature is even so on preview and it already exists a lot of Azure solutions equally Azure SQL Databases (singleton and with elastic pool as well) equally well as SQL Server on Azure VMs.

The bespeak is this new characteristic is interesting in many aspects. Firstly, it will address the gap that currently exists between IaaS infrastructures (SQL Server VMs) and fully-managed services (with Azure SQL DBs). The old still requires maintaining the operating organisation (and licenses) while the latter didn't betrayal all the characteristic surface needed by various application scenarios.

At the same time, Microsoft introduced another purchasing model that based on VCore. I remembered a word with i of my customers sometimes ago almost DTUs. He asked me what is exactly DTU and I pointed out the post-obit sentence from the Microsoft documentation.

The amount of resources is calculated as a number of Database Transaction Units or DTUs and is a bundled measure of compute, storage, and IO resource

That is definitely a good way to merely resource management considering information technology makes an abstraction of the physical resources but this is probably it weakness in some degrees. Indeed, how to interpret what DBAs and infrastructure squad unremarkably manage from on-premises to the deject? Plain, Microsoft provided a figurer to assist customers to accost their questions before moving to the deject but the fact is database administrators seem to not be comfy to deal with DTUs . Simply now permit'due south talk about flexibility: in many databases scenarios nosotros don't desire to increase / decrease resources in the aforementioned bundle but we want to get a amend control of the resource configuration past dissociating compute (CPU / RAM) from the storage. From my feel, I had never seen 1 customer to scale compute and storage in the same fashion regarding their workload. Indeed, some workloads require high-operation storage while others are more CPU-jump. This is where the new vCore-based model comes into play past and I believe it will become a amend adoption from customers to smoothly motion to the deject. That's at to the lowest degree my opinion!

So, allow's try to play with MI. As a reminder, currently it is in preview but that's plenty to get a picture of what y'all may expect in the hereafter. In my demo, I volition use intensively CLI tools with dedicated PowerShell cmdlets and mssql-cli as well. This is voluntary because the fact is more than and more assistants tasks are done in this way and Microsoft provided all the commands to achieve them.

[[electronic mail protected]:#]> Get-AzureRmResourceGroup -Name sql-mi-rg   ResourceGroupName : sql-mi-rg Location          : westeurope ProvisioningState : Succeeded Tags              : ResourceId        : /subscriptions/913528f5-f1f8-4d61-af86-30f2eb0839ba/resourceGroups/sql-mi-rg  [[email protected]:#]> Go-AzureRmResource -ResourceGroupName sql-mi-rg | ft Proper noun, ResourceType, Location -AutoSize  Proper noun                                                    ResourceType                             Location ----                                                    ------------                             -------- sql-mi-client_OsDisk_1_842d669310b04cbd8352962c4bda5889 Microsoft.Compute/disks                  westeurope sql-mi-client                                           Microsoft.Compute/virtualMachines        westeurope shutdown-computevm-sql-mi-client                        Microsoft.DevTestLab/schedules           westeurope sql-mi-client453                                        Microsoft.Network/networkInterfaces      westeurope sql-mi-client-nsg                                       Microsoft.Network/networkSecurityGroups  westeurope sqlmiclientnsg675                                       Microsoft.Network/networkSecurityGroups  westeurope sql-mi-client-ip                                        Microsoft.Network/publicIPAddresses      westeurope sqlmiclientip853                                        Microsoft.Network/publicIPAddresses      westeurope sql-mi-routetable                                       Microsoft.Network/routeTables            westeurope sql-mi-vnet                                             Microsoft.Network/virtualNetworks        westeurope sql-mi-dbi                                              Microsoft.Sql/managedInstances           westeurope sql-mi-dbi/ApplixEnterprise                             Microsoft.Sql/managedInstances/databases westeurope sql-mi-dbi/dbi_tools                                    Microsoft.Sql/managedInstances/databases westeurope VirtualClustersql-mi-subnet                             Microsoft.Sql/virtualClusters            westeurope sqlmirgdiag947                                          Microsoft.Storage/storageAccounts        westeurope

My MI is composed of difference resources including:

• VirtualClustersql-mi-subnet – a logical container of managed instances?
• sql-mi-dbi equally managed case
• sql-mi-dbi/ApplixEnterprise and sql-mi-dbi/dbi_tools as managed databases.
• Network components including sql-mi-vnet, sql-mi-routetable

Here some more than details of my MI:

[[email protected]:#]> Become-AzureRmSqlManagedInstance | ft ManagedInstanceName, Location, ResourceGroupName, LicenseType, VCores, StorageSizeInGB -AutoSize  ManagedInstanceName Location   ResourceGroupName LicenseType     VCores StorageSizeInGB ------------------- --------   ----------------- -----------     ------ --------------- sql-mi-dbi          westeurope sql-mi-rg         LicenseIncluded      viii              32

I picked up a GEN4 configuration based on General Purpose pricing that includes 8 VCores and 32GB of storage.

My managed databases are equally follows:

[[email protected]:#]> Become-AzureRmSqlManagedDatabase -ManagedInstanceName sql-mi-dbi -ResourceGroupName sql-mi-rg | ft Name, ManagedInstanceName, Location, DefaultSecondaryLoc ation, Condition, Collation  -AutoSize  Proper name             ManagedInstanceName Location   DefaultSecondaryLocation Status Collation ----             ------------------- --------   ------------------------ ------ --------- dbi_tools        sql-mi-dbi          westeurope northeurope              Online Latin1_General_CS_AS_KS ApplixEnterprise sql-mi-dbi          westeurope northeurope              Online SQL_Latin1_General_CP1_CI_AS

Other resource are related to my other virtual client machine to connect to my MI. Indeed, the latter is not exposed through a public endpoint and it is reachable only from an internal network. I didn't setup a site-to-site VPN to connect the MI from my remote laptop.

Another signal that drew my attending is the high availability characteristic which is based on remote storage and Azure Service Material.

Exercise yous recollect of the VirtualClustersql-mi-subnet described before? In fact, my MI is congenital upon a service fabric. Referring to the Microsoft documentation a Service Material enables y'all to build and manage scalable and reliable applications composed of microservices that run at high density on a shared pool of machines, which is referred to as a cluster .

We may go a picture of this underlying cluster from a set of defended sys.dm_hadr_fabric_* DMVs with a loftier-level view of the underlying cluster …

… and a more detailed view including my managed databases:

Now let'southward get basic information from my MI:

The MI version may be easily identified by the engine_sql number equal to 8.

As said previously I have ii user databases that exist and they were restored from backups hosted on my blob storage container.

Here an instance of commands I used to restore them. You probably recognize the native RESTORE FROM URL syntax. Annotation as well that you have different means to restore / migrate your databases from on-premises environment with BACPAC and Azure Database Migration Service too.

RESTORE FILELISTONLY  FROM URL = 'https://mikedavemstorage.hulk.cadre.windows.internet/fill-in/ApplixEnterprise2014.bak'  RESTORE DATABASE [ApplixEnterprise]  FROM URL = 'https://mikedavemstorage.blob.core.windows.net/backup/ApplixEnterprise2014.bak';

Here a list of my existing user databases:

Allow's go further with database files configuration:

SELECT  	DB_NAME(database_id) Equally [db_name], 	file_id, 	type_desc, 	name AS [logical_name], 	physical_name, 	state_desc Equally [land], 	size / 128 Every bit size_MB, 	max_size FROM 	sys.master_files; Become

Some interesting points here:

one – tempdb is pre-configured with 12 information files and 16MB each? Probably far from our usualk recommendation but anyway the preview allows to change it by using DBA usual scripts.

2- Nosotros may as well notice that the user databases are placed on a unlike storage types (premium disk co-ordinate to the Microsoft documentation). Organisation databases are hosted to a local path C:\WFRoot\DB 3\Fabric\work\data\ besides as well the tempdb database. I use a standard tier meaning that organization DBs are all on an attached SSD included in the VCore toll.

Just for fun, I tried to install our DMK maintenance tool which basically creates a dbi_tools database with maintenance objects (tables and stored procedures) and related SQL Server agent jobs. A databasemail configuration step is besides part of the DMK installation and the skillful news is the characteristic is bachelor with MIs. However, I rapidly ran into was about some Modify DATABASE commands we utilize at the beginning of the deployment script:

Msg 5008, Level xvi, Country 14, Line 72 This ALTER DATABASE argument is not supported. Correct the syntax and execute the statement again. Msg 5069, Level 16, Land 1, Line 72 Modify DATABASE statement failed. Msg 5008, Level 16, Country fourteen, Line 89 This Change DATABASE statement is non supported. Correct the syntax and execute the argument again. Msg 5069, Level 16, Country one, Line 89 ALTER DATABASE statement failed.

The set up was quite easy and I finally managed to deploy the tool as show below:

The next step consisted in testing our different maintenance tasks:

• Database integrity cheque chore
• Index maintenance task
• Update statistics maintenance task
• Backup chore

The commencement 3 tasks worked well without whatsoever modification. However, for backups, I needed to used URL-based backups considering it is the only method supported and then far. Unfortunately, the current version of our DMK maintenance tool doesn't not handled correctly information technology does shared admission signatures that come with Fill-in TO URL command since SQL Server 2016. The fix will be included soon to the next release for sure :). For the context of my test I modified a fiddling bit the argument generated by the maintenance objects and it worked perfectly:

-- Backup database dbi_tools Backup DATABASE  [dbi_tools]  TO URL = 'https://sqlmidbistorage.hulk.core.windows.net/sqlmidbicontainer/sql-mi-dbi.weu157eee5444ccf.database.windows.net_dbi_tools_1_20180611210123.BAK' WITH COPY_ONLY, CHECKSUM, INIT, FORMAT;  --Verification of the backup https://sqlmidbistorage.blob.core.windows.net/sqlmidbicontainer/sql-mi-dbi.weu157eee5444ccf.database.windows.net_dbi_tools_1_20180611210123.BAK RESTORE VERIFYONLY FROM URL = 'https://sqlmidbistorage.blob.core.windows.internet/sqlmidbicontainer/sql-mi-dbi.weu157eee5444ccf.database.windows.net_dbi_tools_1_20180611210123.BAK' WITH STATS = 100, CHECKSUM;

And to terminate this weblog mail as a good DBA, permit'south accept a await at the resources resource allotment management. First time I took a expect at the resources bachelor on the MI I was very surprised. To get an idea, let'south run some DMVs as sys.dm_os_schedulers, sys.dm_os_sys_memory or sys.dm_os_sys_info DMVs to get a real picture of these aforementioned resource:

Given the number of visible online schedulers only 8 may exist used by the MI. This is an expected outcome co-ordinate to my initial configuration. Apropos the retention configuration, the theorical amount of retention available I can get should be 8 x 7GB = 56GB co-ordinate the Microsoft documentation and the sys.dm_os_sys_memory DMV doesn't really signal such capping while the sys.dm_os_sys_info DMV does (at to the lowest degree closer to the reality)

Are the CPU and memory resources managed differently on MI? I constitute out the right answer in this article from the SQL Server customer advisor team. For MI, this mechanism that is responsible of resources management is called Job Objects. That's very interesting! Without going into details, this is exactly the aforementioned mechanism used by Docker on Windows and information technology is similar (at least in the concept) to existing CGroups on Linux.

Therefore, we may also benefit from another DMV to get details of resource direction:

Thank you to this SQL Server customer counselor team commodity, the situation becomes clearer with the following parameter values:

• cpu_rate 100% indicates my vCores are used at 100% of their capacity
• cpu_affinity_mask indicates nosotros are limited to viii OS level processors
• process_memory_limit_mb is self-explanatory and corresponds to my previous theorical assumptions 🙂
• non_sos_mem_gap_mb corresponds to a safe corporeality of bachelor memory for non-SQLOS activity

Conclusion

I call back Microsoft is doing a great strategic play past introducing this solution for customers. Indeed, change is always a challenge and moving towards something almost similar to what nosotros already know allows a polish transition and a better adoption from humans. We volition see what happens!

By David Barbarin

stephensonthation.blogspot.com

Source: https://blog.dbi-services.com/introducing-sql-managed-instances-on-azure/

Share this post