DBA Sensation

January 31, 2013

SQL 2008 Active-active cluster on windows 2008 R2 Share storage migration

Filed under: 3. MS SQL Server — Tags: , , , , , — zhefeng @ 11:16 am

This is my POC doc before we migrate our production sql 2008 cluster to new storage. it has two parts: first part simply install the cluster on 2 ESX VM images by using vmfs shared disks. Second part is the storage migration part.

====Part 1: build sql 2008 cluster

IP Address mapping:
10.165.36.78 vmsql2008cls01
10.165.36.79 vmsql2008cls02
10.165.36.80 vmsql2008cls
10.165.36.81 vmsql2k8clsvip1
10.165.36.82 vmsql2k8clsvip2
10.165.36.83 vmsql2008clsdtc

Initializing the LUN and labelling them:
G: OLTP (OLTP data volume)
H: OLAP (OLAP data volume)
I: Quorum (Quorum Disk)
R: msdts

1. enable “application server” roles in OS “Server Manager”
check “.NET Framework 3.5.1”, “Distributed Transactions – Incoming Remote Transactions”, “Distributed Transactions – Outgoing Remote Transactions”

2. Add “failover clustering” feature

3. setup cluster in cluster manager, cluster IP/name: vmsql2008cls 10.165.36.80

4. setup clustered MSDTC service
Open Cluster manaager, In “services and applications” add “DTC” service, use name “vmsql2008clsdtc”, ip “10.165.36.83”

5. Install first instance. Run under command prompt: Setup.exe /ACTION=PrepareFailoverCluster (note: you have to use this setup option and run it on BOTH nodes)

1). pickup instance features and shared features (except reporting services)

2). Default instance id: MSSQLSERVER

3). Cluster Security Policy – “Use service SIDs”

4). Server Configuration – use the same account for all sql server services “pgdev\sevice.pg.prod”, keep all the default startup type (especially for dtabase engine “manual”)

6. Run “setup” to finish cluster on ONLY active node (owns the storage)
Run under command prompt: setup.exe /ACTION=CompleteFailoverCluster
1). SQL Server Network Name for first instance: vmsql2k8clsvip1
2). pickup “OLTP” disk
3). ip address for virtual: 10.165.36.81
4). done with installation. Varify it in cluster admin

7. Install 2nd instance.
1). run Setup.exe /ACTION=PrepareFailoverCluster on BOTH nodes
2). run setup.exe /ACTION=CompleteFailoverCluster on ONLY active node
3). virtual IP and network name:
10.165.36.82 vmsql2k8clsvip2
4). pick up “OLAP” disk
5). done with installation

====Part 2: migrating from old LUNs to new LUNs

8. Add new disks on Node1 (V, W, X, Y) and plan their mapping drives:
V -> G
W -> H
X -> I
Y -> R

9. in cluster manager, “add disk” to add all new disks to storage catalog

10. move the new disks to corresponding service or applications except Quorum disk (right click on disk, “more actions” -> “move this resource to another service or application”)

11. Stop your clustered application(s) (the virtual SQL instance in this case) and copy data from old drives to new drives (include msdtc drive)
1). In Failover Cluster Management, take all SQL instances and MSDTC service offline. This will stop SQL services and release any open handles on the SQL data files so we can copy the data to the new drive as below order:
G -> V
H -> W
R -> Y

2). When the data copy is complete, change the drive letter of an existing data drive to a temporary letter. Set the drive letter of the new drive to the original drive letter of your original storage. (If the original data drive was G:, the new drive should be G: or SQL will not be able to find its files and thus will not start).
So set old ones to temp drive letter first:
G -> O
H -> P
R -> Q

12. Then Map new drives to old drive letters:
V -> G
W -> H
Y -> R

13. change the dependency for each application and service. For example, for first instance ‘sql server’, right click “properties” -> dependencies -> change to “cluster disk 5” (the new disk)

14. delete MSDTC service and recreate
1). delete the resource “MSDTC” in dtc service catalog
2). add resource “MSDTC” back to catalog
right click on dtc sevice icon on the left -> add a resource > more resource -> “2- Add Distribute Transaction Coordinator”
3). add dependency for the new dtc service
right click on “microsoft Distributed Transaction Coordinator” -> ” properties” -> “Dependencies” -> add virtual server name And new cluster disk as depenndencies for this service.
4). bring it online.

