CODEWITHSUNDEEP
sql-serverazure
user1679941
user1679941

Reputation:

How can I set up a remote connection to a SQL Server 2012 database in the Cloud?

I have connection strings that look like this after I added a few line feeds so it's easy to read:

        <connectionStrings>
            <add name="DefaultConnection" 
              connectionString="Data Source=xxx.database.windows.net;
              Initial Catalog=database2;
              Persist Security Info=True;
              User ID=xxx;
              Password=yyy" 
              providerName="System.Data.SqlClient"/>

I have two tables one in a local database and one in database2. The tables are in the dbo schema.

If the table in the remote and local databases are both called USERDATA then how can I move data from my local to remote database. I assume I need to make a remote connection but is that possible if the database is store in the cloud like this? If possible can someone tell me how I can set up this remote connection? I have SQL Server Management Studio so I can open a SQL Query window. I am just not sure how to specify the remote connection and if that is the best way to do it.

Upvotes: 0

Views: 415

Answers (2)

Igorek
Igorek

Reputation: 15861

There are multiple ways of handling this. Two simple ones of the top of my head are:

1) If you just need to transfer data for ONE table your best bet is to use a program called 'bcp' that comes with your SQL Server tools.

bcp allows you to export or import data (using in or out parameters) to a file and very quickly. You can simply use the bcp tool, export a table into a flat file, copy it over to the SQL Server in the cloud (using remote deskop or sharing the file in Azure storage or any other web-based storage) and then import the table again.

2) Alternatively, if you have access to SQL server in the cloud from your premise machine (ie: the firewall is open) and you're to run SSIS, you can connect both of the SQL servers within your SSIS package and transfer the data via SSIS

Upvotes: 0

Thiago Custodio
Thiago Custodio

Reputation: 18387

You can use like this:

INSERT INTO [database2].[dbo].[USERDATA]
SELECT tn.ID, tn.NAME
FROM [database].[dbo].[USERDATA] as tn

or you can use SSDT (Sql Server Data Tools) to migrate your schema and data to Sql Database on Azure.

Upvotes: 1

Related Questions