DATABASE

ByDr. SubraMANI Paramasivam

How to create a Linked Server?

This article will help you create your a new linked server from one instance to another instance.

1. Connect to the 1st instance of SQL Server.

2. Under “Server Objects” look for “Linked Servers” and right click on it and select “New Linked Server”. A new popup box will appear like the below one.

 

3. In the linked server text box, type the second instance name.

4. In the server type choose Other data source and then select SQL Server Native Client 10.0.

5. In the product name and data source key in the same instance name that was entered in point 3 above. The entry in point 3 is to make sure that we know what we are connecting to.

6. In the catalog key in the specific database name that you want to connect to.

7. In the security tab, click “Be made using this security context” option and key in a remote login & password that already has access to the second instance. If not available then create a new one and put that information here.

8. Thats it, you are now done creating Linked Server.

If you have any issue in creating a linked server then you may not have enough permission on the instance. Even if you have sysadmin access and have problem creating it then get in touch with me on info@yoursqlman.com for further information.

All the best!

ByDr. SubraMANI Paramasivam

How to browse the database objects through Linked Servers

Without actually connecting to another instance of SQL server, is it possible to browse the database objects like tables & views? The answer is YES. How do I do that? Well, for a working scenario we need at least 2 SQL instances either on same server or on a different servers. If you have already not created the Linked server, click here to see this article and then do the below steps.

1. Connect to the 1st instance of SQL Server in Management Studio.

2. Browse through the folders to see “Server Objects” and look for “Linked Servers”. Click the plus sign on it to expand.

3. Now click on the plus (+) sign that is already configured and choose the name of the linked server and then expand Catalogs.

4. Here you can see the number of databases and expand it.

5. You will only see Tables & Views and not the stored procedures or any other objects.

6. You can right click on any table and select “Script Table as” and click on “Select to” => New Query window.

7. You can see a query automatically created pointing to the table in a 4 point convention (…).

Note: Writing any linked server queries for production usage will obviously have performance problems. If the SLA is agreeable then you can proceed or you need to take extra measure. If you have any issue in tackling these type of issues, do get in touch with info@yoursqlman.com for further information.

 

ByDr. SubraMANI Paramasivam

Instances in SQL Server

Instance is nothing but SQL Server Installation. In SQL Server 7.0 only one installation was allowed and this changed from 2000 Version onwards. Default instance is nothing but connecting to SQL Server using the computer name. Where as the named instance such as Mani will have computername\mani. For example if the computer name is MyServer, then the named instance would be MyServer\Mani. Now I can install many number of named instances and the maximum is based on the edition that you use. The list is provided below for each edition.

The port used by default instance is always 1433 and named instances use dynamic allocation.

The total number of instances including the default instance of SQL Server supported by all editions differ. The following gives a clear idea of the supported instances for each editions.

Enterprise – 50

Developer – 50

Standard – 50

Workgroup – 16

Express – 16

Note: Windows Failover Cluster supports only 25 instances.

ByDr. SubraMANI Paramasivam

Add a new column to every table in a database

This seems quite a painful job when so many start opening a table and add the new column name, datatype and save. But what about you have to do this for 200 tables in a database and say you have 10 databases. Hmmm…. Such a crap work doing manually but SQL Server infamiliar stored procedure comes to rescue at this point.

The below query can be used to add a new column to every single table in a database.

Use [db]
go
exec sp_MsForEachTable ‘alter table ? add Load_date datetime null’

Thats it. You are done. Change the database name for the other databases.

So it is not just adding a column to all table, but modifying anything similar for each table in a database. You can change the SQL statement within the single quotes.

Best of luck

ByDr. SubraMANI Paramasivam

SQL Server Memory limitations

There are totally 6 editions available in SQL Server and 4 Windows Server with 32 & 64 bit for each in 2008 and 3 for 2003 R2 SP2.

12