Difference and similarity between merge and merge join transformation

Difference and similarity between merge and merge join transformation?

Merge Transofrmations                                              Merge Join Transformation
1)The data from 2 input paths are merged into one     1) The data from 2 inputs are merged based on some common key.
2)Works as UNION ALL                                             2) JOIN (LEFT, RIGHT OR FULL)
3)Supports 2 Datasets                                                   3)1 Dataset

4)Metadata for all columns needs to be same                 4)  Key columns metadata needs to be same.

Pre-requisites 
5)Data must be sorted.
Merged columns should have same datatype i.e. if merged            5)  Data must be sorted.
 column is EmployeeName with string of 25 character in Input 1,
 it can be of less than or equal to 25 characters for merging to 
happen.

                                                                                       

                                                                                          Merged columns should have same datatype i.e. if merged column is EmployeeName with                              string of 25 character in Input 1, it can be of less than or equal to 25 characters for merging to happen.

Limitations 
Only 2 input paths can be merged.

Does not support error handling.                                      6)    Does not support error handling.
                                                                                                Use 
                                                                                              Merging of data from 2 data source

Can create complex datasets using nesting merge transformation,

                                                                                            7) When data from 2 tables having foreign key relationship needs to present based on common key.

How to Identify and Fix Orphaned Users in SQL Server

We know about logins and users in SQL Server, to connect to instance and database we have to go through a defined principals. This principal is defined by Microsoft SQL Server to authenticate and verify the login credentials and decides whether to allow access or not based on certain defined criteria. You can query and find the login details in sys.server_principals and sys.syslogins views.

Sometimes when we try to login to newly migrated database or new restored database we get authentication issue. Basically, SQL Server login is mapped to database user and if this mapping is broken or it is not correctly defined as per SQL Server pricipals then login will not be successful for that particular user of database on particular instance and this user is called orphaned user. Also if security identifier is not available for particular database user in server instance, in that case also user becomes orphaned. If you will query sys.server_principals and sys.syslogins you will get some useful information but it is insufficient details for login as login password is not in actual format. So here we are going to find out orphaned users and techniques to map those users with correct security identifier in an instance.

If we refer sys.server_principals and sys.syslogins view it will give you below details.

SELECT name, principal_id, default_database_name, default_language_name, type_desc
FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN'
GO
SELECT loginname, dbname, password, language FROM sys.syslogins
WHERE password IS NOT NULL
GO

How to find Orphaned Users

I have restored my database ‘ClientInfo’ from one server to another server and I am going to check if there is any orphaned user available. We can execute below query on query editor window for particular database which we have restored or attached.

USE ClientInfo
GO
sp_change_users_login 'Report'
GO

So here we have two orphaned users ‘sa’ and ‘Lisa’ and their username and SID is given side by side . We will use this SID to map and fix the orphaned users.

How to find Orphaned Users

I have restored my database ‘ClientInfo’ from one server to another server and I am going to check if there is any orphaned user available. We can execute below query on query editor window for particular database which we have restored or attached.

USE ClientInfo
GO
sp_change_users_login 'Report'
GO

So here we have two orphaned users ‘sa’ and ‘Lisa’ and their username and SID is given side by side . We will use this SID to map and fix the orphaned users.

How to Fix Orphaned Users

We will cross check details by following query for Login SID and User SID differences and we will map it by query. As we can observe for login ‘Lisa’ there is a difference in Login SID and User SID.

USE ClientInfo
GO
SELECT name "LoginName", sid "LoginSID" FROM sys.syslogins WHERE name = 'Lisa'
GO
SELECT name "UserName", sid "UserSID" FROM sys.sysusers WHERE name = 'Lisa'
GO

If you will try to login with login ID ‘Lisa’, you will get authentication failure error. So to fix this login we will use below query because SQL Server login name and user name is same so we can map it easily.

USE ClientInfo
GO
EXEC sp_change_users_login 'Auto_Fix', 'Lisa'
GO

Now again we will execute the query to check orphaned user details. So now login ‘Lisa’ is fixed and we can login successfully.

USE ClientInfo
GO
sp_change_users_login 'Report'
GO

Now consider different scenario, if you have restored the database but login is not available in the instance then you have to create the same to map with database user, You can do the same by executing below query.

USE ClientInfo
GO
EXEC sp_change_users_login 'Auto_Fix', 'Lisa', NULL, 'B@6P@$$w0r6'
GO

where Auto_Fix maps the SQL Server login with the records available in the sys.database_principals view.

above query will check user ‘Lisa’ if it doesn’t exist then it will create a new one with password  B@6P@$$w0r6.

If you want to map a database user with new login, you can do the same by following query.

--Create Login
CREATE LOGIN LisaNew WITH PASSWORD = 'N3wP@$$w0r6'
GO
--Map User Lisa to Login LisaNew
USE ClientInfo
GO
EXEC sp_change_users_login 'Update_One', 'Lisa', 'LisaNew'
GO

Here Update_One maps the user to existing SQL Server login. You have to first create the login then map the user to created login.

Kindly note that Microsoft has included sp_change_users_login to deprecated features after SQL Server 2012 and asked to use ALTER USER feature in future development works.

