Getting started with Microsoft SQL ServerOVER ClausePIVOT / UNPIVOTDatabase SnapshotsRetrieve information about the databaseThe STUFF FunctionFOR XML PATHCursorsJoinCommon Table ExpressionsMove and copy data around tablesDatesLimit Result SetRetrieve Information about your InstanceWith Ties Option VariablesJSON in Sql ServerWindow functionsPartitioningStored ProceduresGROUP BYGenerating a range of datesCOALESCESplit String function in Sql ServerINSERT INTOCREATE VIEWString FunctionsResource GovernorORDER BYWHILE loopSystem database - TempDbMigrationPrimary KeysMERGEFull-Text IndexingFOR JSONSELECT statementDBMAILIndexQueries with JSON dataStoring JSON in SQL tablesOPENJSONRanking FunctionsTriggerConverting data typesNULLsTransaction isolation levelsAdvanced optionsIF...ELSETRY/CATCHData TypesUser Defined Table TypesTable Valued ParametersIn-Memory OLTP (Hekaton)Temporal TablesInsertSequencesSCOPE_IDENTITY()ViewsUse of TEMP TableScheduled Task or JobIsolation levels and lockingSorting/ordering rowsPrivileges or PermissionsForeign KeysSQLCMDFile Groupcross applyBasic DDL Operations in MS SQL ServerComputed ColumnsUNIONSubqueriesLast Inserted IdentityCLUSTERED COLUMNSTOREParsenameInstalling SQL Server on WindowsAggregate FunctionsQuerying results by pageSchemasBackup and Restore DatabaseTransaction handlingNatively compiled modules (Hekaton)Database permissionsSpatial DataDynamic SQLPaginationQuery HintsModify JSON textRow-level securityDynamic data maskingExport data in txt file by using SQLCMDEncryptionManaging Azure SQL DatabaseCommon Language Runtime IntegrationDelimiting special characters and reserved wordsCASE StatementDBCCBULK ImportQuery StoreService brokerAnalyzing a QueryMicrosoft SQL Server Management Studio Shortcut KeysPermissions and SecurityPHANTOM readFilestreamDrop KeywordString Aggregate functions in SQL ServerSQL Server Evolution through different versions (2000 - 2016)SQL Server Management Studio (SSMS)Logical FunctionsDynamic SQL PivotAlias Names in Sql Serverbcp (bulk copy program) Utility

Managing Azure SQL Database

Other topics

Find service tier information for Azure SQL Database

Azure SQL Database has different editions and performance tiers.

You can find version, edition (basic, standard, or premium), and service objective (S0,S1,P4,P11, etc.) of SQL Database that is running as a service in Azure using the following statements:

select @@version
SELECT DATABASEPROPERTYEX('Wwi', 'EDITION')
SELECT DATABASEPROPERTYEX('Wwi', 'ServiceObjective')

Change service tier of Azure SQL Database

You can scale-up or scale-down Azure SQL database using ALTER DATABASE statement:

ALTER DATABASE WWI
MODIFY (SERVICE_OBJECTIVE = 'P6')
-- or
ALTER DATABASE CURRENT
MODIFY (SERVICE_OBJECTIVE = 'P2')

If you try to change service level while changing service level of the current database is still in progress you wil get the following error:

Msg 40802, Level 16, State 1, Line 1 A service objective assignment on server '......' and database '.......' is already in progress. Please wait until the service objective assignment state for the database is marked as 'Completed'.

Re-run your ALTER DATABASE statement when transition period finishes.

Replication of Azure SQL Database

You can create a secondary replica of database with the same name on another Azure SQL Server, making the local database primary, and begins asynchronously replicating data from the primary to the new secondary.

ALTER DATABASE <<mydb>>
ADD SECONDARY ON SERVER <<secondaryserver>>
WITH ( ALLOW_CONNECTIONS = ALL ) 

Target server may be in another data center (usable for geo-replication). If a database with the same name already exists on the target server, the command will fail. The command is executed on the master database on the server hosting the local database that will become the primary. When ALLOW_CONNECTIONS is set to ALL (it is set to NO by default), secondary replica will be a read-only database that will allow all logins with the appropriate permissions to connect.

Secondary database replica might be promoted to primary using the following command:

ALTER DATABASE mydb FAILOVER 

You can remove the secondary database on secondary server:

ALTER DATABASE <<mydb>>
REMOVE SECONDARY ON SERVER <<testsecondaryserver>>

Create Azure SQL Database in Elastic pool

You can put your azure SQL Database in SQL elastic pool:

CREATE DATABASE wwi
( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = mypool1 ) ) 

You can create copy of an existing database and place it in some elastic pool:

CREATE DATABASE wwi
AS COPY OF myserver.WideWorldImporters  
( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = mypool1 ) ) 

Contributors

Topic Id: 7113

Example Ids: 23862,23863,23864,23865

This site is not affiliated with any of the contributors.