How To Use Linked Server

The Linked Servers option allows you to connect to another instance of SQL Server running on a different machine, perhaps remotely in a different city/country. This can be useful if you need to perform distributed queries (query a remote database). Setting up a linked server is quite straight forward in SSMS, all you need is details of the remote server, and the database that you need to query.

1. Navigate to Server Objects > Linked Servers
2. Right click on Linked Servers and select New Linked Server.... 

3. Complete the details for the linked server. In this example, Data source refers to the name of the SQL Server machine ("Barts_database_server"), Catalogue refers to the name of the database ("Barts_database"). You can also configure options in the other two tabs depending on your requirements.

Distributed Queries

Once you have configured your linked server, you will be able to run queries etc against it. When you run a query against a linked server, it is referred to as a distributed query.
When you execute a distributed query against a linked server, you must include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog.schema.object_name.
Here's an example:

Barts_database_server.Barts_database.Person.Enemy

This example is based on the linked server example above. It assumes that the remote database has a schema called "Person" and a table called "Enemy".

Difference between sql server 2005 and sql server 2008

The features that have ms sql server 2008 over 2005 is following.here we are going to discuss some points

1.  SQL server 2008 has the ability to encrypt the entire database by using (TDE)).I.e  transparent data encryption. Where as in sql server 2005 we have cell-level encryption.
2. SQLserver 2008 provides Backup encryption and that will executed at backup time to prevent tampering.
3. External Key Management. Storing Keys separate from the data.
4. With SQL Server Audit, SQL Server 2008 introduces an important new feature that provides a true auditing solution for enterprise customers. While SQL Trace can be used to satisfy many auditing needs, SQL Server Audit offers a number of attractive advantages that may help DBAs more easily achieve their goals such as meeting regulatory compliance requirements. These include the ability to provide centralized storage of audit logs and integration with System Center, as well as noticeably better performance.
5. Data Compression. Fact Table size reduction and improved performance.
6. Collection of performance monitoring tools.With Performance Data Collector in SQL Server 2008, you can now store performance data from a number of SQL Servers in one central location. This data is collected by a collection set on each server and stored in a shareable management data warehouse (MDW). Reports can be generated from this data using the built-in reports or generating your own with reporting Services. Brad McGehee explains more.
7. installation improvements. Disk images and service pack uninstall options.
8. Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.
9. Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)
10. LINQ. Development query language for access multiple types of data such as SQL and XML.
11. Data Synchronizing. Development of frequently disconnected applications.
12. Large UDT(User-defined types). No size restriction on UDT(User-defined types).UDTs were restricted to a maximum size of 8 kilobytes. In SQL Server 2008, this restriction has been removed for UDTs that have a format of UserDefined
13.  

Recovery Models In SQL Server

The first thing you  have to remember before back up of sql server data base is Recovery model..
The Recovery Model describes what data to keep in the transaction log file and for how long.By recovery model types you can select these options.
Basically we  have three types of recovery models
1)Full
2)Simple
3)Bulk-Logged
every data base has only one recovery model.but each data base use a different recovery models.that is you can select one database for simple recovery model and another data base for full recovery model and so on..
The selections is made up of the type of data base and backup needs.The only exception to this is the TempDB database which has to use the "Simple" recovery model.