A Linked Server is SQL Server’s way to connect to other database servers (including other SQL Server instances) which are ODBC compliant or OLE DB data sources. The following will show how to create a Linked Server to an Oracle database. Using a Linked Server consists of 4 steps: 1. Obtaining an Oracle user for the Oracle database you will […]
Migrate database from SQL Server 2005 to SQL Server 2019
The following steps are used to migrate a SQL Server 2005 database (Northwind2005) to a SQL Server 2019 instance (Northwind2019): Step 1 – Setup the SQL Server 2019 instance You may already have an existing 2019 instance available. If you are setting up a new instance, please have a look at my list of default configurations that should be made. […]
My SQL Server 2019 Checklist
My settings checklist for a basic SQL Server 2019 instance. These are the initial settings that can be adjusted based on the database workload. Set Windows server to High performance power plan Optimize Windows server for Background Services Turn on Instant File Initialization (if allowed) Set log file size to 20-30% of the database size Set datafile and log file […]
Free Plan Cache for Stored Procedure
When creating a procedure, you may need to free the plan cache to create a new execution plan as the code changes. Or you may already be experiencing performance issues with a particular procedure and you would like to get a new execution plan based on the user queries. The following procedure frees the plan cache for a particular procedure […]
How to Restore a SQL Server database to a lower version
Recently, I was asked to restore a database from a 2019 production environment to a 2017 development environment. Both databases have the same collation and compatibility level set to SQL Server 2016: This can’t be done. At least, not using the traditional backup and restore process. However, this had to be done as the application that queried this database had […]
Oracle Index Rebuilds
While there may be no significant performance benefit by rebuilding indexes, the activity itself is usually low intensive and free in an on-premises environment. Therefore, a scheduled index maintenance job can only benefit the database. Usually, indexes on tables with a high number of deletes and updates are candidates for an index rebuild job. Rebuilding these indexes may lead to […]
Find Disconnected Servers Using PowerShell
Often we find ourselves troubleshooting outages which may have been caused by the physical server or virtual machine being unreachable rather than the database service itself being unavailable. End-users don’t know this. They simply call and say that the application or database is not working. We may try to access the database via SSMS or SQL Developer. If that fails, […]
Simple Restore Script
The SIMPLE_RESTORE script is useful if: You work in a company whose security policy requires that you write your own backup solution and present it before it gets put into production You cannot touch the production server because it is managed by a third party vendor but you do have access to the backups. You have no control over the […]
Free SQL Server 2019 Development/Testing Environment
Setting up a SQL Server 2019 environment for testing/development is very easy and does not cost anything. To setup a complete environment, all the following must be installed in order: Install and configure Virtual Machines. You can use any free Virtualization software, such as Virtual Box or VMWare Player Install Windows Server 2019 Evaluation copy. This is currently the most […]
Delete All Tables From a Schema in SQL Server
Recently, I’ve been migrating a lot of schemas from Oracle to SQL Server using the SQL Server Migration Assistant application. It is usually necessary to redo the table conversions a few times to get it right. The following script is used to create DROP scripts to drop all the tables in a particular schema. If you have foreign key constraints […]