CODEWITHSUNDEEP
azureazure-sql-database
King Luy
King Luy

Reputation: 141

Windows Azure SQL dynamic scale did not work

im trying to scale my Azure SQL DB with php. All the other sql statements works fine, but when im sending

ALTER DATABASE db1_abcd_efgh MODIFY (EDITION = 'Web', MAXSIZE=5GB);

i get an error like that

User must be in the master database.

My database URL is that 

xaz25jze9d.database.windows.net

and the database is named linke that

db1_abcd_efgh

function skale_a_m(){
$host = "tcp:xaz25jze9d.database.windows.net,1433\sqlexpress";
$user = "db_user";
$pwd = "xxxxx?!";
$db = "master";  //I have tried out db1_abcd_efgh at this point
try {
    $conn = new PDO("sqlsrv:Server= $host ; Database = $db ", $user, $pwd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {

}
$string = 'use master; ALTER DATABASE db1_a_m MODIFY (EDITION =\'Web\', MAXSIZE=5GB)';
$stmt = $conn->query($string);

}

Now i have modified my function linke this

function skale_a_m() {
$serverName = "tcp:yq6ipq11b4.database.windows.net,1433";
$userName = 'db_user@yq6ipq11b4';
$userPassword = 'xxxxx?!';

$connectionInfo = array("UID" => $userName, "PWD" => $userPassword, "MultipleActiveResultSets" => true);

$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn === false) {
    echo "Failed to connect...";
}

$string = "ALTER DATABASE master MODIFY (EDITION ='Web', MAXSIZE=5GB)";
$stmt = sqlsrv_query($conn, $string);

}

Now i get no errors but the Db did not scale?

Upvotes: 1

Views: 537

Answers (2)

Fernando Correia
Fernando Correia

Reputation: 22365

According to ALTER DATABASE (Windows Azure SQL Database), the ALTER DATABASE statement has to be issued when connected to the master database.

With PDO, this can be achieved by a connection string such as:

"sqlsrv:server=tcp:{$server}.database.windows.net,1433; Database=master"

Sample code:

<?php
function scale_database($server, $username, $password, $database, $maxsize) {
    try {
        $conn = new PDO ("sqlsrv:server=tcp:{$server}.database.windows.net,1433; Database=master", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->setAttribute(constant('PDO::SQLSRV_ATTR_DIRECT_QUERY'), true);
        $conn->exec("ALTER DATABASE {$database} MODIFY (MAXSIZE={$maxsize}GB)");
        $conn = null;
    }
    catch (Exception $e) {
        die(print_r($e));
    }
}

scale_database("yourserver", "youruser", "yourpassword", "yourdatabase", "5");
?>

Note: It's not necessary to set the edition; it will be set according to the max size.

To test the sample code, configure it with your details (server name, login, password and database to be scaled) and execute it with PHP configured with the Microsoft Drivers 3.0 for PHP for SQL Server.

After that, refresh (Ctrl+F5) the Windows Azure Management Portal and you should see the new max size reflected on the Scale tab of the database.

You can also verify that it worked by using a tool to connect to the scaled database (not to the master database) and issuing this command:

SELECT CONVERT(BIGINT,DATABASEPROPERTYEX ('yourdatabase', 'MAXSIZEINBYTES'))/1024/1024/1024 AS 'MAXSIZE IN GB'

Upvotes: 2

Eoin Campbell
Eoin Campbell

Reputation: 44268

$string = 'use master; ALTER DATABASE db1_a_m MODIFY (EDITION =\'Web\', MAXSIZE=5GB)'

I'm pretty sure SQL Azure does not support switching Databases using the USE command.

Try connect directly to the master db in your connection, and remove the USE Master statement from the start of your query.

$host = "tcp:xaz25jze9d.database.windows.net,1433\sqlexpress";

That also looks wrong to me. You shouldn't have a named instance called SQLExpress at the end of your server connection afaik.

Upvotes: 1

Related Questions