locked
Scaling DB - there must be a simpler way RRS feed

  • Question

  • Hi everybody,

    When we need to run queries against one of our databases, the process we have to follow is this one

    1. From the Azure Portal go to SQL Databases, then click on the tier, using slider slide to bigger value, then wait while scaling is in progress (also make sure to select correct DB - few times I accidentally used a similar named DB and had to cancel operation)

    2. Run queries

    3. Do the same process in the opposite direction

    If we asked to perform more queries against the DB we repeat the same process few times during the day.

    ----------------------------------

    This is quite inconvenient. I'm wondering if there is a simpler way to perform the steps 1 & 3 from T-SQL (SSMS or Azure Data Studio)?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, March 31, 2020 9:36 PM

Answers

All replies

  • I installed Azure Data Studio and I also installed PowerShell but it's not clear how should I use it. If I try to copy block of code to the terminal, it executes right away.

    Am I installed what I was supposed to install? How exactly should I work with this sample script?

    $SubscriptionId = "4cac86b0-1e56-bbbb-aaaa-000000000000"
    
    $ResourceGroupName = "resourceGroupName"
    $Location = "Japan West"
    
    $ServerName = "serverName"
    $DatabaseName = "databaseName"
    
    $NewEdition = "Standard"
    $NewPricingTier = "S2"
    
    Add-AzureRmAccount
    Select-AzureRmSubscription -SubscriptionId $SubscriptionId
    
    $ScaleRequest = Set-AzureRmSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroupName -Edition $NewEdition -RequestedServiceObjectiveName $NewPricingTier
    
    $ScaleRequest


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, March 31, 2020 10:00 PM
  • Hello Naomi, 

    You will need the Powershell Extension before you run the script.

    Here is a short 10 minute video that will get you up and running.

    Do let us know if you have further questions.

    -

     

    If this answers your query, do click “Mark as Answer” and Up-Vote for the same which might be beneficial to other community members reading this thread .

    And, if you have any further queries do let us know. 

    Wednesday, April 1, 2020 9:00 AM
  • You can also do this from powershell, I added Connect-AzureRmAccount to insure you get connected to the right tenant and subscription before you proceed with changing the server.

    $cred=Get-Credential
    $SubscriptionId = "4cac86b0-1e56-bbbb-aaaa-000000000000"
    
    Connect-AzureRmAccount -Credential $cred -SubscriptionId $SubscriptionId 
    
    $ResourceGroupName = "resourceGroupName"
    $Location = "Japan West"
    
    $ServerName = "serverName"
    $DatabaseName = "databaseName"
    
    $NewEdition = "Standard"
    $NewPricingTier = "S2"
    
    Add-AzureRmAccount
    Select-AzureRmSubscription -SubscriptionId $SubscriptionId
    
    $ScaleRequest = Set-AzureRmSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroupName -Edition $NewEdition -RequestedServiceObjectiveName $NewPricingTier
    
    $ScaleRequest

    The same can also be performed via Azure CLI or Azure template deployment. You can choose appropriate method depends on existing code you use to run the script.


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Wednesday, April 1, 2020 1:24 PM
  • Hi Vidhya,

    Ok, after about 1h struggle I've been able to reset my password. I'm going to re-try the script now using F8 method.

    And yet another issue with the running of the script. Am I supposed to hit Run button next to it? If I do so, I get this dialog

    Yet if I highlight all the code and use run selection, it does run.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi N Wednesday, April 1, 2020 3:40 PM
    Wednesday, April 1, 2020 3:00 PM
  • Hi Kalyan,

    I tried watching the video, but the girl didn't explain the very basics.

    Say, how exactly did she create this $sqlcred variable (e.g. where this variable was - it was not in the script). Also, what exactly am I supposed to put into ServerInstance and how exactly am I supposed to create that credential variable if I'm supposed to use it?

    Say, in the servers section I am connected to my server. If I run select @@servername I get the name.

    However, if I try

    $serverInstance = "namefromservername"
    Get-SqlDatabase -Name DBNameHere -ServerInstance $serverInstance 

    I then get

    Get-SqlDatabase : Failed to connect to server namefromservername.
    At line:3 char:1
    + Get-SqlDatabase -Name DBNameHere -ServerInstance $serverInstance
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (System.String[]:String[]) [Get-SqlDatabase], ConnectionFailureException
        + FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.IaaS.GetSqlDatabaseCommand

    -------------------------------------

    If I try without using ServerInstance then I don't get any output at all.

    Should I use the exact server name as I put into the connection when I try to connect?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, April 1, 2020 4:06 PM
  • Hi Vidhya,

    One more question - if I try to re-start this script, how important is the 'ResourceGroupName' parameter?

    I can not find that name anywhere and it looks like our subscription simply doesn't even have it.

    It seems to be running it now (even though I selected S0 and I hope it is already at S0). Also, I didn't provide the ResourceGroupName - what it was supposed to be?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Wednesday, April 1, 2020 4:17 PM
    Wednesday, April 1, 2020 4:10 PM
  • Hi Naomi,

    Good to know you moved to next level. I thought you will retrieve the saved passwords from Google Chrome :-)

    This is powershell command so I would expect powershell extension is installed properly in Azure Data Studio otherwise these commands can be run from powershell directly or powershell ise.

    Regarding resource group, its kind of logical grouping where the resources exist. Lets say you can have a resource group for HR department and can have all resource within it. It's is required.

    You can obtain the resource group details using the below command. Make sure you connect to the Azure/tenant before you run it.

    Get-AzureRmResource | Where-Object {$_.Name -eq $ServerName} | ft


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Wednesday, April 1, 2020 4:41 PM
  • I can try that in a moment. I was also trying to follow https://docs.microsoft.com/en-us/sql/azure-data-studio/tutorial-qds-sql-server?view=sql-server-ver15 but unfortunately I don't see the 'Explain' button from the step 4.

    In regards to my attempts to run the scale request, the first part (connecting to the server) did work, but I never saw any output from the scale request command and the powershell icon on the left just kept spinning, so I have no idea if anything was working or not. Perhaps I should have selected a different DB, but I was just trying S0 and it was already at S0.

     UPDATE. Ran that command and it returned nothing at all.

    Also, how can I somehow save that credentials? E.g. in order to run anything I repeat the top part of the script with Get-Credential. 

    Also, I was not supposed to set any variables for that command to run it, right? 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi N Wednesday, April 1, 2020 5:22 PM
    Wednesday, April 1, 2020 5:11 PM
  • Also, is something wrong? If I switch to Output I see the following:

    4/1/2020 11:59:13 AM [NORMAL] - Visual Studio Code v1.42.0 64-bit
    4/1/2020 11:59:13 AM [NORMAL] - PowerShell Extension v2020.3.0
    4/1/2020 11:59:13 AM [NORMAL] - Operating System: Windows 64-bit
    4/1/2020 11:59:13 AM [NORMAL] - Language server starting --
    4/1/2020 11:59:13 AM [NORMAL] -     PowerShell executable: C:\WINDOWS\System32\WindowsPowerShell\v1.0\powershell.exe
    4/1/2020 11:59:13 AM [NORMAL] -     PowerShell args: -NoProfile -NonInteractive -ExecutionPolicy Bypass -Command Import-Module

    (I'm skipping long part of it)

    and then

    4/1/2020 11:59:13 AM [NORMAL] - powershell.exe started.
    4/1/2020 11:59:13 AM [NORMAL] - Waiting for session file
    4/1/2020 11:59:15 AM [NORMAL] - Session file found
    4/1/2020 11:59:15 AM [NORMAL] - Registering terminal close callback
    4/1/2020 11:59:15 AM [NORMAL] - Registering terminal PID log callback
    4/1/2020 11:59:15 AM [NORMAL] - powershell.exe PID: 22572
    4/1/2020 11:59:15 AM [NORMAL] - Language server started.
    4/1/2020 11:59:15 AM [NORMAL] - {"status":"started","languageServiceTransport":"NamedPipe","languageServicePipeName":"\\\\.\\pipe\\PSES_pvrtwl4i.uta","debugServiceTransport":"NamedPipe","debugServicePipeName":"\\\\.\\pipe\\PSES_fcv2bqjz.op4"}
    4/1/2020 11:59:15 AM [NORMAL] - Connecting to language service on pipe \\.\pipe\PSES_pvrtwl4i.uta...
    4/1/2020 11:59:15 AM [NORMAL] - Language service connected.

    4/1/2020 11:59:17 AM [WARNING] - Invalid Version: null

    Do I need to do something to fix this? Also, how can I clear this output?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, April 1, 2020 5:26 PM
  • Answering the original question - turned out (as Erland pointed out) that it can be done by the ALTER DATABASE script (which I really hoped for)

    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=azuresqldb-current


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 2, 2020 3:50 AM