create - sql server alias configuration

Cómo crear un alias de base de datos en SQL Server

Question

Tenemos un software muy antiguo creado hace unos 10 años y no tenemos código fuente.

El software utiliza dos bases de datos, DB01 y DB02 en la misma instancia de SQL Server 2012.

Hay sentencias de SQL como db01..table1 join db02..table2 , pero el problema principal es que nuestros procesos no nos permiten usar db02 como nombre de la base de datos.

La pregunta es: ¿cómo podemos crear un alias para la base de datos?

Estaba tratando de usar CREATE SYNONYM

CREATE SYNONYM [db02] FOR [db02_new_name];

pero no funciona para nombres de base de datos.

Por favor, sugiera cómo puede resolverse sin aplicar parches a archivos binarios para corregir las sentencias de SQL.

Answer 1

La pregunta es: ¿cómo podemos crear un alias para la base de datos?

Sé que este es un post viejo pero ...

Es por eso que solo uso la convención de nomenclatura de 2 partes para objetos SQL. Me permite tener sinónimos de 2 partes que apuntan a bases de datos con nombres diferentes dependiendo del entorno en el que me encuentre. Hay algunos lugares donde no funciona tan bien pero, en su mayor parte, esos lugares son muy raros.

En cuanto al software del que no tiene el código fuente y si ese software usa la convención de nomenclatura de 3 partes, probablemente no tenga suerte a menos que sepa cuál es la convención de nomenclatura de 3 partes para cada objeto y cree una 3 partes sinónimo de cada objeto.

Answer 2

1. Vaya a la base de datos que desea crear Alias,

2. Cree una tabla de carpetas de alias con el diseño preferido,

3. Vaya a la tabla de ID únicos y verifique la última secuencia de códigos para la tabla creada.

   Por ejemplo, si el último código es 10, actualícelo a 11.

4. Abra la tabla de Gabinetes y vaya a la derecha en la parte inferior y cree el nombre del gabinete Alias ​​que desea.

Answer 3

Aquí hay un proc almacenado para hacerlo. Simplemente agréguelo a su base de datos y llámelo con la base de datos de destino. Creará sinónimos para todas las tablas en la base de datos de destino y creará los esquemas si no existen. He dejado una sección comentada en caso de que alguien sepa cómo hacer que los esquemas de creación funcionen sin un cursor.

CREATE PROCEDURE CreateSynonymsForTargetDatabase (
    @databaseName sysname
)
AS BEGIN
DECLARE @TSQL nvarchar(max) = N''''
DECLARE @rn char(2),
    @SchemaName sysname;

    SET @rn = char(13) + char(10)   

    CREATE TABLE #DBSynonym(        
        [Schema] sysname NOT NULL,
        [Table] sysname NOT NULL
    )

    SET @TSQL = N''
        INSERT INTO #DBSynonym ([Schema], [Table])
        SELECT Schemas.name, Tables.name
        FROM ['' + @databaseName + ''].sys.tables 
        INNER JOIN ['' + @databaseName + ''].sys.schemas on tables.schema_id = schemas.schema_id      
    ''

    EXEC (@TSQL)
    SET @TSQL = N''''

    DECLARE MissingSchemasCursor CURSOR
    READ_ONLY
    FOR 
        SELECT newSchemas.[Schema]
        FROM #DBSynonym newSchemas
        LEFT JOIN sys.schemas on newSchemas.[Schema] = schemas.name
        WHERE schemas.schema_id is null
        GROUP BY newSchemas.[Schema]

    OPEN MissingSchemasCursor
    FETCH NEXT FROM MissingSchemasCursor INTO @SchemaName
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
            SET @TSQL = N''CREATE SCHEMA '' + QUOTENAME(@SchemaName) + N'';''

            EXEC sp_executesql @TSQL
        END
        FETCH NEXT FROM MissingSchemasCursor INTO @SchemaName
    END
    CLOSE MissingSchemasCursor
    DEALLOCATE MissingSchemasCursor

    /*
    SELECT @TSQL = @TSQL +
        N''
        GO
        CREATE SCHEMA '' + QUOTENAME([Schema]) + N'';''
    FROM #DBSynonym newSchemas
    LEFT JOIN sys.schemas on newSchemas.[Schema] = schemas.name
    WHERE schemas.schema_id is null
    GROUP BY newSchemas.[Schema]

    PRINT ''CREATE SCHEMAS : '' + ISNULL(@TSQL,'''')
    EXEC sp_executesql @TSQL
    */
    SET @TSQL = N''''

    SELECT @TSQL = @TSQL +
        N''
        CREATE SYNONYM '' + QUOTENAME([Schema]) + N''.'' + QUOTENAME([Table]) + N''
        FOR '' + QUOTENAME(@databaseName) + N''.'' + QUOTENAME([Schema]) + N''.'' + QUOTENAME([Table]) + N'';''
    FROM #DBSynonym


    EXEC sp_executesql @TSQL
    SET @TSQL = N''''

