Database : Create Linked Server for Remote SQL Server

if we have two servers on different pc's on a LAN, say server1 and server2. if we have to get data of server 1 on server2 we have to create a linked server on server2.

First we'll create a remote login on server1 then a linked server on server2.

For remote login on server1

1. Open sql server1 and expand security tab.
2. Right click on Logins and select Newlogin.
3. In new window enter name of login and select SQL server authentication wrote password and uncheck UER MUST CHANGE PASSWORD option and click ok
   
   
   
4. Now Expand your database and then expand security tab. Now right click on Users and select add new user.
   
   
   
5. Now click on ok button. You remote login created. Dont forget to turn off firewall.

Crete linked server2

1. Open SQL Server2 and expand server objects.
2. Right click on Linked server and select new .a new window will appear.
3. Enter name of you server1 in linked server box. here our server name is server1
   
   
   
4. Now click on security tab and click on add button below box. In locallogin option in box enter your local server name here our local server is SERVER2 . In Remote user option enter Login name we created in Server1. Here we created it with name aims and in next box write password of that user.
   
   
   
5. In Server Option Tab change RPC and RPC OUt to TRUE.
6. Click on ok

Database : Return All Tables Name from a SQL Server Data Base

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

Database : Tracking Change Data Capture in SQL Server 2008

A very important feature of SQL Server 2008 is that we can enable CDC [Change Data capture] on database or table. 

We can track the database had CDC enabled by querying IS_CDC_ENABLED column 

  
In above query School is name of the database. 

If we want to track tables in database whether CDC enabled or not then 


 
Above query will list name of all tables in School database CDC enabled.

Database : SQL Server Query for Enabling Change Data Capture On All Tables

Change Data Capture (CDC) isan auditing feature provided by SQL Server 2008 and above and used to audit/track all changes done on a table such as inserts, updates, and deletes. We can enable CDC on the database using exec sys.sp_cdc_enable_db then we need to enable the same on each table manually using sys.sp_cdc_enable_table with table name as parameter. Instead of that, we can use the following SP to enable/disable CDC at the table level for all tables in a database:

1. create procedure sp_enable_disable_cdc_all_tables(@dbname varchar(100), @enable bit)  
2. as  
3.   
4. BEGIN TRY  
5. DECLARE @source_name varchar(400);  
6. declare @sql varchar(1000)  
7. DECLARE the_cursor CURSOR FAST_FORWARD FOR  
8. SELECT table_name  
9. FROM INFORMATION_SCHEMA.TABLES where TABLE_CATALOG=@dbname and table_schema='dbo' and table_name != 'systranschemas'  
10. OPEN the_cursor  
11. FETCH NEXT FROM the_cursor INTO @source_name  
12.   
13. WHILE @@FETCH_STATUS = 0  
14. BEGIN  
15. if @enable = 1  
16.   
17. set @sql =' Use '+ @dbname+ ';EXEC sys.sp_cdc_enable_table  
18.             @source_schema = N''dbo'',@source_name = '+@source_name+'  
19.           , @role_name = N'''+'dbo'+''''  
20.             
21. else  
22. set @sql =' Use '+ @dbname+ ';EXEC sys.sp_cdc_disable_table  
23.             @source_schema = N''dbo'',@source_name = '+@source_name+',  @capture_instance =''all'''  
24. exec(@sql)  
25.   
26.   
27.   FETCH NEXT FROM the_cursor INTO @source_name  
28.   
29. END  
30.   
31. CLOSE the_cursor  
32. DEALLOCATE the_cursor  
33.   
34.       
35. SELECT 'Successful'  
36. END TRY  
37. BEGIN CATCH  
38. CLOSE the_cursor  
39. DEALLOCATE the_cursor  
40.   
41.     SELECT   
42.         ERROR_NUMBER() AS ErrorNumber  
43.         ,ERROR_MESSAGE() AS ErrorMessage;  
44. END CATCH  

This SP takes db name and flags enable/disable as inputs and loops through each table name from INFORMATION_SCHEMA.TABLES using the cursor and calling dynamic SQL with command sys.sp_cdc_enable_table in it. 

 

 We can re-usethe same SP for doing any operation on every table in a database.

Database : Major System Databases of SQL Server

Master Database:

1. It is a system database which contains server’s configuration.
2. Used for Backup for master database.
3. SQL Server can't be started without it.

Msdb Database: Stores information regarding,

• Database backups.
• Agent information,
• DTS packages,
• SQL Server jobs,
• Replication information such as for log shipping.

Temp Database:

1. Temp Database holds temporary objects such as global temporary tables, local temporary tables and temporary stored procedure.
2. It stores version information.
3. This is a temporary database used to store temporary, tables, cursors, indexes, variables etc.
4. Temp Database is re-created every time SQL Server is started.
5. Auto shrink is not allowed for temp Database.

Model database 
It is a Template database used in the creation of a new database

Resoure Database

1. Resource Database is a read-only database.
2. It contains all the system objects that are included with SQL Server.
3. The Resource database does not contain user data or user metadata.

Other Related Database of Sql Server Distribution, ReportServer and ReportServerTempDB
Distribution: Used for SQL Server replication only.

ReportServer: To store metadata and other object definitions.

ReportServerTempDB: Acts as a temporary storage for reporting services.