Database compatibility changing to 100 when restoring SQL 2005 to SQL 2017

Question: Don't ask , but I'm restoring some SQL Server 2005 databases onto a SQL Server 2017 - and there is some database compatibility behaviour I was not expecting. When the database is attached to SQL Server 2005 - the database compatibility is 90. But when restored to SQL Server 2017 - the database compatibility is set at 100. Why is this happening? Answer: This is expected behaviour. i.e when changing from a lower version to a higher version - if the database compatibility of the source db is lower than the lowest compatibility of the target SQL Server version... Read more →

Separating the Application and Database server

“I have a new product we need to install. The vendor documentation supports both co-located application and database server , and also remote access to the database server” This sort of comment is standard amongst product documentation. As a DBA I need to review and ensure the product implementation is in line with the organisation standards and the standardisation standards required in managing large amounts of servers. Consolidate.Data Center Migration.Cloud Computing.VM Disaster Recovery , these are some of the background themes when establishing an approach to managing a database server environment. We’re in the age of Server consolidation and services... Read more →

Data Migration Assistant for SQL Server 2016 Upgrade

The Data Migration Assistant (DMA) has replaced the SQL Server Upgrade Wizard. The big difference is the Data Maigration Assistant also offers an option to Migrate the databases. It's a key step in documenting how to update SQL Server. The details from the DMA give database owners detailed information about any changes required The purpose of this post is to focus on Assessment only not on the Migration Aspect. The Assessment type of usage is very similar to the Upgrade Wizard. There is also a command line option for the Data Migration Assistant , but I'll discuss the command line... Read more →

SQL Server Stuff and comma delimited column

Question: I have two tables and would like to return a recordset which is made up of table1 plus an extra column which is made up of a comma-delimited string from table2 based on an INNER JOIN. I’ve read the comma delimited article , but this returns the full recordset as a CSV.SQL Server – Create a comma delimited sql result set (SQL Server ... Answer: One option to use is the SQL Server STUFF function.The SQL Server STUFF function is used to insert one string into another string. This makes it useful for what you’re attempting to achieve. You... Read more →

Database Tuning Advisor Performance

Question: Some developers are requesting access to Database Tuning Advisor (DTA) on a Production Server. I’m not a big fan of DTA , but I can see how developers will find it useful. The main reason why I resist using DTA is it forces DBAs into lazy thinking. I strongly believe best practises for set up , capacity planning and monitoring are the cornerstone of a solid SQL Server environment. I’d like to present some reasons to the developers to resist using DTA on Production. Can you give some real-world reasons? SQL Server Install Checklist Answer: I agree with your... Read more →