15. Move quorum
1). right click on the cluster name under “failover cluster manager” -> “more actions” -> “configure cluster quorum settings”
2). choose default Quorum configuration ” Node and disk Majority”
3). check new cluster disk for “storage witness”,
4). confirm and done with reconfigure
5). no need to change the drive letter since it’s quorum, so the new quorum is “X”

16. bring all old cluster disk offline
that’s: O, P, Q, I

17. bring all clustered application(s) online

18. Verify services and applications are online and functional, and failover works

19. delete old disks in cluster manager, delete partitions as well.

Reference:
1. How to Move a Windows 2008 Cluster to New SAN Storage
http://www.systemcentercentral.com/BlogDetails/tabid/143/IndexID/54853/Default.aspx

August 28, 2012

attached database with single MDF file

Filed under: 3. MS SQL Server — Tags: , , — zhefeng @ 12:15 pm

i need to attach some database and don’t want to copy over the log files, here is how:
EXEC sp_attach_single_file_db @dbname=’TestDb’,
@physname=N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf’

After run it, sql server will make a log file for you.

January 4, 2012

How to configure resource governor in sql 2008 to seperate the classified work load

Filed under: 3. MS SQL Server — Tags: , , , — zhefeng @ 5:30 pm

On our server some big apps always eat up all resource which cause other apps get hang as well.
Try to seperate the traffic between big apps and normal apps on a shared sql instance by implementing resource governor.

Here is the plan for pools. pBigApp takes maxium 60% resource and other apps by default using default pool.

Pool_name MIN% MAX% Calculated_Effective_Max% Calculated_Shared% Comment
internal 0 100 100 0 not applicable to internal pool
default 0 100 100 100 Calculated_Effective_Max%= min(MAX%,100-sum(min%)), calculated_shared%= Calculated_Effective_Max% – Min%
pBigapp 0 60 60 60

Configuration:
1. make sure the resource governor is enabled
select is_enabled from sys.resource_governor_configuration
–if it returns “0”, then you need to enable it
ALTER RESOURCE GOVERNOR RECONFIGURE;

2. Issue a CREATE RESOURCE POOL statement to create a resource pool
USE master;
— Create a resource pool “pBigApp” that sets the MAX_CPU_PERCENT to 60%.
CREATE RESOURCE POOL pBigApp WITH (MAX_CPU_PERCENT = 60);
GO

3. Create a workload group to use new pool “pBigApp”
CREATE WORKLOAD GROUP gASTEC USING pBigApp;
GO

4. Create a classifier function that maps the workload group created in the preceding step to the user of the low-priority login
–Note that any request that does not get classified goes into the ‘Default’ group.
USE master;
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @workload_group_name AS sysname
IF (SUSER_NAME() = ‘ASTEC’)
SET @workload_group_name = ‘gASTEC’
RETURN @workload_group_name
END;
GO

–another function example with application name classfied
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name sysname
IF (SUSER_NAME() = ‘sa’)
SET @grp_name = ‘GroupAdmin’
IF (APP_NAME() LIKE ‘%MANAGEMENT STUDIO%’)
OR (APP_NAME() LIKE ‘%QUERY ANALYZER%’)
SET @grp_name = ‘GroupAdhoc’
IF (APP_NAME() LIKE ‘%REPORT SERVER%’)
SET @grp_name = ‘GroupReports’
RETURN @grp_name
END;
GO

5. Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);

6. Verify the classification of specific sessions
log in as the user that you specified in your classifier function, and verify the session classification by issuing the following SELECT statement
USE master;
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name
FROM sys.dm_exec_sessions AS sess
JOIN sys.dm_resource_governor_workload_groups AS grps
ON sess.group_id = grps.group_id
WHERE session_id > 50;
GO

Useful scripts:
1. what workload group and resource pool in Resource Governor was assigned to each session
SELECT session_id as ‘Session ID’,
[host_name] as ‘Host Name’,
[program_name] as ‘Program Name’,
nt_user_name as ‘User Name’,
SDRGWG.[Name] as ‘Group Assigned’,
DRGRP.[name] as ‘Pool Assigned’
FROM sys.dm_exec_sessions SDES
INNER JOIN sys.dm_resource_governor_workload_groups SDRGWG
ON SDES.group_id = SDRGWG.group_id
INNER JOIN sys.dm_resource_governor_resource_pools DRGRP
ON SDRGWG.pool_id = DRGRP.pool_id

