We have a scenario in which we want to change the collation of our production database (including columns) from SQL_Scandinavian_Pref_CP850_CI_AS to Finnish_Swedish_CI_AS. We have developed scripts to do that. But executing this script in a large database of size more than 100GB will take considerable time and we can't afford to take very long downtime. So we decided to reduce this downtime using the below strategy:
- We will setup Transactional Replication and we will initialize the subscriber using the database backup method.
- Publisher database will be live with the application and its transactions will be delivered to the subscriber database through Transactional Replication.
- We will execute the Collation Change Script on the subscriber side, and it does allow us to execute this script when SQL Server is the same for publisher and subscriber databases. We recently found this in SQL Server 2019.
- Now, the pain point is, it's not replicating correctly the varchar, char column's data when it contains special characters like 'åÅäÄöÖ'. On the subscriber side, we are getting weird characters like '†„Ž”™'
Can you please suggest how we can resolve this bug or any alternative architecture to minimize the downtime in production while changing the database collation(including columns)?
Furthermore, my collation change script is doing the following tasks on the subscriber database to change its collation:
- Drop Foreign Key Constraints
- Drop indexes including Primary Key
- Drop check and default constraints
- Drop user statistics
- Drop views, computed columns, SPs to resolve Object Bound bugs
- After execution of the above steps, the Tables are ready for collation change. So, the script will change the collations of the columns for every table one by one.
- Recreate above listed constraints after successful execution of step 6.