SQL Server – How to Fix ‘Could Not Find Server Error’ with INSERT SELECT

amazon-rdssql server

I'm trying to copy a record from a production database table into a staging database table. They are both SQL Server databases on Amazon RDS. My query looks something like this:

INSERT INTO [neonpig.staging1.us-east-1.rds.amazonaws.com].[neonpig_prod_db].[dbo].[AspNetUsers] SELECT * FROM [neonpig.prod1.us-east-1.rds.amazonaws.com].[neonpig_prod_db].[dbo].[AspNetUsers]

But this seems to produce the error below:

Could not find server 'neonpig.prod1.us-east-1.rds.amazonaws.com' in
sys.servers. Verify that the correct server name was specified. If
necessary, execute the stored procedure sp_addlinkedserver to add the
server to sys.servers.

I've ensured that the server address are correct. Anyone know what I'm doing wrong?

Note that the database name is the same on both the prod server and the staging server.

Best Answer

To execute such statement you need to create LinkedServer first. In this case, you need to have LinkedServer from one RDS instance to another RDS instance.

https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/

RDS SQL Server to RDS SQL Server

For this scenario, both instances of RDS SQL Server must be private instances (not publicly accessible) within the same VPC. If the RDS SQL Server instances are publicly accessible, then you need to refer to the private IP of the RDS instances when creating the linked servers. Because you don’t have access to the private IP addresses of the servers, you need to use an EC2 instance in the same VPC to run NSLookup on the private instances of RDS. Do this for each RDS instance name.

NSlookup privatesql.cb2xkfsffmy7.us-west-2.rds.amazonaws.com

Address: 10.0.4.236

From there, the code is very much like the RDS to EC2 code, except now you use the private IP address of the remote RDS instance.

EXEC master.dbo.sp_addlinkedserver @server = N’RDSPrivate‘, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’10.0.4.236′;

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’RDSPrivate’,@useself=N’False’,@locallogin=NULL,@rmtuser=N'<username>’,@rmtpassword=‘<password>’;

GO

Just as we did preceding, use a four-part name to reference the remote RDS instance:

SELECT * from RDSPrivate.TestDB.dbo.t1;

Note one limitation for all RDS instances: If the physical server supporting your RDS SQL Server instance changes (such as when upgrading to a new version of SQL Server or changing the instance type), the private IP address of the RDS instance of SQL Server might change. This change can happen with no alerts or notifications to your administrative team. Thus, if the linked server connection fails, your first troubleshooting steps should include verifying that the private IP address of the RDS instance has not changed. If this risk is unacceptable in your environment, then AWS doesn’t recommend using the linked server feature with RDS SQL Server.

Related Solutions

MySQL – Keeping Staging Database Updated from Production on Same Server

I can see mysqldump being a little pain in the neck because of transaporting data out and then in.

Let's say you are copying data from proddb to stagedb. Here is a query to create the script for lateral copying of every table:

select
concat
(
    'drop table if exists ',db2,'.',tbl,'; ',
    'create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ',
    'insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';'
)
from (select table_schema db1,table_name tbl
from information_schema.tables where table_schema='stack_dev') tbls,
(select 'stack_test' db2) dbname;

Just generate the script as a SQL text file and import the text file to the target server:

PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" > /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql

This should work just fine for MyISAM. It should also work for InnoDB if there are no constraints. If there are constraints, you may have to disable them for the import session:

PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
echo "SET FOREIGN_KEY_CHECKS=0;" > /root/CopyFromProdToStage.sql
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" >> /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql

UPDATE 2012-03-09 17:14 EST

I have a stored procedure that will accomplish this. Run the following only once:

DROP DATABASE IF EXISTS utility;
CREATE DATABASE utility;
DELIMITER $$
DROP PROCEDURE IF EXISTS `utility`.`CopyDB` $$
CREATE PROCEDURE `utility`.`CopyDB` (sourceDB VARCHAR(64),targetDB VARCHAR(64))
TheStoredProcedure:BEGIN

    DECLARE found_count,ndx,ndx_last INT;
    DECLARE sqlcmd VARCHAR(1024);

    SELECT COUNT(1) INTO found_count
    FROM information_schema.tables
    WHERE table_schema = sourceDB;
    IF found_count = 0 THEN
        LEAVE TheStoredProcedure;
    END IF;

    DROP TABLE IF EXISTS DBTablesToCopy;
    CREATE TABLE DBTablesToCopy
    (
        id INT NOT NULL AUTO_INCREMENT,
        src  VARCHAR(64),
        tgt  VARCHAR(64),
        tbl  VARCHAR(64),
        PRIMARY KEY (id)
    ) ENGINE=MyISAM;
    DROP TABLE IF EXISTS SQLScriptToCopyTables;
    CREATE TABLE SQLScriptToCopyTables
    (
        id INT NOT NULL AUTO_INCREMENT,
        sqltext VARCHAR(1024),
        PRIMARY KEY (id)
    ) ENGINE=MyISAM;

    INSERT INTO DBTablesToCopy (src,tgt,tbl)
    SELECT sourceDB,targetDB,table_name
    FROM information_schema.tables
    WHERE table_schema = sourceDB;

    INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
    (CONCAT('DROP DATABASE IF EXISTS ',targetDB));
    INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
    (CONCAT('CREATE DATABASE ',targetDB));
    SELECT MAX(id) INTO ndx_last FROM DBTablesToCopy;
    SET ndx = 0;
    WHILE ndx < ndx_last DO
        SET ndx = ndx + 1;
        INSERT INTO SQLScriptToCopyTables (sqltext)
        SELECT CONCAT('CREATE TABLE ',tgt,'.',tbl,' LIKE ',src,'.',tbl)
        FROM DBTablesToCopy WHERE id = ndx;
    END WHILE;
    SET ndx = 0;
    WHILE ndx < ndx_last DO
        SET ndx = ndx + 1;
        INSERT INTO SQLScriptToCopyTables (sqltext)
        SELECT CONCAT('INSERT INTO ',tgt,'.',tbl,' SELECT * FROM ',src,'.',tbl)
        FROM DBTablesToCopy WHERE id = ndx;
    END WHILE;

    SELECT MAX(id) INTO ndx_last FROM SQLScriptToCopyTables;
    SET ndx = 0;
    WHILE ndx < ndx_last DO
        SET ndx = ndx + 1;
        SELECT sqltext INTO @stmt FROM SQLScriptToCopyTables WHERE id = ndx;
        PREPARE s1 FROM @stmt;
        EXECUTE s1;
        DEALLOCATE PREPARE s1;
    END WHILE;

END $$
DELIMITER ;

then anytime you feel like copying stack_dev to stack_test, do this:

call utility.copydb('stack_dev','stack_test');

and that's all.

Give it a Try !!!

Sql-server – Windows could not start the SQL Server service error

Open SQL Server configuration manager (start-->Run-->SQLServerManager10.msc) and try to start the service. If there is a problem starting the service, locate the SQL server Error log (in the SQL service configuration manager advanced tab there is a location or path of the error log) and post the output in the forum.

Best Answer

Related Solutions

MySQL – Keeping Staging Database Updated from Production on Same Server

UPDATE 2012-03-09 17:14 EST

Sql-server – Windows could not start the SQL Server service error

Related Question