HomeAdmin ManualTroubleshooting GuidesMicrosoft SQL Server

12.3. Microsoft SQL Server

Connecting to a Named Instance

In order to connect to a named instance of SQL Server you will need to configure an alias for that instance as described in the Create an Alias section on this page: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/create-or-delete-a-server-alias-for-use-by-a-client?view=sql-server-ver16

General SQL connectivity troubleshooting tips from Microsoft are available here: https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/connect/resolve-connectivity-errors-overview

Common Connection Troubles

Error: "The statement has been terminated."

In extremely rare cases SQL Server can lose seeding on the HS_Request table or another tables, primary key. If this occurs inserts to the database will fail with this error. You can check if this is the case by running the following in management studio:

DBCC CHECKIDENT ('HS_Request');

If the current seed value doesn't match the current column value (it will be less) than there's a problem. You can reseed the tables primary key with this command:

DBCC CHECKIDENT ('HS_Request',RESEED,######);

where ###### is the new ID value. Make this a few higher than the current highest value in the table. That should resolve the issue.

Moving from local SQL Server to SQL Server on RDS

Use SQL Server Management Studio and select the database that will be used as the source. Right click on the database and select the generate scripts task. Select advanced, scroll to types of data to script and select Schema and Data. Once the file is saved, open it to make some minor changes for Amazon RDS compatibility.  Change the path in the FileName section of the script to be D:\RDSDBDATA\DATA\ instead of the default C:\Program Files\..... . Save the script. Next, run the script to import the data and the schema. The last step is to open security on the newly created database and ensure that the user created in the script has db_owner rights to the database.
 
Performing the above steps, allowed the user to copy the schema and the data from a SQL Server 2008R2 instance on a traditional Windows Server 2008 R2 instance and migrate it intact to an Amazon RDS instance.

Knowledge Tags

This page was: Helpful | Not Helpful