2. Assigns all new sessions to the default workload group by removing any existing classifier function from the Resource Governor configuration.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

3.Example of store the classifier function in the master database.
USE master;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
— Declare the variable to hold the value returned in sysname.
DECLARE @grp_name AS sysname
— If the user login is ‘sa’, map the connection to the groupAdmin
— workload group.
IF (SUSER_NAME() = ‘sa’)
SET @grp_name = ‘groupAdmin’
— Use application information to map the connection to the groupAdhoc
— workload group.
ELSE IF (APP_NAME() LIKE ‘%MANAGEMENT STUDIO%’)
OR (APP_NAME() LIKE ‘%QUERY ANALYZER%’)
SET @grp_name = ‘groupAdhoc’
— If the application is for reporting, map the connection to
— the groupReports workload group.
ELSE IF (APP_NAME() LIKE ‘%REPORT SERVER%’)
SET @grp_name = ‘groupReports’
— If the connection does not map to any of the previous groups,
— put the connection into the default workload group.
ELSE
SET @grp_name = ‘default’
RETURN @grp_name
END
GO
— Register the classifier user-defined function and update the
— the in-memory configuration.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.rgclassifier_v1);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

4. system views and dm views
sys.resource_governor_configuration: Returns the stored Resource Governor state.
sys.resource_governor_resource_pools: Returns the stored resource pool configuration. Each row of the view determines the configuration of a pool.
sys.resource_governor_workload_groups: Returns the stored workload group configuration.

sys.dm_resource_governor_workload_groups: Returns workload group statistics and the current in-memory configuration of the workload group.
sys.dm_resource_governor_resource_pools: Returns information about the current resource pool state, the current configuration of resource pools, and resource pool statistics.
sys.dm_resource_governor_configuration: Returns a row that contains the current in-memory configuration state for Resource Governor.

Reference:
1. Managing SQL Server Workloads with Resource Governor
http://msdn.microsoft.com/en-us/library/bb933866.aspx

2. Part 1: Anatomy of SQL Server 2008 Resource Governor CPU Demo
http://blogs.technet.com/b/sqlos/archive/2007/12/14/part-1-anatomy-of-sql-server-2008-resource-governor-cpu-demo.aspx

3. Part 2: Resource Governor CPU Demo on multiple CPUs
http://blogs.technet.com/b/sqlos/archive/2008/01/18/part-2-resource-governor-cpu-demo-on-multiple-cpus.aspx

4. How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)
http://msdn.microsoft.com/en-us/library/cc280384.aspx

5. Resource Governor DDL and System Views
http://msdn.microsoft.com/en-us/library/bb895339.aspx

March 17, 2010

How to migrate sql cluster between domains

Filed under: 3. MS SQL Server — Tags: , , , — zhefeng @ 4:54 pm

Migrating SQL Server 2005/2000 cluster from one domain to the other domain is not supported by Microsoft. However, here is the way how to do that.
i used this way to migrate our production cluster sucessfully.

1. Current settings (active – passive mode)
Cluster name: clustest
Cluster management IP: 10.0.0.142
Virtual server Name: clustestvip1.test.com
VIP: 10.0.0.144
Service account: test\service.product
Cluster group: test\sqlcluster
Node1:
Name –dbnode1.test.com
Physical IP: 10.0.0.140

Node2:
Name – dbnode2.test.com
Physical IP: 10.0.0.204

2. Objective
(1). To change the domain from test.com to test1.com
(2). To change the service account from test\service.acc to test1\service.act
(3). Make sure the sql cluster is still functional after changing

