Sunday, April 25, 2010

Setting SQL Servers to be able to Communicate Each Other

Followings are the basic steps if you want to write any stored procedures or DML(Data Manipulation Language) in an SQL servers that accesses remote SQL servers:

1. Add Linked Servers
To make a server be able to communicate with other servers we need to add linked servers by calling sp_addlinkedserver.
The syntax is as follow:
EXEC sp_addlinkedserver , N'SQL Server'

2. Setting Distributed Transaction Coordinator
Verify the "Distributed Transaction Coordinator" Service is running on both servers:
1. Go to "Administrative Tools > Services
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running, in both servers:
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers > My Computer"
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client", "Allow Inbound/Outbound" with ‘No Authentication Required’, "Enable TIP" and ‘Enable XA Transactions’
7. The service will restart
8. It might be needed to reboot server if there is issue on executing the SP.

3. Adding Remote Instance Name to the Host
In Windows Explorer, go to ‘C:\Windows\system32\drivers\etc’, open ‘hosts’ file. In bottom part add IP and remote instance names by typing:
[Tabkey]

By doing all above steps, you will be able to execute SP like in below sample where a server in Head Office accesses server in remote sites and collect the data back to Head Office (at least in my network it works):

CREATE PROCEDURE [dbo].[HSAP_SP_UPL_Test]
AS
BEGIN

DECLARE @ServerIP AS varchar(100)
SET @ServerIP = '[192.168.5.1].[MataPao].[dbo].[HSAP_SP_RPT_ItemClassSummary]'

DECLARE @hsap_EstateClassSummary Table (
MatGroup nvarchar(5),
MatDesc nvarchar(50),
Value decimal (18,0)
)

INSERT @hsap_EstateClassSummary
EXEC @ServerIP
@FromDate = '2009-11-01',
@ToDate = '2009-11-30'

SELECT * FROM @hsap_EstateClassSummary
END

Friday, February 26, 2010

How to Limit Folder Size (Windows 2003 Server)

You have shared folders in a server that you don't want users to abuse the usage of them by storing huge files like mp3, jpeg, etc that will end up with a hang server because of low disk space. Don't worry, we can limit folder size in Windows 2003 Server R2 Standard and Enterprise.
We need a tool called FSRM (File Server Resource Manager). This Microsoft tool will allow you to set folder size quotas amongst other file server related controls, and has options for alerting and reporting too.

FSRM was introduced with Server 2003 SP2, but not installed by default. How to install:
1. Start>Control Panel>Add or Remove Program
2. Click Add/Remove Windows Program
3. Select Management and Monitoring Tools. Click Details.
4. Click File Server Management and File Server Resource Manager. On installing it we need to copy files from the original CD.

How to set the quota:
1. Start>Administrative Tools>File Server Resource Manager
2. Under Quota Management>Quota right click and select Create Quota.
3. Define the path that is going to be limited and define the quota size.

That's all folks!
Naughty users will get message that no more space if the files in corresponding folder has exceeded the quota.