Archive

Archive for November, 2012

How to configure Agent XP if you get an error regarding that in SQL Server

November 21, 2012 Leave a comment

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘Agent XPs’, 1;

GO

RECONFIGURE

GO

Advertisements
Categories: Uncategorized

Error while trying to create a new maintenance plan in SQL Server

November 21, 2012 Leave a comment

Exception has been thrown by the target of an invocation. (mscorlib)

——————————

ADDITIONAL INFORMATION:

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

——————————

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

 

——————————

FIX:

——————————

 

cd C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn>REGSVR32.EXE dts.dll

 

Boom. It worked like a charm

Categories: SQL Server

SqlServer: Running sql script from command line

November 20, 2012 Leave a comment

This is a simple command line utility to run sql scripts from command line in SQL Server.

Sqlcmd –S servername –d databasename –U username –P password –i sqlscriptfullpath

Categories: Uncategorized

SQL SERVER RAID consideration 2

November 8, 2012 Leave a comment

RAID 0: Offer only disk striping and there is no redudancy. No redudancy – no fault tolerance, if disk fails all data is lost. RAID 0 is not recommended for storing SQL Server data files, log files or operating system files.

RAID 1: known as mirroring (duplicates your data disk). High level of fault tolerance. Raid1 is costly bacause you must double the number of disks. RAID 1 support split seeks, so both disk drives perform read operation. RAID 1 can support twice the number of random reads that a single disk can. Writing to RAID 1: write capacity of a single disk drive.

RAID 5: Good level of fault tolerance.It’s an economical, fault-tolerant choice. Fast with random reads, all disk drives can perform read operation. Slow with write operation, when data is written to RAID 5, disk stripe and the parity must be read, parity need to be calculated and then both stripes must be written.

RAID 10: Mirroring a disk stripe, each disk have a duplicate. High performance and high level of fault tolerance, but very expensive. Good random read performance, all disk drives can perform read operation. Write performance of RAID 10: same as with RAID 1.

1. Disk array configuration:

OS files and SQL binaries:RAID 1

Data files: if database is read only use RAID 5 (or >= 90% read and 10% write), for other databases use RAID 10 (better write performance).

Log files: dedicated RAID 10 (or RAID 1 ) for better write performance

Tempdb: Best to use RAID 10

Categories: Uncategorized

SQL Server RAID consideration

November 8, 2012 Leave a comment

SQL Server performance is heavily dependant on access to disks, so basic RAID fundamentals are just as important as other areas when deploying on a SAN. In general the following recommendations are made:

· Log files should always be kept on RAID 1+0.11 If possible also keep data and index files on RAID 1+0, but this can be expensive. Often customers are installing RAID 5, however this is not recommended. RAID 1+0 gives better write performance and availability than RAID 5.

· Microsoft recommends a stripe size of either 64Kb or 256Kb, and further recommends the latter option where index scan ranges are on tables of greater than one hundred megabytes.12 In large installations the Altiris database often contains tables of 100 Mb, so a 256 Kb stripe size is the recommendation. Stripe sizes should never be set below 64Kb, as this is the size of a single SQL extent.

· Because of the heavy use of the TempDB in Altiris, RAID1+0 is the preferred solution for this database. Again, this is a costly solution.

Categories: Uncategorized

RAIDS explained

November 8, 2012 Leave a comment

RAID 0, RAID 1, RAID 5, RAID 10 Explained with Diagrams

RAID stands for Redundant Array of Inexpensive (Independent) Disks.

On most situations you will be using one of the following four levels of RAIDs.

§ RAID 0

§ RAID 1

§ RAID 5

§ RAID 10 (also known as RAID 1+0)

This article explains the main difference between these raid levels along with an easy to understand diagram.

In all the diagrams mentioned below:

§ A, B, C, D, E and F – represents blocks

§ p1, p2, and p3 – represents parity

RAID LEVEL 0

Following are the key points to remember for RAID level 0.

§ Minimum 2 disks.

§ Excellent performance ( as blocks are striped ).

§ No redundancy ( no mirror, no parity ).

§ Don’t use this for any critical system.

RAID LEVEL 1

