AWS-RDS SQL Server limitations

On-prem to AWS RDS SQL Server has some differences. Processes need to be reviewed Read more →

On-Demand Instant File Initialization status with sys.dm_server_services

Configuring a SQL Server with Instant file initialization is a fairly standard part of SQL Server Installation Checklists. There are substantial benefits to the duration taken during a SQL Server database restore. If you want to read more on how to set up instant file initialization and the technical benefits read SQL Server faster restores with instant file initialisation When I first started using instant file initialization I used to manually check using the gpedit.msc console - and checking the Windows Settings | Security settings | Local Policies | User Rights Assignments. Eventually I wrote a script to check as... Read more →

A great way to Link SQL Server and MongoDB with BI Connector

Step 1 - Ensure MongoDB BI Connector is installed and configured . This will support the access to the MongoDB server. How to unlock the secrets of MongoDB BI Connector Step 2 - Install MongoDB ODBC Driver for BI Connector - provides connectivity between a SQL client and the MongoDB Connector for BI More details on https://docs.mongodb.com/bi-connector/master/reference/odbc-driver/ Step 3 - Create the System DSN You'll need the following base information to connect to the MongoDB BI Connector Server Name\Port User. Password Read further for full details of the Create System DSN options Step 4 - Create the Linked Server This... Read more →

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 →

Connect SQL Server to AS400 with a Linked Server

Question: I need to set up a Linked Server between SQL Server 2016 and AS400 to extract data from a file and import the data onto a table on a SQL Server database. How do I set up the Linked Server? Answer:Setting up a Linked Server from SQL Server to AS400 can be tricky , as there a number of variables. But the method I've outlined below has repeatedly worked for me. Thy trickiest part - and normally most fails occur is in the Provider String . the details below have worked for me 1)Step 1 : Obtain the Microsoft... Read more →