3. Testing steps
(1). Take all cluster resources offline (except the quorum, which cannot be taken offline)
(2). Stop the cluster service on both nodes and change startup type to “manual”
(3). Change the domain of each machine to the new domain and reboot
(4). After reboot, on each machine, change the cluster and SQL service accounts to accounts in the new domain (test\service.acc ->test1\service.act).
Note: according to MSKB 915846 it should be “On one of the cluster nodes, use SQL Server Configuration Manager to change the service account to the new account.”
That’s not true, and you got error from there, have to do it on both nodes in services.msc.
(5). Add the cluster and SQL service accounts to the local Adminstrators group.
(6). Run gpedit.msc to grant the following rights to the new service account:
Path: “Local Computer Policy” -> “Windows Settings” -> “Security Settings” -> “Local Policies” ->”User Rights Assignment”
*Act as part of the operating system
*Adjust memory quotas for a process
*Back up files and directories.
*Debug programs
*Increase scheduling priority
*Increase quotas (not in windows 2003).
*Load and unload device drivers.
*Lock pages in memory
*Log on as a batch job
*Log on as a service
*Manage auditing and security log
*Replace a process level token
*Restore files and directories
7). Update the domain groups SID for cluster services (sql 2000 skip this step)
There are domain groups related to old domain name as below which couldn’t be changed in AD(you can’t find them! They only appear in register as SID at this branch: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup).
So what we need to do is just find the old group sid in registry and replace it with new group sid. Here are the steps:
a. find old cluster group sid by logging in as old service account, then run “whoami /groups” in dos prompt
b. in this case, the service account is service.acc, and cluster group is : test\sqlcluster
c. find the sid for test\sqlcluster is:
S-1-5-21-1679026800-3574736516-1101542067-14559
d. same way to find the new group sid for test1\sqlcluster is:
S-1-5-21-2789037367-2132044359-1364708090-24619
e: replace the old sid with new sid under branch (do a small search there) : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\
8). Reboot both nodes
9). Start the cluster service on both machines (put them back to “automatic” mode again)
10). Bring cluster resources online

Reference:
1. Changing the Domain for a SQL 2005 Cluster
http://nyc-dba.blogspot.com/2007/01/changing-domain-for-sql-2005-cluster.html

2. How to move a Windows Server cluster from one domain to another
http://support.microsoft.com/kb/269196

3. Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster
http://support.microsoft.com/kb/915846

October 20, 2009

SQL Server 2008 Cluster installation on Windows 2008 Enterprise Server

Filed under: 3. MS SQL Server — zhefeng @ 12:41 pm

SQL Server 2008 Cluster installation on Windows 2008 Enterprise Server

1. Windows 2008 cluster ring installation

When you are trying to create windows 2008 cluster ring, you will get error like this:
“An error occurred while creating the cluster. An error occurred creating cluster ‘sql08clus’. The network location cannot be reached.”

It looks like 2008 Cluster requires a system share for it to be created. However, by default windows 2008 server system share was disabled by a registry key. In order to enabling system share, you have to modify this key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\
“AutoShareServer”=dword:00000000
To:
“AutoShareServer”=dword:00000001
After that the cluster can be created successfully.

Cluster ring configurations:

Cluster Name: sql08clus IP address: 10.0.0.132
Node1: sqlc08n1 IP address: 10.0.0.133
Node2: sqlc08n2 IP address: 10.0.0.134
MSDTC: sql08cldtc IP address: 10.0.0.135

2. SQL Server 2008 cluster installation
The SQL Server 2008 cluster installation is quite different than earlier versions. It doesn’t do the installation for the remote nodes. (See more at Ref. 1)
There are two installation options:
* Integrated failover cluster install with Add Node
SQL Server integrated failover cluster installation consists of two steps:
1. Create and configure a single-node SQL Server failover cluster instance. At the completion of a successful configuration of the node, you have a fully functional failover cluster instance. At this time it does not have high-availability because there is only one node in the failover cluster.
2. On rest nodes (NOT ON FIRST ONE) to be added to the SQL Server failover cluster, run Setup with Add Node functionality to add that node.

* Advanced/Enterprise installation
SQL Server Advanced/Enterprise failover cluster installation consists of two steps:
1. On each node that will be part of the SQL Server failover cluster, run Setup with Prepare Failover Cluster functionality. This step prepares the nodes ready to be clustered, but there is no operational SQL Server instance at the end of this step.
2. After the nodes are prepared for clustering, run Setup on the node that owns the shared disk with the Complete Failover Cluster functionality. This step configures and completes the failover cluster instance. At the end of this step, you will have an operational SQL Server failover cluster instance.

In this case we used option 1 – “integrated failover cluster install with add node”

