Connecting Remote server with Local Server

Introduction:

Linked server is powerful functionality provided by SQL Server. We can create object of remote server on local server and use that object to perform operations on remote server.

To explain this topic let's consider a scenario

Scenario:  Local server having number of databases. We need to take backup of local databases and restored this database into the remote server.

For this scenario we will follow certain steps:

Step 1 : Create object of Remote Server

Add remote server object in local server

Syntax:

    sp_addlinkedserver @server = N'LinkServer',

    @srvproduct = N' ',

    @provider = N'SQLNCLI',

    @datasrc = N'Server Name,

    @catalog = N'Database Name'

Here we have created object of remote server i.e. LinkServer. By using this object you can perform Select, Update functionality on the same.

Step 2 : Grant Permission

Next Step add permission to user on remote server.

• ·         On Remote Server Click on Security -> Logins
• ·         Right Click -> New Login Create User

It will create your user login on remote server. Refer below screen shot for new user login

• ·         Now Right Click on Server -> Property

• ·         Click Permission Tab and select user login which we have previously created.

·         Grant permission to selected user to created database.

Now you are ready to fire your queries on remote database using your Local Server.

  

Step 3:  Re-enable the RCP commands:

After this step 2, I am trying to run my Create database command on Remote Server object but I have faced one issue i.e..

            Server ‘SERVERNAME1′ is not configured for RPC.

This means that when I set up my linked server, I disable RPC commands to and from running for security reasons.

To re-enable the RCP commands for the linked server I have Executed some system stored procedure on  remote server

Syntax:

 exec sp_serveroption @server='LinkServer', @optname='rpc', @optvalue='true'

 exec sp_serveroption @server='LinkServer', @optname='rpc out', @optvalue='true'

Step 4: Execute Final query:

Finally after following these many step I can run my SQL Statements on remote server.

Below is the syntax to run SQL statement on remote server.

Syntax:

  DECLARE @statement AS NVARCHAR(1000)

  SET @statement ='Create DATABASE Database Name'

  EXEC LinkServer.master.dbo.sp_executesql @statement

For our scenario you need to first get list of database form local server on temp table server.

·       Get Local Database name list:

Syntax:

SELECT name FROM Sys.Databases

·         Add database list in to temp table:

      Declare @Temp as table (Name Varchar(max))

      Insert into @Temp

      SELECT name FROM Sys.Databases

select * from @Temp

Above query will return the name of databases on local server in @temp Table.

·         Declare cursor to take backup of databases:

Create Folder for backup database and share it to remote server machine.

Below is the syntax to save backup of local machine databases on shared location folder.

Syntax:

            Declare @Name as varchar(max), @SQLQuery as varchar(max)

     Declare DatabaseList cursor for

     Select Name from @Temp

     Open DatabaseList

     fetch next from DatabaseList into @Name

     While @@FETCH_STATUS=0

     BEGIN

     SET @SQLQuery= 'USE master;

     BACKUP DATABASE @Name

     TO DISK = ''''C:\ShareFolderName\'''+@Name+'''.BAK''''

     GO'

     Exec @SQLQuery

     FETCH NEXT FROM DatabaseList INTO @Name

     END

  

·         Restore database on remote machine:

Now we have backup files of  local server databases on shared folder. You can use below cursor syntax to restore this backup file on remote server.

      Syntax:

          Declare @Name as varchar(max)

          Declare DatabaseList cursor for

          Select Name from @Temp

          Declare @SQLQuery as varchar(max)

          Open DatabaseList

          fetch next from DatabaseList into @Name

          While @@FETCH_STATUS=0

          BEGIN

          DECLARE @Statement as nvarchar(1000)

          SET @Statement ='RESTORE DATABASE '+@Name+' FROM          DISK = N''\\remotemachine\ShareFolderName\'+@Name+'.bak'''

          EXEC LinkServer.master.dbo.sp_executesql @Statement

          Exec @SQLQuery

          FETCH NEXT FROM DatabaseList INTO @Name

          END

Note:

You can add SSAS Server instance into the local database server as we have added a database server earlier and use this as per your requirement. Below is the syntax to add SSAS Server instance: 

Syntax:

    sp_addlinkedserver @server = N'LinkServer',

    @srvproduct = N' ',

    @provider = N'MSOLAP',

    @datasrc = N'Server Name,

    @catalog = N'Cube Name

By using OPENQUERY we can run SSAS Syntax query on Database server.

Syntax:

SELECT * FROM OPENQUERY

(LinkServer,

'Select Measures1, Measures2 ON 0, Attributes on 1 from CubeName');

Step 5:- Disable RPC

After your operations are done on remote server please remember to disable RPC as a security caution:

Syntax:

 EXEC sp_serveroption @server='LinkServer', @optname='rpc',     @optvalue='false'

 EXEC sp_serveroption @server='LinkServer', @optname='rpc out', @optvalue='false'