jump to navigation

Generate SQL Scripts for database objects with SMO October 6, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment

Generate SQL Scripts for database objects with SMO

In this article I take about how you can generate SQL object scripts programmatically. Though you can do this through SQL Server Management Studio (SSMS) there might be times (more details on usage scenarios given below) when you would need to create SQL scripts automatically. Click here for more details….

Accessing SQL Server programmatically with SQL Server Management Objects (SMO) October 6, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment

Accessing SQL Server programmatically with SQL Server Management Objects (SMO)

SQL Server 2005 and 2008 provide SQL Server Management Objects (SMO), a collection of namespaces which in turn contain different classes, interfaces, delegates and enumerations, to programmatically work with and manage a SQL Server instance. SMO extends and supersedes SQL Server Distributed Management Objects (SQL-DMO) which was used for SQL Server 2000. In this article, I discuss how you can get started with SMO and how you can programmatically manage a SQL Server instance with your choice of programming language. Click here for more details….

Change Tracking in SQL Server 2008 October 6, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008.
add a comment

Change Tracking in SQL Server 2008

Change Tracking is a light-weight feature which provides a synchronization mechanism between two applications. In other words, it tracks a table for net DML (INSERT, UPDATE and DELETE) changes that occur on a table, so that an application (like a caching application) can refresh itself with just the changed dataset. In this article, I am going to discuss in detail about what Change Tracking is, how it works, how to configure it, an application scenario and how it differs from Change Data Capture. Click here for more details….

Reorganize and Rebuild Index in SQL Server 2005 and 2008 October 6, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment

Reorganize and Rebuild Index in SQL Server 2005 and 2008

Once you have identified the high fragmentation level in your database, which could be a bottleneck in your SQL Server performance, what is the next step of fixing this high fragmentation. In this article, I am going to discuss the different methods and its feasibility to fix the identified high fragmentation levels by Reorganize and Rebuild, click here for more details….

Filtered Indexes in SQL Server 2008 September 30, 2009

Posted by Arshad Ali in Database Administration, SQL Server 2008.
add a comment
Filtered Indexes in SQL Server 2008
SQL Server 2008 introduces Filtered Indexes which is an index with a WHERE clause. Doesn’t it sound awesome especially for a table that has huge amount of data and you often select only a subset of that data? For example, you have a lot of NULL values in a column and you want to retrieve records with only non-NULL values or in another scenario you have several categories of data in a particular column, but you often retrieve data only for a particular category value. This article talks about what a Filtered Index is, how it differs from other indexes, its usage scenario, its benefits and limitations.

Executing dynamic SQL scripts on remote SQL Server with EXEC AT statement September 30, 2009

Posted by Arshad Ali in Database Administration, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment
Executing dynamic SQL scripts on remote SQL Server with EXEC AT statement
With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using “EXEC AT” to execute a pass-through query on the specified linked server which also addresses several shortcomings of OPENQUERY and OPENROWSET table functions. This article talks about this new way of executing dynamic SQL scripts on the remote server in SQL Server 2005 and 2008.

Sending HTML formatted email from SSIS August 25, 2009

Posted by Arshad Ali in SQL Server 2008, SQL Server Integration Services, SSIS, SSIS 2008.
add a comment
Sending HTML formatted email from SSIS
Send Mail Task which is quite simple to use and can be used in a scenario where you need to send plain text email with less development efforts. But you can use, Script Task to overcome the limitations imposed by the Send Mail Task. Click here to see what code you need to write to send HTML formatted mails from SSIS package.

Sending email from SSIS Package August 25, 2009

Posted by Arshad Ali in SQL Server 2008, SQL Server Integration Services, SSIS, SSIS 2008.
add a comment
Sending email from SSIS Package
SSIS provides a built-in “Send Mail Task” to send email. The Send Mail Task is quite simple and straight forward in its configuration and use. Click here to learn how to configure and use Send Mail Task in your SSIS Package.

Resource Governor in SQL Server 2008 August 25, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008.
add a comment
Resource Governor in SQL Server 2008
Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests. In an environment where multiple distinct workloads are present on the same server, Resource Governor enables us to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory. Click here for more details on Resource Governor.

Identifying fragmentation level in SQL Server 2005 and 2008 March 22, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008, SQL Server New Version.
add a comment

Identifying fragmentation level in SQL Server 2005 and 2008

While indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table. Also when you perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may occur and the information in the index can get scattered in the database. Fragmented index data can cause SQL Server to perform unnecessary data reads and switching across different pages, so query performance against a heavily fragmented table can be very poor.
Refer this link to learn more details about fragmentation and different queries to determine the level of fragmentation.

http://www.mssqltips.com/tip.asp?tip=1708