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!

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s