I have been put through some grief a few times in last year due to issues caused by SQL collation mismatch. It happened across a few projects that worked on in that duration.
Each time it so happened that when we got ready to deploy the code to a different environment, SQL Server was deployed by a different team in a different geographical location. We provided deployment script and database backups for the databases present in our development environment. Invariably this caused a mismatch between the collation setting of the SQL Server Instance and the database and caused runtime errors complaining about the mismatch in collation.
The fix was pretty straightforward and in our case, a little less risky because the target environments were development and testing environments. The steps and commands are explained in detail here.
A few things to note are:
Do not forget to take backup of the existing databases. "Rebuilddatabase" option removes all the databases.
Default SQL Server Instance name is usually MSSQLSERVER.
User name is case sensitive. We struggled with administrator vs Administrator mismatch when running the commands.
The command is executed in silent mode, so you will not be able to see any progress signs etc. If there are any issues when running the commands then you can check EventViewer for the details.
We can avoid all this pain if we are careful when installing SQL Server though :).
Each time it so happened that when we got ready to deploy the code to a different environment, SQL Server was deployed by a different team in a different geographical location. We provided deployment script and database backups for the databases present in our development environment. Invariably this caused a mismatch between the collation setting of the SQL Server Instance and the database and caused runtime errors complaining about the mismatch in collation.
The fix was pretty straightforward and in our case, a little less risky because the target environments were development and testing environments. The steps and commands are explained in detail here.
A few things to note are:
Do not forget to take backup of the existing databases. "Rebuilddatabase" option removes all the databases.
Default SQL Server Instance name is usually MSSQLSERVER.
User name is case sensitive. We struggled with administrator vs Administrator mismatch when running the commands.
The command is executed in silent mode, so you will not be able to see any progress signs etc. If there are any issues when running the commands then you can check EventViewer for the details.
We can avoid all this pain if we are careful when installing SQL Server though :).
No comments:
Post a Comment