locked
How to deploy the code in multitenant architecture database? RRS feed

  • Question

  • Hi Experts,

        We have database with multi tenant architecture in that using multi tenant database (Different database for each tenant),

    The big challenge here is to deploy the same code in different database.  can anyone please suggest over this.

    For Example :

    I have database called (MasterTenantDB,Tenant1DB,Tenant2DB,Tenant3DB) and having one procedure (dbo.USP_GetUser) which i want to execute through out all database what will be the best way to execute in each database. Doing it manually its some what time consuming and having risk to execute manually if missed to execute any database.

    Regards,

     

     


    Niraj Sevalkar

    Tuesday, May 17, 2016 3:33 PM

Answers

  • There are some third party tools that can help you publish code for multiple databases. They were handy for us when we had to make changes to stored procedures across multiple clients.

    you can also use the tool to execute procedure across multiple clients. you can also do this using sp_msforeachdB - but this inculdes all databasesinculding system, which you might not want, in that case you can easily come up with a script..

    example:

    select 'execute  '+ Name +'.dbo.myprocedure' from sys.databases where name not in('master','model','tempdb','MSDB')


    Hope it Helps!!

    Tuesday, May 17, 2016 3:44 PM

All replies

  • There are some third party tools that can help you publish code for multiple databases. They were handy for us when we had to make changes to stored procedures across multiple clients.

    you can also use the tool to execute procedure across multiple clients. you can also do this using sp_msforeachdB - but this inculdes all databasesinculding system, which you might not want, in that case you can easily come up with a script..

    example:

    select 'execute  '+ Name +'.dbo.myprocedure' from sys.databases where name not in('master','model','tempdb','MSDB')


    Hope it Helps!!

    Tuesday, May 17, 2016 3:44 PM
  • USE master
    CREATE TABLE test (c1 VARCHAR(50)) 
    INSERT test VALUES('master')
    go
    CREATE PROC sp_test AS
    SELECT * FROM test
    GO
    USE northwind
    CREATE TABLE test (c1 VARCHAR(50)) 
    INSERT test VALUES('northwind')
    USE pubs
    CREATE TABLE test(c1 VARCHAR(50))
    INSERT test VALUES('pubs')
    USE pubs
    EXEC sp_test --returns 'master'
    USE master
    EXEC sp_MS_marksystemobject sp_test
    USE pubs
    EXEC sp_test --returns 'pubs'
    USE northwind
    EXEC sp_test --returns 'northwind'

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, May 18, 2016 7:16 AM