Step1: usually we start installation by run “setup” to bring the GUI interface. However, sometimes a fully functional cluster ring couldn’t pass the sql server 2008 validation rule which will block the installation. In our case it’s blocked, so we have to skip this validation rule to let installation carry on (see more at ref.6):
Here are some commands to bypass the rule check:
* For an integrated failover setup, run the following command on each node that is being added:
Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster
* For adding node to integrated installation instance
Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode
* For an advanced or enterprise installation, run the following command:
Setup /SkipRules=Cluster_VerifyForErrors /Action=CompleteFailoverCluster
So the first step here we run (c:\temp is my installable location):
C:\temp\Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster
We use the configuration as below:

Cluster mode: Active – Passive two nodes cluster
SQL Network name: sqlc2k8vip IP address: 10.0.0.136
Service Account: test\service.acc
Cluster domain group: test\sqlcluster

Step 2: According to the integrated failover cluster installation method, we have to run add node(by GUI/Command line) on second node to add this node into cluster. Because of the validation problem we run this here on node 2:
C:\temp\Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode
However, Microsoft gave us an error again:
“The current SKU is invalid”
This is actually a installation bug (see more in ref. 7), so we download the hotfix (SQLServer2008-KB973601-x86), patched first node and second node(maybe not necessary). The patch apparently will failed on first node, but it’s ok you can bring the services up manually in cluster admin GUI.
After patching the installation worked well.

Step3: testing failover between two nodes
No problem at all

Reference:
1. “Getting Started with SQL Server 2008 Failover Clustering”
http://msdn.microsoft.com/en-us/library/ms189134.aspx

2. “Setting up a Two-node SQL Server 2008 Cluster from the Command Prompt – Preparation”
http://www.databasejournal.com/features/mssql/article.php/3771296/Setting-up-a-Two-node-SQL-Server-2008-Cluster-from-the-Command-Prompt—Preparation.htm

3. “Setting up a Two-NODE SQL Server 2008 Cluster from the Command Prompt – Integrated Installation”
http://www.databasejournal.com/features/mssql/article.php/3772826/Setting-up-a-Two-NODE-SQL-Server-2008-Cluster-from-the-Command-Prompt—Integrated-Installation.htm

4. “Installing a Two-node SQL Server 2008 Cluster – Advanced option”
http://www.databasejournal.com/features/mssql/article.php/3783326/Installing-a-Two-node-SQL-Server-2008-Cluster—Advanced-option.htm

5. “Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 1-4”
http://www.mssqltips.com/tip.asp?tip=1687

6. “Cluster validation rule of the setup process failure workaround”
http://support.microsoft.com/kb/953748

7. “Error message when you try to add a second node to a SQL Server 2008 failover cluster: “The current SKU is invalid””
http://support.microsoft.com/kb/957459/

October 9, 2009

enable a disabled sql agent service

Filed under: 3. MS SQL Server — Tags: , , , — zhefeng @ 10:03 am

When SQL Server 2005 Management Studio’s Object Browser shows the SQL Server Agent service with a red down arrow and the text Agent XP’s disabled, the service is not started or disabled.
i had this issue on my sql 2005 active-active cluster even with sql agent resource started in my cluster admin interface, the agent actually was not running.

To fix this, you need to open the sql server advance configuration and enable sql agent, then close the advance configuration option again:
–step 1. open advanced configuration option
exec sys.sp_configure @configname = N’show advanced options’, @configvalue = 1
reconfigure with override

–step 2. change value from “0” to “1” which means enable
exec sys.sp_configure @configname = N’Agent XPs’, @configvalue = 1
reconfigure with override

–step 3. close advanced configuration option
exec sys.sp_configure @configname = N’show advanced options’, @configvalue = 0
reconfigure with override

September 24, 2009

table usages for ms sql database

Filed under: 3. MS SQL Server — Tags: , , , , , — zhefeng @ 3:20 pm

This STP help us find usage for any objects (includes table)
sp_spaceused [object_name]

By combining other things you got full table usage list from db
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”

–or

select ‘exec sp_spaceused ‘+name from sys.tables order by name;

July 29, 2009

MS SQL Server 2005 alias doesn’t work on 64bit platform

Filed under: 3. MS SQL Server — Tags: , , , , , — zhefeng @ 10:04 am

we recently upgraded our 1 cluster from 32bit platform to 64bit platform.
In order to launch the replication monitor we have to use sql alias name which has been set up on “sql server configuration Manager” -> “sql native client configuration ” -> “alias”.

for 32bit sql client, there is only one option to set up alias. so from 32bit client to connect to 64bit server by using alias, not a problem!

