SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Azure SQL Elastic Pools – Moving Databases

If you read official Microsoft documentation about naming conventions for your SQL elastic pools, it is hard to find any guidelines on the subject. You are probably thinking it is not that big of a deal. I thought the same until I tried to execute some TSQL to move a database into an elastic pool.

2pools

As you can see from the above screen shot I have two elastic pools called SQL-EDP1 and SQLPOOL. Absolutely no issues with the naming convention when going through the creation wizard. So what do you think will happen if I decide to use TSQL commands to move a single Azure SQL Database into these elastic pools?

Let’s move a database into the SQLPOOL elastic pool.


ALTER DATABASE [TwitterDB]
MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = SQLPOOL ) ) ;  

SELECT * FROM sys.dm_operation_status
ORDER BY start_time DESC

No issues at all.
poolmove

So let’s move a different single Azure SQL database into the elastic pool called SQL-EDP1.

ALTER DATABASE  [MeeTwoDB]
MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = SQL-EDP1 ) ) ;

It won’t parse because of the naming of the pool with the dash in it. Msg 102, Level 15, State 1, Line Incorrect syntax near ‘-‘.

You will need surround the elastic pool name with square brackets.

 ALTER DATABASE  [MeeTwoDB]
MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = [SQL-EDP1] ) ) ;  

SELECT * FROM sys.dm_operation_status
ORDER BY start_time DESC

newpool

It might be obvious to some readers but it got me!

 

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...