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.
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.
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
It might be obvious to some readers but it got me!