- Library Procedure to list columns with specified delimiter horizontally (one row ) or vertically ( columns)
-- Select Query -> Results to -> Text
/*
EXEC sp_lib_columns 'employee',',','H'
EXEC sp_lib_columns 'employee',',','V'
*/
CREATE procedure dbo.sp_lib_columns
@vchtablename varchar(200),
@chrdelimiter char(1) = ',',
@hvflag char(1) = 'H/V' -- Horizontal/Vertical
as
set nocount on
if @vchtablename is null or len(@vchtablename)= 0
return
if @chrdelimiter is null
return
set nocount on
declare @vchcolumns varchar(8000),@intmaxslno int
declare @tblcolumn table (Column_Name varchar(200),slno int identity(1,1))
set @vchcolumns='('
insert into @tblcolumn(Column_Name)
select column_name
from information_schema.columns where table_name=@vchtablename
order by Ordinal_position
select @intmaxslno=@@rowcount
if @hvflag = 'H'
begin
select @vchcolumns=@vchcolumns+column_name+case when slno<>@intmaxslno then @chrdelimiter else '' end
from @tblcolumn
order by slno
select @vchcolumns + ')'
end
else
begin
select @vchcolumns = @vchcolumns + char(10)
select @vchcolumns = @vchcolumns + column_name + case when slno<>@intmaxslno then @chrdelimiter else '' end + char(10)
from @tblcolumn order by slno
select @vchcolumns + ')' + char(10)
end
GO
Saturday, March 27, 2010
Fix orphan database users in a database after restoration
-- Fix orphan database users in a database after restoration
-- SQL 2005 and above
-- Assumption: Logins are created in the server for orphaned database users
declare @slno int, @maxslno int,@username varchar(100)
set @slno = 0
select identity(int,1,1) as slno ,
a.name as username into #temp
from sys.sysusers a,
sys.syslogins b
where not ( a.issqlrole = 1 or a.isntgroup = 1 or a.isntuser = 1 )
and a.hasdbaccess =1
and
not ( b.isntgroup = 1 or b.isntuser = 1 )
and a.name = b.name
select @maxslno = max(slno) from #temp
while @slno < @maxslno
begin
set @slno = @slno + 1
select @username = a.username from #temp a where slno = @slno
EXEC sp_change_users_login 'auto_fix',@username
end
drop table #temp
-- SQL 2005 and above
-- Assumption: Logins are created in the server for orphaned database users
declare @slno int, @maxslno int,@username varchar(100)
set @slno = 0
select identity(int,1,1) as slno ,
a.name as username into #temp
from sys.sysusers a,
sys.syslogins b
where not ( a.issqlrole = 1 or a.isntgroup = 1 or a.isntuser = 1 )
and a.hasdbaccess =1
and
not ( b.isntgroup = 1 or b.isntuser = 1 )
and a.name = b.name
select @maxslno = max(slno) from #temp
while @slno < @maxslno
begin
set @slno = @slno + 1
select @username = a.username from #temp a where slno = @slno
EXEC sp_change_users_login 'auto_fix',@username
end
drop table #temp
Thursday, March 25, 2010
Credential, Proxies, SQL Server Agent Jobs
Terminology:
Credentials:
A credential is a record that contains the authentication information (credentials) required to connect to a resource outside SQL Server. Most credentials contain a Windows user name and password.
The information stored in a credential enables a user who has connected to SQL Server by way of SQL Server Authentication to access resources outside the server instance. When the external resource is Windows, the user is authenticated as the Windows user specified in the credential. A single credential can be mapped to multiple SQL Server logins. A SQL Server login can be mapped to only one credential.
System credentials are created automatically and are associated with specific endpoints. Names for system credentials start with two hash signs (##).
Proxies:
SQL Server Agent uses proxies to manage security contexts. A proxy can be used in more than one job step. Members of the sysadmin fixed server role can create proxies.
Each proxy corresponds to a security credential. Each proxy can be associated with a set of subsystems and a set of logins. The proxy can be used only for job steps that use a subsystem associated with the proxy. To create a job step that uses a specific proxy, the job owner must either use a login associated with that proxy or be a member of a role with unrestricted access to proxies. Members of the sysadmin fixed server role have unrestricted access to proxies. Members of SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole can only use proxies to which they have been granted specific access. Each user that is a member of any of these SQL Server Agent fixed database roles must be granted access to specific proxies so that the user can create job steps that use those proxies.
Two Types of Proxies
1. xp_cmdshell proxy
2. SQL Server Agent Proxy
xp_cmdshell proxy :
This is used for non sysadmin users to execute xp_cmdshell from SSMS (QA) or from SPs or in Jobs ( Only as T SQL Job Step )
xp_cmdshell is disabled during sql server installation
1. Enable xp_cmdshell through Facets or Surface Area Configuration
2. Create a domain Windows account ( domain\sqljobrun) in the domain
3. For each database server use this account to create credentials
Note: This account should not be local admin account in database server and should not be sysadmin. It need not access SQL Server at all.
4. Provide Log on batch to sqljobrun: Start -> Administrative Tools - > Local Security Policy - > Log on as a batch Job Click Add User or Group .
Add domain\sqljobrun
5. Create a non sys admin Login and Database user for SQL Server User (sqltest)
6. Provide database access sqltest to master database
7. Provide execute access to xp_cmdshell in master database
Use master
Go
Grant exec on xp_cmdshell to sqltest
8. Server - > Object Explorer -> Right Click -> Properties - > Security
9. Server Proxy Account -> Enable Server Proxy Account
Proxy account :domain\sqljobrun
Password : xxxxxx
This will create system credential ##xp_cmdshell_proxy_account## under Security -> Credentials
sqltest can execute xp_cmdshell in SSMS, Stored Procedures and T-SQL job steps .
Note: This is not same as cmdexec . cmdexec uses direct execution of cmd.exe
Sqltest test will have same directory and window privileges of domain\sqljobrun
Note: Make sure domain\sqljobrun password newer expires .
Any error in executing xp_cmdshell, provide read/execute access to windows\system32\cmd.exe to domain\sqljobrun
SQL Agent Proxies :
The SQL Agent Proxy accounts allow users without sysadmin authority to own and run SQL Agent jobs. The SQL Agent contains a number of subsystems, each one dedicated to a particular type of work. A proxy account is required in order for an account without sysadmin authority to use these subsystems
The recommended approach is to configure each Non-Admin account that needs to run SQL Agent jobs as a proxy account. This will allow all the authorities required by the job to be contained within the job owner account. The first step in creating a SQL Agent proxy is to create a credential. Therefore, every Job Owner account would have a credential defined for it in order to both isolate and encapsulate the authorities required by a given job.
The credentials created for a given job owner are then associated with each type of SQL Agent subsystem used by the job owner. It is this association that creates the required proxies. This will ensure that all steps in the jobs owned by that account run with a predictable and consistent security profile.
SubSystem:
A subsystem is a predefined object that represents a set of functionality available to a SQL Server Agent proxy. Each proxy has access to one or more subsystems. Subsystems provide security because they delimit access to the functionality that is available to a proxy. Each job step runs in the context of a proxy, except for Transact-SQL job steps. Transact-SQL job steps use the EXECUTE AS command to set the security context.
Subsystem name Description
Microsoft ActiveX Script Run an ActiveX scripting job step.
Important:
The ActiveX Scripting subsystem will be removed from SQL Server Agent in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Operating System (CmdExec) Run an executable program.
PowerShell Run a PowerShell scripting job step.
Replication Distributor Run a job step that activates the replication Distribution Agent.
Replication Merge Run a job step that activates the replication Merge Agent.
Replication Queue Reader Run a job step that activates the replication Queue Reader Agent.
Replication Snapshot Run a job step that activates the replication Snapshot Agent.
Replication Transaction Log Reader Run a job step that activates the replication Log Reader Agent.
Analysis Services Command Run an Analysis Services command.
Analysis Services Query Run an Analysis Services query.
SSIS package execution Run an SSIS package.
Any non sysadmin Sql Server user who needs to create, execute and maintain sql server jobs must be member of one of these database fixed roles in msdb database.
• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole
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. A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent.
Permissions of SQL Server Agent Fixed Database Roles
The SQL Server Agent database role permissions are concentric in relation to one another -- more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). For example, if members of least-privileged SQLAgentUserRole have been granted access to proxy_A, members of both SQLAgentReaderRole and SQLAgentOperatorRole automatically have access to this proxy even though access to proxy_A has not been explicitly granted to them. This may have security implications, which are discussed in the following sections about each role.
SQLAgentUserRole Permissions
SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and 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. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.
Security Note:
Consider the security implications before granting proxy access to members of the SQL Server Agent database roles. The SQLAgentReaderRole and the SQLAgentOperatorRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole and SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to the SQLAgentUserRole and can use those proxies.
The following table summarizes SQLAgentUserRole permissions on SQL Server Agent objects.
Action Operators Local jobs (owned jobs only) Job schedules (owned schedules only) Proxies
Create/modify/delete No Yes 1 Yes No
View list (enumerate) Yes 2 Yes Yes Yes 3
Enable/disable No Yes Yes Not applicable
View properties No Yes Yes No
Execute/stop/start Not applicable Yes Not applicable Not applicable
View job history Not applicable Yes Not applicable Not applicable
Delete job history Not applicable No 4 Not applicable Not applicable
Attach/detach Not applicable Not applicable Yes Not applicable
1 Cannot change job ownership.
2 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.
3 List of proxies only available in the Job Step Properties dialog box of Management Studio.
4 Members of SQLAgentUserRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs.
SQLAgentReaderRole Permissions
SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as 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. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.
Security Note:
Consider the security implications before granting proxy access to members of the SQL Server Agent database roles. Members of SQLAgentReaderRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole have access to all SQL Server Agent proxies that have been granted to SQLAgentUserRole and can use those proxies.
The following table summarizes SQLAgentReaderRole permissions on SQL Server Agent objects.
Action Operators Local jobs Multiserver jobs Job schedules Proxies
Create/modify/delete No Yes 1 (owned jobs only) No Yes (owned schedules only) No
View list (enumerate) Yes 2 Yes Yes Yes Yes 3
Enable/disable No Yes (owned jobs only) No Yes (owned schedules only) Not applicable
View properties No Yes Yes Yes No
Edit properties No Yes (owned jobs only) No Yes (owned schedules only) No
Execute/stop/start Not applicable Yes (owned jobs only) No Not applicable Not applicable
View job history Not applicable Yes Yes Not applicable Not applicable
Delete job history Not applicable No 4 No Not applicable Not applicable
Attach/detach Not applicable Not applicable Not applicable Yes (owned schedules only) Not applicable
1 Cannot change job ownership.
2 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.
3 List of proxies only available in the Job Step Properties dialog box of Management Studio.
4 Members of SQLAgentReaderRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs.
SQLAgentOperatorRole Permissions
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. They can also enable or disable all local jobs and schedules on the server. To enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule. Only the parameters that specify the job or schedule name or identifier and the @enabled parameter can be specified by members of SQLAgentOperatorRole. If they specify any other parameters, execution of these stored procedures fails. SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own.
The Jobs, Alerts, Operators, and Proxies nodes in SQL Server Management Studio Object Explorer are visible to members of SQLAgentOperatorRole. Only the Error Logs node is not visible to members of this role.
Security Note:
Consider the security implications before granting proxy access to members of the SQL Server Agent database roles. Members of SQLAgentOperatorRole are automatically members of SQLAgentUserRole and SQLAgentReaderRole. This means that members of SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to either SQLAgentUserRole or SQLAgentReaderRole and can use those proxies.
The following table summarizes SQLAgentOperatorRole permissions on SQL Server Agent objects.
Action Alerts Operators Local jobs Multiserver jobs Job schedules Proxies
Create/modify/delete No No Yes 2 (owned jobs only) No Yes (owned schedules only) No
View list (enumerate) Yes Yes 1 Yes Yes Yes Yes
Enable/disable No No Yes 3 No Yes 4 Not applicable
View properties Yes Yes Yes Yes Yes Yes
Edit properties No No Yes (owned jobs only) No Yes (owned schedules only) No
Execute/stop/start Not applicable Not applicable Yes No Not applicable Not applicable
View job history Not applicable Not applicable Yes Yes Not applicable Not applicable
Delete job history Not applicable Not applicable Yes No Not applicable Not applicable
Attach/detach Not applicable Not applicable Not applicable Not applicable Yes (owned schedules only) Not applicable
1 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.
2 Cannot change job ownership.
3 SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.
4 SQLAgentOperatorRole members can enable or disable schedules they do not own by using the stored procedure sp_update_schedule and specifying values for the @enabled and the @schedule_id (or @name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.
To execute cmdexec and Active X Script for sqltest
1. Login as sysadmin
2. Go to Object Explorer - > Security - > Credentials
3. New Credential
Credential name : sqltest credential
Identity : domain\sqljobrun
Password: xxxxx
Confirm password: xxxxx
OK
4. SQL Server Agent - > Proxies
5. New Proxy
Proxy name: sqltest proxy
Credential name: sqltest credential
Active to the following Subsystem
Check Box :
ActiveX Script
OperatingSystem(CmdExec)
6. Principals : Add sqltest
With this sqltest can create jobs. For job step type Operating System(cmdexec) sqltest should select sqltest proxy to run the job
Credentials:
A credential is a record that contains the authentication information (credentials) required to connect to a resource outside SQL Server. Most credentials contain a Windows user name and password.
The information stored in a credential enables a user who has connected to SQL Server by way of SQL Server Authentication to access resources outside the server instance. When the external resource is Windows, the user is authenticated as the Windows user specified in the credential. A single credential can be mapped to multiple SQL Server logins. A SQL Server login can be mapped to only one credential.
System credentials are created automatically and are associated with specific endpoints. Names for system credentials start with two hash signs (##).
Proxies:
SQL Server Agent uses proxies to manage security contexts. A proxy can be used in more than one job step. Members of the sysadmin fixed server role can create proxies.
Each proxy corresponds to a security credential. Each proxy can be associated with a set of subsystems and a set of logins. The proxy can be used only for job steps that use a subsystem associated with the proxy. To create a job step that uses a specific proxy, the job owner must either use a login associated with that proxy or be a member of a role with unrestricted access to proxies. Members of the sysadmin fixed server role have unrestricted access to proxies. Members of SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole can only use proxies to which they have been granted specific access. Each user that is a member of any of these SQL Server Agent fixed database roles must be granted access to specific proxies so that the user can create job steps that use those proxies.
Two Types of Proxies
1. xp_cmdshell proxy
2. SQL Server Agent Proxy
xp_cmdshell proxy :
This is used for non sysadmin users to execute xp_cmdshell from SSMS (QA) or from SPs or in Jobs ( Only as T SQL Job Step )
xp_cmdshell is disabled during sql server installation
1. Enable xp_cmdshell through Facets or Surface Area Configuration
2. Create a domain Windows account ( domain\sqljobrun) in the domain
3. For each database server use this account to create credentials
Note: This account should not be local admin account in database server and should not be sysadmin. It need not access SQL Server at all.
4. Provide Log on batch to sqljobrun: Start -> Administrative Tools - > Local Security Policy - > Log on as a batch Job Click Add User or Group .
Add domain\sqljobrun
5. Create a non sys admin Login and Database user for SQL Server User (sqltest)
6. Provide database access sqltest to master database
7. Provide execute access to xp_cmdshell in master database
Use master
Go
Grant exec on xp_cmdshell to sqltest
8. Server - > Object Explorer -> Right Click -> Properties - > Security
9. Server Proxy Account -> Enable Server Proxy Account
Proxy account :domain\sqljobrun
Password : xxxxxx
This will create system credential ##xp_cmdshell_proxy_account## under Security -> Credentials
sqltest can execute xp_cmdshell in SSMS, Stored Procedures and T-SQL job steps .
Note: This is not same as cmdexec . cmdexec uses direct execution of cmd.exe
Sqltest test will have same directory and window privileges of domain\sqljobrun
Note: Make sure domain\sqljobrun password newer expires .
Any error in executing xp_cmdshell, provide read/execute access to windows\system32\cmd.exe to domain\sqljobrun
SQL Agent Proxies :
The SQL Agent Proxy accounts allow users without sysadmin authority to own and run SQL Agent jobs. The SQL Agent contains a number of subsystems, each one dedicated to a particular type of work. A proxy account is required in order for an account without sysadmin authority to use these subsystems
The recommended approach is to configure each Non-Admin account that needs to run SQL Agent jobs as a proxy account. This will allow all the authorities required by the job to be contained within the job owner account. The first step in creating a SQL Agent proxy is to create a credential. Therefore, every Job Owner account would have a credential defined for it in order to both isolate and encapsulate the authorities required by a given job.
The credentials created for a given job owner are then associated with each type of SQL Agent subsystem used by the job owner. It is this association that creates the required proxies. This will ensure that all steps in the jobs owned by that account run with a predictable and consistent security profile.
SubSystem:
A subsystem is a predefined object that represents a set of functionality available to a SQL Server Agent proxy. Each proxy has access to one or more subsystems. Subsystems provide security because they delimit access to the functionality that is available to a proxy. Each job step runs in the context of a proxy, except for Transact-SQL job steps. Transact-SQL job steps use the EXECUTE AS command to set the security context.
Subsystem name Description
Microsoft ActiveX Script Run an ActiveX scripting job step.
Important:
The ActiveX Scripting subsystem will be removed from SQL Server Agent in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Operating System (CmdExec) Run an executable program.
PowerShell Run a PowerShell scripting job step.
Replication Distributor Run a job step that activates the replication Distribution Agent.
Replication Merge Run a job step that activates the replication Merge Agent.
Replication Queue Reader Run a job step that activates the replication Queue Reader Agent.
Replication Snapshot Run a job step that activates the replication Snapshot Agent.
Replication Transaction Log Reader Run a job step that activates the replication Log Reader Agent.
Analysis Services Command Run an Analysis Services command.
Analysis Services Query Run an Analysis Services query.
SSIS package execution Run an SSIS package.
Any non sysadmin Sql Server user who needs to create, execute and maintain sql server jobs must be member of one of these database fixed roles in msdb database.
• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole
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. A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent.
Permissions of SQL Server Agent Fixed Database Roles
The SQL Server Agent database role permissions are concentric in relation to one another -- more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). For example, if members of least-privileged SQLAgentUserRole have been granted access to proxy_A, members of both SQLAgentReaderRole and SQLAgentOperatorRole automatically have access to this proxy even though access to proxy_A has not been explicitly granted to them. This may have security implications, which are discussed in the following sections about each role.
SQLAgentUserRole Permissions
SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and 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. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.
Security Note:
Consider the security implications before granting proxy access to members of the SQL Server Agent database roles. The SQLAgentReaderRole and the SQLAgentOperatorRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole and SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to the SQLAgentUserRole and can use those proxies.
The following table summarizes SQLAgentUserRole permissions on SQL Server Agent objects.
Action Operators Local jobs (owned jobs only) Job schedules (owned schedules only) Proxies
Create/modify/delete No Yes 1 Yes No
View list (enumerate) Yes 2 Yes Yes Yes 3
Enable/disable No Yes Yes Not applicable
View properties No Yes Yes No
Execute/stop/start Not applicable Yes Not applicable Not applicable
View job history Not applicable Yes Not applicable Not applicable
Delete job history Not applicable No 4 Not applicable Not applicable
Attach/detach Not applicable Not applicable Yes Not applicable
1 Cannot change job ownership.
2 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.
3 List of proxies only available in the Job Step Properties dialog box of Management Studio.
4 Members of SQLAgentUserRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs.
SQLAgentReaderRole Permissions
SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as 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. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.
Security Note:
Consider the security implications before granting proxy access to members of the SQL Server Agent database roles. Members of SQLAgentReaderRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole have access to all SQL Server Agent proxies that have been granted to SQLAgentUserRole and can use those proxies.
The following table summarizes SQLAgentReaderRole permissions on SQL Server Agent objects.
Action Operators Local jobs Multiserver jobs Job schedules Proxies
Create/modify/delete No Yes 1 (owned jobs only) No Yes (owned schedules only) No
View list (enumerate) Yes 2 Yes Yes Yes Yes 3
Enable/disable No Yes (owned jobs only) No Yes (owned schedules only) Not applicable
View properties No Yes Yes Yes No
Edit properties No Yes (owned jobs only) No Yes (owned schedules only) No
Execute/stop/start Not applicable Yes (owned jobs only) No Not applicable Not applicable
View job history Not applicable Yes Yes Not applicable Not applicable
Delete job history Not applicable No 4 No Not applicable Not applicable
Attach/detach Not applicable Not applicable Not applicable Yes (owned schedules only) Not applicable
1 Cannot change job ownership.
2 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.
3 List of proxies only available in the Job Step Properties dialog box of Management Studio.
4 Members of SQLAgentReaderRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs.
SQLAgentOperatorRole Permissions
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. They can also enable or disable all local jobs and schedules on the server. To enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule. Only the parameters that specify the job or schedule name or identifier and the @enabled parameter can be specified by members of SQLAgentOperatorRole. If they specify any other parameters, execution of these stored procedures fails. SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own.
The Jobs, Alerts, Operators, and Proxies nodes in SQL Server Management Studio Object Explorer are visible to members of SQLAgentOperatorRole. Only the Error Logs node is not visible to members of this role.
Security Note:
Consider the security implications before granting proxy access to members of the SQL Server Agent database roles. Members of SQLAgentOperatorRole are automatically members of SQLAgentUserRole and SQLAgentReaderRole. This means that members of SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to either SQLAgentUserRole or SQLAgentReaderRole and can use those proxies.
The following table summarizes SQLAgentOperatorRole permissions on SQL Server Agent objects.
Action Alerts Operators Local jobs Multiserver jobs Job schedules Proxies
Create/modify/delete No No Yes 2 (owned jobs only) No Yes (owned schedules only) No
View list (enumerate) Yes Yes 1 Yes Yes Yes Yes
Enable/disable No No Yes 3 No Yes 4 Not applicable
View properties Yes Yes Yes Yes Yes Yes
Edit properties No No Yes (owned jobs only) No Yes (owned schedules only) No
Execute/stop/start Not applicable Not applicable Yes No Not applicable Not applicable
View job history Not applicable Not applicable Yes Yes Not applicable Not applicable
Delete job history Not applicable Not applicable Yes No Not applicable Not applicable
Attach/detach Not applicable Not applicable Not applicable Not applicable Yes (owned schedules only) Not applicable
1 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio.
2 Cannot change job ownership.
3 SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.
4 SQLAgentOperatorRole members can enable or disable schedules they do not own by using the stored procedure sp_update_schedule and specifying values for the @enabled and the @schedule_id (or @name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.
To execute cmdexec and Active X Script for sqltest
1. Login as sysadmin
2. Go to Object Explorer - > Security - > Credentials
3. New Credential
Credential name : sqltest credential
Identity : domain\sqljobrun
Password: xxxxx
Confirm password: xxxxx
OK
4. SQL Server Agent - > Proxies
5. New Proxy
Proxy name: sqltest proxy
Credential name: sqltest credential
Active to the following Subsystem
Check Box :
ActiveX Script
OperatingSystem(CmdExec)
6. Principals : Add sqltest
With this sqltest can create jobs. For job step type Operating System(cmdexec) sqltest should select sqltest proxy to run the job
DBMAIL
DBMAIL
Terminology:
Account: Email Account (Not SQL Server User) having access to SMTP servers. Multiple accounts are only required if more than one SMTP server is present or to use more than one sender’s email address.
Profile: Set of Email Accounts.
Note: A profile can consist of multiple accounts. An account can be member of different profiles. This is a many to many relationship.
Users: SQL Server Users who directly or through group membership are in DatabaseMailUserRole in msdb to send emails. Access to Users is provided through profiles. For Public profile all users in msdb and member of DatabaseMailUserRole in can send mail. For Private Profile, sysadmin need to provide access to sql servers.
Prerequisites:
1. SMTP server
2. Enable DBMAIL ( Facets (SQL 2008) or Surface Area Configuration (SQL 2005 )
3. Email account or set of email accounts having access to SMTP
4. SQL Server Service Account should have access to SMTP Server. So this should be a domain account.
5. Database Server should have access to SMTP. SMTP relay for the system should be enabled at SMTP (Exchange) server.
6. SQL Server Users who needs to send email must be a member of DatabaseMailUserRole in msdb and should have execute permission to sp_send_dbmail
7. For Private Profiles , sysadmin should provide access to users in msdb having DatabaseMailUserRole in msdb
8. For Public Profile , All users in DatabaseMailUserRole in msdb can send the mail
Introduction
1. Database Mail can be used for sending e-mail messages to users from the SQL Server Database Engine. The messages can contain query results/include files from any resource on the network.
2. Database Mail uses the standard Simple Mail Transfer Protocol (SMTP) to send mail. This does not require any POP3 client like outlook to be installed on SQL Server which is the case for SQL MAIL.
3. To minimize the impact on SQL Server, the component that delivers e-mail runs outside of SQL Server, in a separate process. SQL Server will continue to queue e-mail messages even if the external process stops or fails. The queued messages will be sent once the outside process or SMTP server comes online.
4. A Database Mail profile allows specifying more than one SMTP server. If an SMTP server is unavailable, mail can still be delivered to another SMTP server
5. Database Mail is fully supported on a cluster
6. Database Mail provides background, or asynchronous, delivery. When sp_send_dbmail is called to send a message, Database Mail adds a request to a Service Broker queue. The stored procedure returns immediately. The external e-mail component receives the request and delivers the e-mail.
7. Database Mail allows creating multiple profiles within a SQL Server instance. Optionally, a profile can be chosen to send a message.
8. Each profile can contain multiple failover accounts. Different profiles can be configured with different accounts to distribute e-mail across multiple e-mail servers
9. Database Mail is fully supported on 64-bit installations of SQL Servers
10. To send Database Mail, user must be a member of the DatabaseMailUserRole database role in the msdb database.
11. A Public profile consists of all users in msdb database users or groups.
12. A private profile restricts access to a specified list of users.
13. Users can figure Attachment file size and attached file extensions with sysmail_configure_sp
14. Database Mail maintains the information for e-mail accounts within SQL Server Database Engine. This can be configured through Database Mail Configuration Wizard or T-SQL.
15. Database Mail logs e-mail activity to SQL Server, the Microsoft Windows application event log, and to tables in the msdb database.
16. Database Mail keeps copies of messages and attachments sent in the msdb database for auditing Database Mail usage and review the retained messages
17. Database Mail allows e-mails formatted as HTML
18. Database Mail can be enabled By
• Database Mail Configuration Wizard
• sp_configure
• surface area configuration
19. Sysadmin can only use the dbmail wizard. Users in DatabaseMailUserRole database role in msdb can only send messages
DB Mail Architecture
1. Configuration and security objects create profiles and accounts used by Database Mail in msdb
2. The msdb database acts as the mail-host database that holds the messaging objects those Database Mail users to send e-mail. These objects include the sp_send_dbmail stored procedure and the data structures that hold information about messages.
3. msdb size need to be controlled periodically since this database contains all the email sent with attachments. Daily backup of this database is very important.
4. The Database Mail executable is an external program that reads from a queue in the msdb database and sends messages to e-mail servers
5. Database Mail records logging information in the msdb database and the Microsoft Windows application event log.
Database Mail is designed on a queued architecture that uses service broker technologies. When users execute sp_send_dbmail, the stored procedure inserts an item into the mail queue and creates a record that contains the e-mail message. Inserting the new entry in the mail queue starts the external Database Mail process (DatabaseMail90.exe). The external process reads the e-mail information and sends the e-mail message to the appropriate e-mail server or servers. The external process inserts an item in the Status queue for the outcome of the send operation. Inserting the new entry in the status queue starts an internal stored procedure that updates the status of the e-mail message. Besides storing the sent, or unsent, e-mail message, Database Mail also records any e-mail attachments in the system tables. Database Mail views provide the status of messages for troubleshooting, and stored procedures allow for administration of the Database Mail queue.
Database Mail provides two types of configuration objects:
• Database Mail profiles are sets of accounts.
• Database Mail accounts contain information for e-mail servers.
sp_send_dbmail
Sends an e-mail message to the specified recipients. The message may include a query result set, file attachments, or both. When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message. This stored procedure is in the msdb database.
Transact-SQL Syntax Conventions
Syntax
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
Arguments
[ @profile_name = ] 'profile_name'
Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.
[ @recipients = ] 'recipients'
Is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.
[ @copy_recipients = ] 'copy_recipients'
Is a semicolon-delimited list of e-mail addresses to carbon copy the message to. The copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.
[ @blind_copy_recipients = ] 'blind_copy_recipients'
Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to. The blind copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.
[ @subject = ] 'subject'
Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is 'SQL Server Message'.
[ @body = ] 'body'
Is the body of the e-mail message. The message body is of type nvarchar(max), with a default of NULL.
[ @body_format = ] 'body_format'
Is the format of the message body. The parameter is of type varchar(20), with a default of NULL. When specified, the headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values:
• TEXT
• HTML
Defaults to TEXT.
[ @importance = ] 'importance'
Is the importance of the message. The parameter is of type varchar(6). The parameter may contain one of the following values:
• Low
• Normal
• High
Defaults to Normal.
[ @sensitivity = ] 'sensitivity'
Is the sensitivity of the message. The parameter is of type varchar(12). The parameter may contain one of the following values:
• Normal
• Personal
• Private
• Confidential
Defaults to Normal.
[ @file_attachments = ] 'file_attachments'
Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max).
[ @query = ] 'query'
Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.
[ @execute_query_database = ] 'execute_query_database'
Is the database context within which the stored procedure runs the query. The parameter is of type sysname, with a default of the current database. This parameter is only applicable if @query is specified.
[ @attach_query_result_as_file = ] attach_query_result_as_file
Specifies whether the result set of the query is returned as an attached file. attach_query_result_as_file is of type bit, with a default of 0.
When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified.
[ @query_attachment_filename = ] query_attachment_filename
Specifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.
[ @query_result_header = ] query_result_header
Specifies whether the query results include column headers. The query_result_header value is of type bit. When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1. This parameter is only applicable if @query is specified.
[ @query_result_width = ] query_result_width
Is the line width, in characters, to use for formatting the results of the query. The query_result_width is of type int, with a default of 256. The value provided must be between 10 and 32767. This parameter is only applicable if @query is specified.
[ @query_result_separator = ] 'query_result_separator'
Is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ' ' (space).
[ @exclude_query_output = ] exclude_query_output
Specifies whether to return the output of the query execution in the e-mail message. exclude_query_output is bit, with a default of 0. When this parameter is 0, the execution of the sp_send_dbmail stored procedure prints the message returned as the result of the query execution on the console. When this parameter is 1, the execution of the sp_send_dbmail stored procedure does not print any of the query execution messages on the console.
[ @append_query_error = ] append_query_error
Specifies whether to send the e-mail when an error returns from the query specified in the @query argument. append_query_error is bit, with a default of 0. When this parameter is 1, Database Mail sends the e-mail message and includes the query error message in the body of the e-mail message. When this parameter is 0, Database Mail does not send the e-mail message, and sp_send_dbmail ends with return code 1, indicating failure.
[ @query_no_truncate = ] query_no_truncate
Specifies whether to execute the query with the option that avoids truncation of large variable length data types (varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, and user-defined data types). When set, query results do not include column headers. The query_no_truncate value is of type bit. When the value is 0 or not specified, columns in the query truncate to 256 characters. When the value is 1, columns in the query are not truncated. This parameter defaults to 0.
Note:
When used with large amounts of data, the @query_no_truncate option consumes additional resources and can slow server performance.
[ @mailitem_id = ] mailitem_id [ OUTPUT ]
Optional output parameter returns the mailitem_id of the message. The mailitem_id is of type int.
Return Code Values
Friday, March 19, 2010
TEXT to VARCAHR(MAX)
TEXT to VARCAHR(MAX)
You may have migrated from SQL Server 2000 to SQL Server 2005/2008. There are several tables in your database having text data type columns. Text data type and image columns are deprecated, and replaced by varchar (max) and varbinary(max) . Apart from this, if the text data is stored outside the row, when this column is referenced in select, insert, update statements, it will consume extra reads slowing down query performance
You can check, whether text data is stored inside the row or on a separate data page by using the following query
select objectproperty(object_id('tablename'),'TableTextInRowLimit')
If the output is 0 then text in row limit is not set. i.e. The text column(s) are stored out side the data page and pointers in the data row, references the text data stored in text pages.
In SQL 2005 and above, by default lob data stored inside the data row. If we want to store lob data outside the row
EXEC sp_tableoption 'tablename', 'large value types out of row', 1;
Step by Step process to convert text to varchar(max)
We will take an example of an employee table and go through the step by step process to convert text data types to varchar (max) .
Script 1 :
use tempdb
go
if exists (select 1 from sys.tables where name = 'employee')
begin
drop table dbo.employee
end
create table dbo.employee
(
employee_id int identity(1,1) primary key ,
employee_name varchar(25) not null,
salary numeric(10,2) not null,
employee_info text
)
go
insert into employee ( employee_name,salary,employee_info)
values ('Rama',10000,'Good Character' )
insert into employee ( employee_name,salary,employee_info)
values ('Mohammad',40000,'Nice' )
insert into employee ( employee_name,salary,employee_info)
values ('James',50000,'Compassion' )
insert into employee ( employee_name,salary,employee_info)
values ('Saraswathi',50000,'Has high Knowledge' )
insert into employee ( employee_name,salary,employee_info)
values ('Krishna',20000,'Very Good' )
insert into employee ( employee_name,salary,employee_info)
values ('Fathima',32000.40,'Caring' )
insert into employee ( employee_name,salary,employee_info)
values ('Teresa',44000.50,'Professional' )
insert into employee ( employee_name,salary,employee_info)
values ('Buddha',90000.00,'Calm' )
go
Script 2 :
select objectproperty(object_id('employee'),'TableTextInRowLimit')
–- Returns 0 , which means text column rows are stored outside the data page
go
Now, let us create a function which will return page number in readable format
Script 3 :
create function dbo.ufn_convertpage_nums(@page_num binary (6))
returns varchar(20)
as
begin
return
( convert(varchar(2),(convert(int,substring(@page_num,6,1)) * power(2,8)) +
convert(int,substring(@page_num,5,1)))) + ':' +
convert( varchar(11),
( convert(int, substring(@page_num,4,1 )) * power(2,24)) +
( convert(int, substring(@page_num,3,1 )) * power(2,16)) +
( convert(int, substring(@page_num,2,1 )) * power(2,8)) +
( convert(int, substring(@page_num,1,1 )))
)
end
go
Script 4 : CAUTION: Don’t run this on production server. Run only on test server
use tempdb
go
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC FREESYSTEMCACHE ('All')
set statistics io on
go
set nocount on
select employee_id,employee_name, employee_info from dbo.employee
go
set statistics io off
go
Output is
-- Table 'employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 16, lob physical reads 0, lob read-ahead reads 0.
There are 16 lobs (large object) logical reads. Since there are 8 rows in the table
Script 5 :
select convert(varchar(25),object_name(p.object_id)) as tablename,
a.type_desc as page_type_desc,
total_pages as totpages,
used_pages,
data_pages ,
partition_number as pnum,rows ,
dbo.ufn_convertpage_nums(first_page) as first_page,
dbo.ufn_convertpage_nums(root_page) as root_page,
dbo.ufn_convertpage_nums(first_iam_page) as first_iam_page,
partition_id,
allocation_unit_id
from sys.partitions p
inner join sys.system_internals_allocation_units a
on p.partition_id = a.container_id
where p.object_id = object_id('dbo.employee')
go
tablename page_type_desc totpages used_pages data_pages pnum rows first_page root_page first_iam_page partition_id allocation_unit_id
employee IN_ROW_DATA 2 2 1 1 8 1:155 1:155 1:158 72057594038648800 72057594043236300
employee LOB_DATA 2 2 0 1 8 1:153 1:153 1:154 72057594038648800 72057594043301800
There are 2 LOB_DATA pages
After migration for previous versions of SQL server to SQL Server 2005/2008, text data type should be altered to varchar (max)
Script 6 :
alter table employee
alter column employee_info varchar(max)
go
Again, Run Script 4
Table 'employee'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 8, lob physical reads 1, lob read-ahead reads 0.
Lob reads reduced from 16 to 8
Running Script 5
tablename page_type_desc totpages used_pages data_pages pnum rows first_page root_page first_iam_page partition_id allocation_unit_id
employee IN_ROW_DATA 2 2 1 1 8 1:155 1:155 1:158 72057594039238600 72057594044809200
employee LOB_DATA 2 2 0 1 8 1:153 1:153 1:154 72057594039238600 72057594044874700
employee ROW_OVERFLOW_DATA 0 0 0 1 8 0:0 0:0 0:0 72057594039238600 72057594044940200
You can see that a new page type description ROW_OVERFLOW_DATA is created. This page type is used when a row,does not have enough space to hold its maximum capacity of 8060 bytes
To make it more efficient, and reduce lob reads we need to update the table. We are updating the same column to push out of row data to inside the data row.
Script 7 :
update employee set
employee_info = employee_info
go
Note: If there are large number of rows , update in batches to reduce logging and performance improvement.
Again, Run Script 4
Table 'employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
Number of Lob reads are reduced to 0. This means data is moved from outside text pages to inside the data row
As an alterantive we can create the table with varchar(max) instead of text data type
Script 8 :
Note: In SQL Server 2005 and later, by default varchar(max) data is stored in the data row,
if the length of row does not exceed 8060. If length of row reaches this threshold,
data is pushed out to large object page (text page)
if exists (select 1 from sys.tables where name = 'employee')
begin
drop table dbo.employee
end
create table dbo.employee
(
employee_id int identity(1,1) primary key ,
employee_name varchar(25) not null,
salary numeric(10,2) not null,
employee_info varchar(max)
)
go
Run the insert script (Script 1 )
Run Script 4
Table 'employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Run Script 5
tablename page_type_desc totpages used_pages data_pages pnum rows first_page root_page first_iam_page partition_id allocation_unit_id
employee IN_ROW_DATA 2 2 1 1 8 1:153 1:153 1:154 72057594039304100 72057594045005800
employee LOB_DATA 0 0 0 1 8 0:0 0:0 0:0 72057594039304100 72057594045071300
After migration or if there are text data type columns , it is better to create a new table replacing text data types with varchar(max) data type and populating data from the old table
Conclusion
Recommended Method After Migration:
1. Create a new table (employee_new) replacing text data type with varchar(max)
2. Populate from old table (employee) to employee_new .
Note: If number of rows are very high , insert in batches to avoid bloating of log file and to improve performance
3. Drop old table (employee) . You may be required to drop constraints if any before dropping the table
4. Rename new table (employee_new) to old table (employee) . Put back the dropped constraints if any
Alternative Method
1. Alter table data type from text to varchar(max)
2. Update the same column
Note: If number of rows are very high ,update in batches to avoid bloating of log file and to improve performance
You may have migrated from SQL Server 2000 to SQL Server 2005/2008. There are several tables in your database having text data type columns. Text data type and image columns are deprecated, and replaced by varchar (max) and varbinary(max) . Apart from this, if the text data is stored outside the row, when this column is referenced in select, insert, update statements, it will consume extra reads slowing down query performance
You can check, whether text data is stored inside the row or on a separate data page by using the following query
select objectproperty(object_id('tablename'),'TableTextInRowLimit')
If the output is 0 then text in row limit is not set. i.e. The text column(s) are stored out side the data page and pointers in the data row, references the text data stored in text pages.
In SQL 2005 and above, by default lob data stored inside the data row. If we want to store lob data outside the row
EXEC sp_tableoption 'tablename', 'large value types out of row', 1;
Step by Step process to convert text to varchar(max)
We will take an example of an employee table and go through the step by step process to convert text data types to varchar (max) .
Script 1 :
use tempdb
go
if exists (select 1 from sys.tables where name = 'employee')
begin
drop table dbo.employee
end
create table dbo.employee
(
employee_id int identity(1,1) primary key ,
employee_name varchar(25) not null,
salary numeric(10,2) not null,
employee_info text
)
go
insert into employee ( employee_name,salary,employee_info)
values ('Rama',10000,'Good Character' )
insert into employee ( employee_name,salary,employee_info)
values ('Mohammad',40000,'Nice' )
insert into employee ( employee_name,salary,employee_info)
values ('James',50000,'Compassion' )
insert into employee ( employee_name,salary,employee_info)
values ('Saraswathi',50000,'Has high Knowledge' )
insert into employee ( employee_name,salary,employee_info)
values ('Krishna',20000,'Very Good' )
insert into employee ( employee_name,salary,employee_info)
values ('Fathima',32000.40,'Caring' )
insert into employee ( employee_name,salary,employee_info)
values ('Teresa',44000.50,'Professional' )
insert into employee ( employee_name,salary,employee_info)
values ('Buddha',90000.00,'Calm' )
go
Script 2 :
select objectproperty(object_id('employee'),'TableTextInRowLimit')
–- Returns 0 , which means text column rows are stored outside the data page
go
Now, let us create a function which will return page number in readable format
Script 3 :
create function dbo.ufn_convertpage_nums(@page_num binary (6))
returns varchar(20)
as
begin
return
( convert(varchar(2),(convert(int,substring(@page_num,6,1)) * power(2,8)) +
convert(int,substring(@page_num,5,1)))) + ':' +
convert( varchar(11),
( convert(int, substring(@page_num,4,1 )) * power(2,24)) +
( convert(int, substring(@page_num,3,1 )) * power(2,16)) +
( convert(int, substring(@page_num,2,1 )) * power(2,8)) +
( convert(int, substring(@page_num,1,1 )))
)
end
go
Script 4 : CAUTION: Don’t run this on production server. Run only on test server
use tempdb
go
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC FREESYSTEMCACHE ('All')
set statistics io on
go
set nocount on
select employee_id,employee_name, employee_info from dbo.employee
go
set statistics io off
go
Output is
-- Table 'employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 16, lob physical reads 0, lob read-ahead reads 0.
There are 16 lobs (large object) logical reads. Since there are 8 rows in the table
Script 5 :
select convert(varchar(25),object_name(p.object_id)) as tablename,
a.type_desc as page_type_desc,
total_pages as totpages,
used_pages,
data_pages ,
partition_number as pnum,rows ,
dbo.ufn_convertpage_nums(first_page) as first_page,
dbo.ufn_convertpage_nums(root_page) as root_page,
dbo.ufn_convertpage_nums(first_iam_page) as first_iam_page,
partition_id,
allocation_unit_id
from sys.partitions p
inner join sys.system_internals_allocation_units a
on p.partition_id = a.container_id
where p.object_id = object_id('dbo.employee')
go
tablename page_type_desc totpages used_pages data_pages pnum rows first_page root_page first_iam_page partition_id allocation_unit_id
employee IN_ROW_DATA 2 2 1 1 8 1:155 1:155 1:158 72057594038648800 72057594043236300
employee LOB_DATA 2 2 0 1 8 1:153 1:153 1:154 72057594038648800 72057594043301800
There are 2 LOB_DATA pages
After migration for previous versions of SQL server to SQL Server 2005/2008, text data type should be altered to varchar (max)
Script 6 :
alter table employee
alter column employee_info varchar(max)
go
Again, Run Script 4
Table 'employee'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 8, lob physical reads 1, lob read-ahead reads 0.
Lob reads reduced from 16 to 8
Running Script 5
tablename page_type_desc totpages used_pages data_pages pnum rows first_page root_page first_iam_page partition_id allocation_unit_id
employee IN_ROW_DATA 2 2 1 1 8 1:155 1:155 1:158 72057594039238600 72057594044809200
employee LOB_DATA 2 2 0 1 8 1:153 1:153 1:154 72057594039238600 72057594044874700
employee ROW_OVERFLOW_DATA 0 0 0 1 8 0:0 0:0 0:0 72057594039238600 72057594044940200
You can see that a new page type description ROW_OVERFLOW_DATA is created. This page type is used when a row,does not have enough space to hold its maximum capacity of 8060 bytes
To make it more efficient, and reduce lob reads we need to update the table. We are updating the same column to push out of row data to inside the data row.
Script 7 :
update employee set
employee_info = employee_info
go
Note: If there are large number of rows , update in batches to reduce logging and performance improvement.
Again, Run Script 4
Table 'employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
Number of Lob reads are reduced to 0. This means data is moved from outside text pages to inside the data row
As an alterantive we can create the table with varchar(max) instead of text data type
Script 8 :
Note: In SQL Server 2005 and later, by default varchar(max) data is stored in the data row,
if the length of row does not exceed 8060. If length of row reaches this threshold,
data is pushed out to large object page (text page)
if exists (select 1 from sys.tables where name = 'employee')
begin
drop table dbo.employee
end
create table dbo.employee
(
employee_id int identity(1,1) primary key ,
employee_name varchar(25) not null,
salary numeric(10,2) not null,
employee_info varchar(max)
)
go
Run the insert script (Script 1 )
Run Script 4
Table 'employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Run Script 5
tablename page_type_desc totpages used_pages data_pages pnum rows first_page root_page first_iam_page partition_id allocation_unit_id
employee IN_ROW_DATA 2 2 1 1 8 1:153 1:153 1:154 72057594039304100 72057594045005800
employee LOB_DATA 0 0 0 1 8 0:0 0:0 0:0 72057594039304100 72057594045071300
After migration or if there are text data type columns , it is better to create a new table replacing text data types with varchar(max) data type and populating data from the old table
Conclusion
Recommended Method After Migration:
1. Create a new table (employee_new) replacing text data type with varchar(max)
2. Populate from old table (employee) to employee_new .
Note: If number of rows are very high , insert in batches to avoid bloating of log file and to improve performance
3. Drop old table (employee) . You may be required to drop constraints if any before dropping the table
4. Rename new table (employee_new) to old table (employee) . Put back the dropped constraints if any
Alternative Method
1. Alter table data type from text to varchar(max)
2. Update the same column
Note: If number of rows are very high ,update in batches to avoid bloating of log file and to improve performance
Subscribe to:
Posts (Atom)