Following are the key points to remember for RAID level 1.

§ Minimum 2 disks.

§ Good performance ( no striping. no parity ).

§ Excellent redundancy ( as blocks are mirrored ).

RAID LEVEL 5

Following are the key points to remember for RAID level 5.

§ Minimum 3 disks.

§ Good performance ( as blocks are striped ).

§ Good redundancy ( distributed parity ).

§ Best cost effective option providing both performance and redundancy. Use this for DB that is heavily read oriented. Write operations will be slow.

RAID LEVEL 10

Following are the key points to remember for RAID level 10.

§ Minimum 4 disks.

§ This is also called as “stripe of mirrors”

§ Excellent redundancy ( as blocks are mirrored )

§ Excellent performance ( as blocks are striped )

§ If you can afford the dollar, this is the BEST option for any mission critical applications (especially databases).

Categories: Uncategorized

Raid 10 vs Raid 01

November 8, 2012 Leave a comment

RAID 10 is not the same as RAID 01.

This article explains the difference between the two with a simple diagram.

I’m going to keep this explanation very simple for you to understand the basic concepts well. In the following diagrams A, B, C, D, E and F represents blocks.

RAID 10

§ RAID 10 is also called as RAID 1+0

§ It is also called as “stripe of mirrors”

§ It requires minimum of 4 disks

§ To understand this better, group the disks in pair of two (for mirror). For example, if you have a total of 6 disks in RAID 10, there will be three groups–Group 1, Group 2, Group 3 as shown in the above diagram.

§ Within the group, the data is mirrored. In the above example, Disk 1 and Disk 2 belongs to Group 1. The data on Disk 1 will be exactly same as the data on Disk 2. So, block A written on Disk 1 will be mirroed on Disk 2. Block B written on Disk 3 will be mirrored on Disk 4.

§ Across the group, the data is striped. i.e Block A is written to Group 1, Block B is written to Group 2, Block C is written to Group 3.

§ This is why it is called “stripe of mirrors”. i.e the disks within the group are mirrored. But, the groups themselves are striped.

If you are new to this, make sure you understand how RAID 0, RAID 1 and RAID 5 and RAID 2, RAID 3, RAID 4, RAID 6 works.

RAID 01

§ RAID 01 is also called as RAID 0+1

§ It is also called as “mirror of stripes”

§ It requires minimum of 3 disks. But in most cases this will be implemented as minimum of 4 disks.

§ To understand this better, create two groups. For example, if you have total of 6 disks, create two groups with 3 disks each as shown below. In the above example, Group 1 has 3 disks and Group 2 has 3 disks.

§ Within the group, the data is striped. i.e In the Group 1 which contains three disks, the 1st block will be written to 1st disk, 2nd block to 2nd disk, and the 3rd block to 3rd disk. So, block A is written to Disk 1, block B to Disk 2, block C to Disk 3.

§ Across the group, the data is mirrored. i.e The Group 1 and Group 2 will look exactly the same. i.e Disk 1 is mirrored to Disk 4, Disk 2 to Disk 5, Disk 3 to Disk 6.

§ This is why it is called “mirror of stripes”. i.e the disks within the groups are striped. But, the groups are mirrored.

Main difference between RAID 10 vs RAID 01

§ Performance on both RAID 10 and RAID 01 will be the same.

§ The storage capacity on these will be the same.

§ The main difference is the fault tolerance level. On most implememntations of RAID controllers, RAID 01 fault tolerance is less. On RAID 01, since we have only two groups of RAID 0, if two drives (one in each group) fails, the entire RAID 01 will fail. In the above RAID 01 diagram, if Disk 1 and Disk 4 fails, both the groups will be down. So, the whole RAID 01 will fail.

§ RAID 10 fault tolerance is more. On RAID 10, since there are many groups (as the individual group is only two disks), even if three disks fails (one in each group), the RAID 10 is still functional. In the above RAID 10 example, even if Disk 1, Disk 3, Disk 5 fails, the RAID 10 will still be functional.

§ So, given a choice between RAID 10 and RAID 01, always choose RAID 10.

Categories: Uncategorized