Skip to content

Settings and activity

2 results found

  1. 25 votes
    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    You have left! (?) (thinking…)
    How important is this to you?

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    An error occurred while saving the comment
    Sandor Pakh commented  · 

    We use synonyms to abstract table definitions that point to AS/400 systems. Depending on our servers (dev, test, prod) these synonym definitions are different. Using synonyms does NOT force us to maintain different versions of the same stored procedures or queries that differ only in table names because these table names are different between dev, test and prod systems.
    ex:
    dev AS/400:
    CREATE SYNONYM [dbo].[SYN_TMAGENT] FOR [TPSSYNC].[ANDRXTST].[AGID06USR].[TMAGENT]

    prod AS/400:
    CREATE SYNONYM [dbo].[SYN_TMAGENT] FOR [TPSSYNC].[TPS].[AGID06USR].[TMAGENT]

    You can see that the 4 part definition differs between the two environments.

    It would be great if these multiple definitions of sysnonym objects can be stored in source control and an update/get operation would extract the appropriate one depending on the environment that it is being extracted to.

    Sandor Pakh shared this idea  · 
  2. 480 votes
    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    You have left! (?) (thinking…)
    How important is this to you?

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    under review  ·  Kendra responded

    Thank you everyone for your comments and votes on this over the years. While I don’t have a 100% full resolution for this suggestion, I can sum up our current recommendations here. Continued feedback is very welcome.

    Our current recommendation is to use the post-deployment script feature of SQL Source Control (released in V6.3) to manage SQL Server Agent jobs.

    An example script for this is here: https://documentation.red-gate.com/soc/common-tasks/working-with-pre-post-deployment-scripts/create-sql-server-agent-job

    As some commenters in this thread have alluded to, it is possible (and sometimes very common) for SQL Agent jobs to have steps that touch multiple databases on a single SQL Server Instance. For this reason, some customers prefer to create a separate database for instance-level management and objects (sometimes named DBA or similar) and choose to manage things like linked servers and SQL Agent jobs with the post-script associated with that database.

    This separate-database architecture also makes sense if the jobs…

    Sandor Pakh supported this idea  ·