Documentation of dependencies and Mapping Process

Hey guys, apologies if should be in a better place of forum. I've inherited some staff using SQL/SSIS for some data processing and am not by trade a SQL expert so flying a little blind.

Currently, one of our large processes, is spread across two servers. Currently Server A is used to pseudonomise data and do some processing before it's pushed to the Server B with further processing and end tables production. I need to move all processing from Server A in to Server B, leaving Server A, purely for the pseudonomisation routing.

I was looking to see if anyone could lend their thoughts on ground work we need to do, to scope things out. I was hoping to map out the flow from end tables on Server B, right back to the data being loaded on server A. What scripts/packages are run and effectively have a data flow map. Conscious also that I may need to identify dependencies etc before we start to make this move.

Happy to provide specifics if it would help, but wanting to minimise the pain and take any learnings from others before we commence the move. (Base documentation of existing process is naturally very scant)...

Thanks!

Only thought that I have is that if not already done OUTLAW! and 3 any 4 part naming (so that would be Database.Schema.Table and Server.Database.Schema.Table)

Create SYNONYMS instead so that

MyDataBase.dbo.SomeTable

becomes (using my own naming convention)

MyDataBase.SomeTable

If in the future stuff needs moving again you can just adjust the SYNONYMS rather than having to find all the places in the code where MyDatabase needs to change to SomeNewDatabaseName, and ditto for MyOtherServer changing to YetAnotherServerName

I don't use SSIS so don't know to what extent this is possible / matters in that context.

Thanks for the reply. Apologies for my ignorance, but why not

dbo.SomeTable

This is how most of the code is written.

You have Schema and then Tables. The combination of Schema and table must be unique. Sometimes I see several Schemas, sometimes the only schema used is "dbo" (which is the default).

If you have a table "dbo.Customers", and your database is called "Accounts", on a server called "SQL01" then you can reference the rows in that table using any of:

SELECT Col1, Col2, ... FROM Customers    -- schema is implicit
SELECT Col1, Col2, ... FROM dbo.Customers
SELECT Col1, Col2, ... FROM Accounts.dbo.Customers
SELECT Col1, Col2, ... FROM SQL01.Accounts.dbo.Customers

Of these the best is #2 - naming the schema explicitly is slightly more efficient than #1 and, of course, required if the table is not in the default schema.

#3 and #4 are a pain if you move the APP to a different Database / Server - e.g. if you have DEV / QA / Production databases, because you have to change the Database (and Server) names wherever they occur in the code.

If your SQL statement also needs to reference a table in another database (and possibly on a different server - lets call that "SQL02") then you can do

SELECT C.Col1, C.Col2, ..., CRM.ColX, CRM.ColY, ...
FROM dbo.Customers AS C
     JOIN SQL02.RemoteDatabaseName.dbo.CRM_Stuff AS S
         ON S.CustomerID = C.CustomerID

then you immedaitely have the problem that "SQL02.RemoteDatabaseName.dbo.CRM_Stuff" contains hardwired names for both the Server and the Database, and moving code from DEV to QA to Production then has a problem.

So instead you can create SYNONYMs, lets say you create a SCHEMA called CRM, solely for the purpose of "housing" the Synonyms for the CRM tables on the remote server SQL02. Now you can do:

SELECT C.Col1, C.Col2, ..., CRM.ColX, CRM.ColY, ...
FROM dbo.Customers AS C
     JOIN CRM.CRM_Stuff AS S
         ON S.CustomerID = C.CustomerID

and on the Production "Accounts" database mounted on SQL01 we can define CRM.CRM_Stuff as being located in SQL02.RemoteDatabaseName.dbo.CRM_Stuff, whereas on a SQL_DEV server we might have both the DEV Versions of Accounts Database AND the DEV Version of RemoteDatabaseName on the same box (or on two DEV boxes, or that might change week-to-week!!) and we just set up / change the Synonyms to "point" to the right database / server appropriately. No code [referencing that table] needs to change at all.

IF (SELECT OBJECT_ID('CRM.CRM_Stuff ')) IS NOT NULL
	DROP SYNONYM CRM.CRM_Stuff 
	
CREATE SYNONYM CRM.CRM_Stuff 
FOR SQL02.RemoteDatabaseName.dbo.CRM_Stuff 
2 Likes

many thanks for your reply!