SQL Server Database Migration Checklist

Migrating a database is very critical and time bound process, if anything goes wrong then its very difficult to rollback things and move back to existing environment again. So its very important to prepare a checklist before migrating a database and also keep a rollback plan ready in case of migration failure.

At some point of time we have to migrate old version of database server to new version of database server because of technology and feature enhancements, business requirements changes or hardware upgradation etc. We have two options for database migration, either we can go for in-place upgrade where new version of SQL Server is installed on the same machine where older version of SQL Server exists, this is automated process similar to SQL Server installation and second method is side by side migration where new version of SQL Server is installed on new system and when new system is ready, applications and other links and connectivities are pointed to it. It is a manual process where you have to copy or move each and every object from old server to new server manually.

Here I am going to list key points to keep in mind when doing side by side database migration.

Pre-Migration Checklist

1. Run upgrade advisor on existing database to check the compatibility and deprecated features with respect to database and applications and make the required changes accordingly.
2. Note down data file and log file locations and size and make sure new server has enough disk space available.
3. Note down database recovery model, collation type and database facets.
4. Note down compatibility level, database owner, linked server, full text catalogs and trustworthy settings details.
5. Always keep more than one copy of latest database backup if it is feasible and you have enough storage available.
6. Note down information regarding database logins, users and orphan users and permissions.
7. Take a copy of all SSIS packages and config files and note down disk locations for files to move.
8. Generate scripts for all SQL Server agent jobs.
9. Note down all existing database maintenance plan and its properties.
10. Generate all the SQL Server logins and keep it safe to deploy on new server.
11. Note down windows logins and groups and permissions if any.
12. Check if any Disaster recovery or high availability settings are available and make a note of that.

Once you are ready with pre-migration checklist, start performing migration.

Migration Checklist

1. Make sure you stop all applications services connected with database.
2. Set database to read only mode if required.
3. Take the latest backup of databases.
4. Restore latest copy of database on new server.
5. Check and change the database compatibility level after restore.
6. Migrate all the user logins and windows logins to new server.
7. Check the database properties and alter it accordingly if required.
8. Enable trustworthy database setting if required or keep it as default.
9. Verify the orphan users and fix the same.
10. Execute DBCC UPDATEUSAGE command to correct pages and row counts on migrated database.
11. Execute DBCC CHECKDB on new migrated database to check the integrity of the objects.
12. It is very important to rebuild all indexes on newly migrated database else you will face performance degradation while running the applications.
13. Make required changes at application pointer to database and other connectivity settings.
14. Update statistics on migrated database tables.
15. Recompile all stored procedures, functions and triggers with sp_recompile.
16. Refresh all the views available in the migrated database with sp_refershview.
17. Deploy your high availability or disaster recovery plans if any on new database.
18. Now test the application and correct the errors if any else celebrate success.

Queries used in Migration Checklist

– Take database backups

– Restore database backups

– Verify compatibility level and change the same if required.

--Verify Compatibility Level
SELECT name, compatibility_level, collation_name FROM sys.databases
GO
--Change Compatibility Level
USE [master]
GO
ALTER DATABASE [DB_Name] SET COMPATIBILITY_LEVEL = 110
GO

– Note down Linked Servers details.

--Verify Linked Servers
SELECT * FROM sys.sysservers
GO

– Note down recovery model details.

--Check Recovery Model
SELECT name, recovery_model_desc FROM sys.databases
WHERE name = 'DBName'
GO

– Note down collation setting.

--Verify Collation setting
SELECT name, collation_name FROM sys.databases
WHERE name = 'DBName';
GO

– Migrate all user logins and passwords – click here to know how to migrate logins and passwords.

– Verify database properties and alter the same if required.

--Check Database Properties
SELECT *FROM sys.databases SD
JOIN sys.syslogins SL ON SD.owner_sid = SL.sid
GO

– Enable trustworthy database settings if required.

--Verify Trustworthy Settings
SELECT is_trustworthy_on FROM sys.databases WHERE name = 'DBName'
GO
--Enable Trustworthy Database Settings if required
ALTER DATABASE DBName SET TRUSTWORTHY ON
GO

– Verify and fix orphan users – click here to know how to fix orphan users.

– Correct all the pages and row counts with DBCC UPDATEUSAGE command.

--Correct Pages and Row Counts
DBCC UPDATEUSAGE('DBName') WITH COUNT_ROWS
GO

– Check integrity of objects with DBCC CHECKDB command.

--Check Integrity of Objects
DBCC CHECKDB('DBName') WITH ALL_ERRORMSGS
GO

– Rebuild all indexes by creating maintenance plan or by manual query.

--Script for Index Rebuild
USE DBName
GO
ALTER INDEX ALL ON dbo.TableName REBUILD
GO

– Update database tables statistics.

--Update Database Statistics
USE DBName
EXEC sp_updatestats
GO

– Recompile all stored procedures, functions and triggers.

--Recompile Objects
USE DBName
EXEC sp_recompile 'ObjectName'
GO

– Refresh all the views of database.

--Refresh Views
USE DBName
EXEC sp_refreshview 'ViewName'
GO