locked
Deployment tools for SQL scripts RRS feed

  • Question

  • User84044094 posted

    Hello All,

    I have a requirement to deploy multiple sql scripts across multiple servers as part of our regular release deployments.Currently we are using a company internal tool to achieve this,but is giving so many issues of late.Basically for every release we keep all the .sql files in different folder and check in the build folder into TFS.Is there any other way /tool which I can use to bulk deploy the scripts to multiple servers?Please suggest

    Tuesday, June 26, 2018 8:52 AM

All replies

  • User347430248 posted

    Hi Sudheer60,

    You can try to refer script below may help you to solve your issue.

    -- Script to deploy to multiple servers
    DECLARE @InstanceName VARCHAR (125)
    DECLARE @mySQL VARCHAR(250)
    
    -- Set up cursor for your sql instances
    DECLARE myInstances CURSOR FOR SELECT SQLInstanceName FROM mySQLinstancesTable
    
    OPEN myInstances
    FETCH NEXT FROM myInstances INTO @InstanceName
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT 'I am now working on instance: ' + @InstanceName
    
    -- Connect to the sql instance
    SET @mySQL = 'sp_addlinkedserver @server = [' + @InstanceName + ']'
    EXEC (@mySQL)
    
    -- Run a command remotely
    SET @mySQL = 'SELECT COUNT(*) FROM [' + @InstanceName + '].[MASTER].[dbo].[SYSINDEXES]'
    EXEC (@mySQL)
    
    -- Drop the remote instance connection
    SET @mySQL = 'sp_dropserver [' + @InstanceName + ']'
    EXEC (@mySQL)
    
    FETCH NEXT FROM myInstances INTO @InstanceName
    END
    
    CLOSE myInstances
    DEALLOCATE myInstances

    Reference:

    Deploying Scripts To Multiple Instances

    Regards

    Deepak

    Thursday, June 28, 2018 1:24 AM