Find Table Fragmentation

[cc lang=”sql”] DECLARE @Database VARCHAR(255) DECLARE @TableName VARCHAR(255) DECLARE @IndexName VARCHAR(255) SET @Database = ‘SQLServerPlanet’ SET @TableName = ‘Users’ SET @IndexName = NULL SELECT avg_fragmentation_in_percent ,page_count FROM sys.dm_db_index_physical_stats ( DB_ID(@Database) ,OBJECT_ID(@TableName) ,OBJECT_ID(@IndexName) ,NULL ,NULL ) [/cc] Or you can still do it the old fashioned way. Just substitute the name of the table or index below. It should not be in quotes. [cc lang=”sql”][/cc] –Just the table DBCC SHOWCONTIG (tablename) –Table with the index DBCC SHOWCONTIG (tablename, indexname) –Do not block anything (run during prod hours) DBCC SHOWCONTIG (tablename, indexname) WITH FAST –(2005) [cc lang=”sql”][/cc] Continue reading ...

Disable All SQL Server Jobs

[cc lang=”sql”] CREATE TABLE #Job_Names ( Job_Name SYSNAME NOT NULL ) INSERT INTO #Job_Names SELECT name FROM msdb.dbo.sysjobs ORDER BY name DECLARE @job_name SYSNAME DECLARE @job_id UNIQUEIDENTIFIER DECLARE disable_jobs CURSOR FOR SELECT Job_Name FROM #Job_Names SET @job_id = NULL OPEN disable_jobs FETCH NEXT FROM disable_jobs INTO @job_name WHILE @@FETCH_STATUS = 0 BEGIN EXEC msdb.dbo.sp_verify_job_identifiers ‘@job_name’, ‘@job_id’, @job_name OUTPUT, @job_id OUTPUT EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0 SET @job_id = NULL FETCH NEXT FROM disable_jobs INTO @job_name END CLOSE disable_jobs DEALLOCATE disable_jobs DROP TABLE #Job_Names [/cc]

SQL Server Endpoints

Endpoints are essentially web services that expose database access over HTTP. Aside from architectural design decisions, these are useful is that your application development team does not have to have management studio access to SQL Server in order to begin development. The WSDL generated shows the parameters required for the endpoint. There are a few security issues to keep in mind when exposing this functionality. First, if you are not connecting over a secure channel (SSL) then you need to use Integrated or NTLM authentication. Meaning your web application will have to impersonate a user or use the logged in user’s context in order to access the service. (Yes, that does mean enabling impersonation). If you are connecting over a secure channel, then you can use SQL Authentication (mixed mode). The second security consideration is to disable BATCHES. Batches basically allows pass-through SQL calls. Before you can create an endpoint, you have to reserve an HTTP namespace (URL). This will ensure that you do not overwrite a URL already created in IIS or vice-versa. Create the namespace using the following command: [cc lang=”sql”] EXEC sp_reserve_http_namespace N’http://YourServerName:80/WebServices/’ [/cc] Now you can create the endpoint. [cc lang=”sql”] IF EXISTS ( SELECT name from sys.http_endpoints WHERE name = ‘MyEndpoint’ ) BEGIN DROP ENDPOINT MyEndpoint END GO CREATE ENDPOINT MyEndpoint AUTHORIZATION [domainyouruser] — with optional authorization for owner STATE = STARTED — the state of the endpoint — http or tcp AS HTTP ( path=’/WebServices/’, — the virtual path AUTHENTICATION=(INTEGRATED), — type of authentication […] Continue reading ...

Reporting Services Scale-Out Setup with Kerberos Delegation

A common configuration for SQL Reporting Services is to use a scale-out setup. The reason for this is the performance of the rendering (or pagination) of the reports is relatively processor intensive (at the time of this article SQL Server 2005) Along with this setup comes an intrinsic problem. By default, SQL Reporting Services uses integrated authentication with impersonation. In other words, SQL Reporting services uses an authentication scheme that integrates with Active Directory in order to provide access and administrative priveliges. When Reporting Services is installed on the same machine as the database engine, this does not pose any issues. However with a scale-out setup, it does. This obstacle is known as the “double-hop” issue. Why? Because user credentials cannot be passed from one machine to another without a setting up Kerberos Delegation. In this article we will go over how to accomplish this setup and the typical obstacles you may need to overcome. There are a few prerequisites to setting up Kerberos delegation. All computers accessing the application must be in the same domain. The time of all computers must be synchronized using the time service. Kerberos ports must be open if going through a firewall. Client browsers must be setup to allow integrated authentication. Clients must be domain users. All clients must be running Windows 2000 or greater. All client’s browsers must be IE 5+ Functional level of the domain is set to Windows 2003 (highly preferable but not required) Physical Layout Depending on your infrastructure design, […]