END
GO

Úsalo de la siguiente manera:

EXEC CreateSynonymsForTargetDatabase ''targetDbName''

Answer 4

Crea una base de datos con el nombre que quieres suplantar. Re-jigg el generador de código DDL para crear una vista para cada tabla en la base de datos que tiene las tablas a las que necesito acceder a través del nombre codificado. Básicamente, cada vista tendrá una declaración que se ve así.

CREATE VIEW schemaname.tablename as SELECT * FROM targetdbname.schemaname.tablename

Ejemplo:

El nombre de la base de datos de destino que está codificado en ProdDBV1 se llama ProdDBV1 y la base de datos de origen que tiene se llama ProductDatabaseDatabaseV1 , el esquema es dbo y el nombre de la tabla es customer

1. Cree la base de datos llamada ProdDBV1 utilizando SSMS o script.
2. CREATE VIEW dbo.customer as SELECT * FROM ProductDatabaseDatabaseV1.dbo.customer

Si puede enumerar cada tabla en su base de datos "fuente" y luego crear el DDL como se muestra arriba. Si quieres puedo actualizar esta publicación con un ejemplo de código. (utilizando el procedimiento sp_msforeachtable si es posible)

Answer 5

Encontré la respuesta de Charles (y la solución alternativa en el comentario de maxcastaneda) muy útil. Seguí este enfoque y funciona para mí. Lo he optimizado un poco y he creado la siguiente consulta que muestra todos los sinónimos necesarios para crear.

Como requisito previo para este fragmento de código, tanto la base de datos original como el sinónimo / alias db deben estar en el mismo servidor, de lo contrario, en caso de que utilice un servidor vinculado o tenga que modificarlo un poco. Debería ser bastante fácil poner esto en una pequeña sp para actualizar los sinónimos automáticamente.

USE <SYNONYMDB>
SELECT 
''['' + TABLE_NAME + '']'', 
''['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'',
''IF EXISTS (SELECT * FROM sys.synonyms WHERE name = '''''' + TABLE_NAME + '''''') DROP SYNONYM [''+ TABLE_NAME + ''];   CREATE SYNONYM ['' + TABLE_NAME + ''] FOR <ORIGINALDB>.'' + TABLE_SCHEMA + ''.['' + TABLE_NAME + '']'' AS SynonymUpdateScript FROM <ORIGINALDB>.INFORMATION_SCHEMA.TABLES

No olvide ingresar sus nombres de Db en los puntos <...>.

Simplemente copie el contenido de la Columna de SynonymUpdateScript y ejecute en la base de datos de sinónimos o cree un procedimiento almacenado para esta tarea.

Tenga en cuenta que hay un problema si tiene vistas en el lugar que se refieren a tablas u otros objetos db sin la convención de nomenclatura de 2 partes. Esos sinónimos no funcionarán. Debe arreglar esto en los objetos / vistas originales.

Answer 6

Puede crear un alias de ''Administrador de configuración de SQL Server'' en la Herramienta de configuración en la carpeta de SQL Server.

Fuente detallada: http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/

http://technet.microsoft.com/en-us/library/ms190445.aspx

Answer 7

Tuve un problema similar
Resuelto con esta workaround , utilizando sinónimos.

Versión corta: inunda su base de datos con un sinónimo de todos los objetos que necesitará referenciar. Más tarde, volverá a crear todos los sinónimos con el nombre de la otra base de datos.