However, if you like me are using 64bit sql client, and happen to set up the alias under “sql server configuration Manager” -> “sql native client configuration ” – “alias”, you will fail to connect to the server by using this alias.
The right spot you setup the alias should be “sql server configuration Manager” -> “sql native client configuration(32bit) ” – “alias”.

i have no idea why on 64bit client you still need to use 32bit client configuration for alias connection. But that is bloody truth:)

Note: if you are using sql server 2005 cluster then you have to keep the same alias in both of 32bit and 64bit client configuration. Otherwise, by missing alias in the 64bit one, you will get trouble with all the jobs and logs

May 26, 2009

How to make user to view they jobs in sql 2005

Filed under: 3. MS SQL Server — Tags: , , — zhefeng @ 2:25 pm

i tried to make user able to see their own jobs in sql 2005. First thing you need to assign job owner as user’s account.

Second thing, sql 2005 server has some new server roles (on MSDB database user only!) for managing sql agent stuff. Here is the screenshot:

With SQL Server 2005, a user must be a member of one of the new fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent. When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. After this basic introduction let us identify what are the specific uses of each role.

SQLAgentUserRole

This is the least privileged role among new three new fixed server roles. This role has permissions only on operators, local jobs, and job schedules. These types of users can only view the operator but they can’t create, modify or delete operators. They cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. These users cannot delete job history.

SQLAgentReaderRole

SQLAgentReaderRole has all the options that SQLAgentUserRole has. In addition, it does have permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own.

SQLAgentOperatorRole

SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server.

Link: http://www.sqlservercentral.com/articles/Administration/sqlserveragent2005/2376/

April 27, 2009

How to connection sql server named instance SSIS service

Filed under: 3. MS SQL Server — Tags: , , — zhefeng @ 1:52 pm

I’ve occasionally run into the following error when attempting to connect to SSIS from Management Studio:

"Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSvr)."

The problem, as it turns out, is that when I installed SQL Server 2005/2008 on a machine, that a default instance was already installed. In my case SQL Server 2000 was already installed as the default instance. The initial posts I read (and heard about in talking to other colleagues who had the same problem) said that SQL Server 2005/2008 had to be installed as the default instance  if you wanted to be able to use SSIS.

The problem is that unlike the database engine, which can have many running named instances running, SSIS can only run one instance on a server at a time. When SQL Server 2005/2008 is installed there is no option to specify which SQL Server instance should be used to run SSIS. Instead the default configuration is always applied and SSIS tries to start under the default instance. If the default instance is SQL Server 2000, you have a problem. You can only run SSIS on SQL Server 2005 or higher.

Since a server can only run one instance of SSIS you need to make sure that the config file points to the correct instance of SQL Server. Locate the MsDtsSrvr.ini.xml configuration fileand open it with a text or xml editor.

SQL 2005 location:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2008 location:
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

The default contents of the file:

<?xml version=”1.0″ encoding=”utf-8″?>
<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”&gt;
<StopExecutingPackagesOnShutdown>true </StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type=”SqlServerFolder”>
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type=”FileSystemFolder”>
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

Notice the ServerName element points to the default instance of SQL Server (when connecting to the local machine “.” is the same as “(local)” – they both use NamedPipes to connect).

Modify the configuration file to point to the named instance as follows.

<?xml version=”1.0″ encoding=”utf-8″?>
<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema&#8221; xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”&gt;
<StopExecutingPackagesOnShutdown>true </StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type=”SqlServerFolder”>
<Name>MSDB</Name>
<ServerName>.\SQL2005</ServerName>
</Folder>
<Folder xsi:type=”FileSystemFolder”>
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

After making the change you MUST restart the SSIS service for the change to take effect.

Another normal issue to connect to SSIS in sqlserver 2005 is “access is denied”. The following workground can resolve this issue(http://msdn.microsoft.com/en-us/library/aa337083.aspx):

  1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
  2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.
  3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
  4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.
  5. Right-click on MsDtsServer and select Properties.
  6. In the MsDtsServer Properties dialog box, select the Security tab.
  7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.
  8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
  9. Click OK to close the dialog box.
  10. Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.
  11. Close the MMC snap-in.
  12. Restart the Integration Services service.
Older Posts »

Create a free website or blog at WordPress.com.