for Sql server 2005, It was a bug when you modify maintainance plan you may get the error
“no description found” and you cannot edit the Maitainance plan.
problem could be the software you install is confilicting with sql server

to resolve this register following dll
regsvr32 msxml3.dll
regsvr32 msxml6.dll

Reference:
https://connect.microsoft.com/SQLServer/feedback/details/243850/ss2005-cannot-save-maintenance-plan-no-description-found-error?wa=wsignin1.0″
HTH
Vinay

Important: System Objects/Views/DMV/DMF

This is the some of the list of System Objects/Views/DMV/DMF.

Catalog View:

All system information is stored on catalog views which look like

Sys.xxx

Eg. Sys.databases

Compatibile views:

This are views to manage old compatibility

Select * from master.dbo.sysprocesses

Server Side DMV

1. Sys.dm_exec* (query exection)
2. Sys.dm_io* (io level)
3. Sys.dm_db* (database level  like index….)
4. Sys.dm_tran* (transaction level)
5. Sys.dm_os* (os level)

Component level DMV:

1. Sys.dm_Repl* (replication)
2. Sys.dm_broker* (service broker)
3. Sys.dm_fts* (full Text Search)
4. Sys.dm_clr*( CLR)

—————————– Understand the dmv:

Require permission as view database stats or view server stats

1.  select count(1) from sys.all_objects –1841

2.  select * from sys.databases

3.  select count(*) from sys.system_objects –1763

4.  select * from sys.sysobjects –- show all the objects in present database

5.  select * from sys.system_sql_modules where object_id=object_id(’sys.sysobjects’)—-shows code for system objects

6.  select * from sys.dm_exec_sessions -– like sysprocesses info

7.  select * from sys.dm_exec_requests order by logical_reads desc -– like sysprocesses info

select * from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) where session_id>50

8.  select * from sys.dm_os_waiting_tasks  — current waits

9.  select * from sys.dm_os_wait_stats  — report for waits

10.select * from sys.dm_os_latch_stats –report for latchs

11.select db_name(database_id),* from sys.dm_io_virtual_file_stats(NULL , NULL )

order by io_stall desc

12.select * from sys.dm_os_wait_stats

13.select * from sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL)

14.select * from sys.dm_db_partition_stats

15.select * from sys.dm_exec_query_stats

Sys.dm_exec_query_stats:

Select * from Sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle)

cross apply sys.dm_exec_query_plan(qs.plan_handle)

16.select * from sys.dm_db_index_usage_stats

17.select object_name(object_id),* from sys.dm_db_index_physical_stats (

db_id(),null,null,null,default)  — index fragmentation information

18.select * from sys.dm_os_latch_stats

19.select * from sys.dm_db_missing_index_group_stats

1. select * from sys.dm_exec_query_optimizer_info
2. select * from sys.dm_exec_cached_plans

22.select * from sys.system_internals_allocation_units  — inside information about object first root and next page address.

1. select * from sys.system_internals_partitions  –more inforation abt views useful for join of system_internals_allocation_units

22, 23 used to be achieve using DBCC IND for DBCC Page() with dbcc traceon(3604)

1. select * from sys.dm_io_pending_io_requests  — any pending io
2. select * from sys.dm_tran_locks  –lock info
3. select * from sys.dm_tran_active_transactions
4. select * from sys.dm_os_sys_info  –sys info
5. select * from sys.dm_os_performance_counters  –perfmon info
6. select * from sys.dm_os_schedulers  –CPU info
7. select * from sys.dm_os_ring_buffers  –memory
8. select * from sys.dm_os_buffer_descriptors  –memory

32.select * from sys.dm_os_memory_cached_counters  –Sql server 2008

33.select * from sys.dm_os_sys_memory –Sql server 2008

DBCC MEMORYSTATS

Thanx.

Vinay

Reference:

http://technet.microsoft.com/en-us/library/bb838723(office.12).aspx

Yesterday I though of doing an audio webcast what all I blog.

So started audio webcast, its a series of webcasts on Whats New blog category which I blog here and here please refer blog when listen.

Today I uploaded it on public folder. Hope you will enjoy it same as I did while recording.

Following are the audio webcasts and their link to it

1. History of MS Sql Server

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma

2. Whats New MS Sql Server 7

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!744

3.  Whats New in MS Sql Server 2000

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!746

4. Whats New in MS Sql Server 2005

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!747

5. Whats New in MS Sql Server 2008 & MS Sql Server 2008 R2

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!748

Please let me know your feedback on this.

Please read the Blog as a reference for this Audio Webcast.

http://vinay-thakur.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3dWhats%2520New

and

http://rdbmsexperts.com/Blogs/?p=57

Thanks and Happy Learning.

Thanx.

Vinay

http://rdbmsexperts.com/Blogs

vinay-thakur.spaces.live.com/

twitter/thakurvinay

As discussed about Disaster Recovery and High Availbility topic here continue on this. today would like to discuss some more information about mirroring.

As stated in my ealier post about mirroring, today I would like to write about how to conifigure mirroring, mirroring is very simple to configure. before going for it we should deside what time of mirroring we want to go with

High Availability

High Protection

High Performance

depending upon that we have to configure Witness.

I would say there are only two steps for it.

1. Create End points

2. alter database “” set PARTNER

this is the simple procedure I want to discuss there are other several ways to setup the mirroring.

1. need to decide what is your “priceple” database server , “Mirror” database server and “Witness” database server. this can be seperate box or difference named instance on the same server, but should be on differenct instance as on one  sql server you can create multiple “endpoints” which is the way Mirroring works(interacts)

the simple command for creating endpoints could be

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT =5022)
FOR DATABASE_MIRRORING (ROLE = ALL )
if you are on same server(box) with different instances then we have to give different port number(eg. 5023, 5024)

* Principle and Mirrored database should be having same edition of sql server. witness can be express edition, and should be of “FULL RECOVERY MODEL”

Backup the Principle and restore it on Mirrored database with NORecovery
once you create the “Endpoints” then we have to alter the database PARTNER to listen to that endpoint port like.

at principle

alter database MirrorDB  set Partner=’TCP://MirroredServer:5022′

at mirror server

alter database MirrorDB  set Partner=’TCP://PricipleSrv:5024′

we can check the property of the principle database of option “Mirror” you will see the end point specified and other mirroring related info.

you are done. this is quick and short way of configuring mirror, once priciple is down it will rollover to mirror database server and Mirror server will become priciple and when Priciple is up that will become “mirror” server ie. reverse the setup.

*You cannot mirror the master, msdb, temp, or model system databases.

You can add on the securites and other configuration on it.

This is what I learned today.

Thanx.

Vinay

http://rdbmsexperts.com/Blogs/

http://vinay-thakur.spaces.live.com/blog

twitter\thakurvinay

Continue to part 1 

the Jet ODBC driver/OLE DB Provider as 64 bit.

http://blogs.msdn.com/psssql/archive/2010/01/21/how-to-get-a-x64-version-of-jet.aspx

2010 Office System Driver Beta: Data Connectivity Components
http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

Must read Paul’s Myth for April Month.

http://www.sqlskills.com/BLOGS/PAUL/category/Misconceptions.aspx

Must read Glenns blog for April “A DMV per DAY”

http://glennberrysqlperformance.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=amonth%3d4%26ayear%3d2010

Will try to post more info here.

Thanx.

Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/blog/

http:\www.twitter.com\thakurvinay

High Availability and Disaster recovery Techniques in sql server

This blog is the introduction and brief overview for high availability and Disaster recovery techniques available in sql server, and its my understanding on it. I will write detail blog on each technique will later

There are different ways of achieving this is sql server each has its own advantage and disadvantages. following methods are used for HA and DR :

1. Clustering
2. Database Mirroring.
3. Log Shipping
4. Replication
5. Backup and restore
6. Detach and Attach

1. Clustering:-
   1. Cluster is a very high level technique to achieve HA.
   2. Cluster may not be good for DR plan as here both host should be close to each other
   3. Clustering require few seconds to move to other node if one node failed for high availability.
   4. Earlier to SS2005, this can only be possible at “Enterprise Edition” which itself cost expensive.
   5. At SS2005 2 node clustering is possible at standard edition as well.
   6. Different types of cluster are available as one node cluster (active/passive), two node cluster(active/active). Multi node clustering.
   7. Can be configured very easily below the steps to setup clustering.
   8. Clustering requires both nodes to be of same configuration.
   9. Active/active clustering can also be used for load balancing.

Reference:

http://msdn.microsoft.com/en-us/library/ms179530.aspx

http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&displaylang=en

http://support.microsoft.com/kb/254321

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

http://technet.microsoft.com/en-us/library/cc917693.aspx

http://technet.microsoft.com/en-us/magazine/2007.03.sqlclusters.aspx

1. 2. Database Mirroring:-
   1. Database mirroring is introduced in SS2005 sp1.
   2. This is much cost efficient technique unlike clustering this can be as HA. As here mirror server does not required to be of same configuration.
   3. Here Principle / Witness/Mirror servers, Principle and witness could be on same server.
   4. The setup for Mirroring is here
   5. Mirroring is configured with three ways:

Reference:

http://blogs.msdn.com/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-Performance-boost-for-Database-Mirroring.aspx

http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx

http://community.winsupersite.com/blogs/itprotips/archive/2008/02/28/sql-server-2008-database-mirroring.aspx

http://msdn.microsoft.com/en-us/library/ms188712.aspx

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

http://msdn.microsoft.com/en-us/library/ms177412.aspx

http://technet.microsoft.com/en-us/library/cc917681.aspx

http://technet.microsoft.com/en-us/library/cc917680.aspx

3. Log Shipping:-

1. Log shipping is well known term for HA and DR plan and is available from SS2000 onwards. In fact we can setup the custom log shipping using backup and restore technique.
2. Log shipping is available at enterprise edition on Sql server 2000 and is available at Standard edition for SS2005 onwards.
3. Log shipping is very efficient and easy technique the setup.
4. To use this for HA may require little downtime… manual intervention to make secondary server as primary, move logins and other activity. And same is applied for DR plan.

Reference:

http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx 

http://support.microsoft.com/kb/314515 

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

http://www.youtube.com/watch?v=2k003Dj7fHo 

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1309728,00.html

http://www.informit.com/articles/article.aspx?p=331402

http://technet.microsoft.com/en-us/library/cc917705.aspx

http://www.sql-articles.com/articles/high-availability/ten-steps-to-configure-log-shipping

4. Replication:-

1. a. Replication is generally used as a technique to load balance the production server and distinguish the high activity production with reporting server.
2. b. Replication can also be used as HA or DR technique but not especially for it. Can be achievable but like logshipping it also require manual intervention.

Reference:

http://vinay-thakur.spaces.live.com/?_c11_BlogPart_BlogPart=summary&_c=BlogPart&partqs=cat%3dReplication

http://blogs.msdn.com/repltalk/default.aspx

http://www.replicationanswers.com/

1. 5. Backup and restore:-
   1. a. This is a standard way for HA and DR.
   2. b. This requires DBA skill.
   3. c. Time consuming process.

Reference:

http://msdn.microsoft.com/en-us/library/ms177429.aspx

http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups

http://articles.techrepublic.com.com/5100-10878_11-1041267.html

1. 6. Detach and attach database:-
   1. a. This is not a technique for HA or DR but can be possible as now a days things are on SAN and high end storage devices which are redundant and can recovery the data. in such cases if we could recovery sql server data and log file(optional) we can attach the file can be used.
   2. b. Steps are here

Reference:

http://www.mssqlcity.com/Articles/Adm/attach_database.htm

http://support.microsoft.com/kb/224071

http://www.sqlservercentral.com/articles/Administration/spattachdb/169/

I already shared some of the great links for deadlock in my earlier blog here

got a question for my interview what is deadlock?, so would like to brief about it, following description would be deadlock by simple transactions which is very good for sql server 2000.

Deadlock in simple terms:

Deadlock contains:

>> Two or more sessions/processes
>> Locks(exclusive/Update lock)
>> both the session is running -Editing serially and trying to access each other after they are locked(update/exclusive).
>> Generally whenever we begin the trasaction and running lock query e.g. update (update lock) then we are locking that object…. so that way if we are having two sessions and both sessions are locking two tables respectively and now when one session tries to access session locked by other and ( this is blocking)…… finally when second session tries to access first which is already locked and waiting for first session to complete, so second session never could able to access data from table which is locked by first session(which is waiting for second sessions data)— cause deadlock.

Now in my next blog I would explain deadlock could also cause by Reindex,  these senario would be sql server 2005 and above. you can get these all information by my ealier blog link above. which has all the great experts link for reference.

Thanx.

Vinay

* I Blog whenever I learn something and also for my future understanding/reference.

Everyone wants to know the list of error numbers present in sql server.

Today found a very interesting site( another great site -added in my fevorites list for reference).

http://www.sql-server-helper.com/tips/index.aspx

above link shows at the bottom list of sql server error messages.   some messages are missing in above – (I am seaching that will update here once get an information:-)). Also check cools stuff like practise test on that site its great, but it is for sql server 2000.

for sql server 2005 and above the same can be accesss using following query

select * from sys.messages

Thanx.

Vinay

I already discussed the steps of server starts which log info at error logs here, In this blog I would like to share the information we can get from error log.

Error Log  contains:

1. Version of Sql server
2. Version of  Windows
3. Sql ServerPackage(Enterprise/standard…)
4. How manu CPU are on the server.
5. Sql server exe process ID
6. What network library are used to listen for new connection
7. state of database for as DLL (TDS)
8. where any extended procedure executes
9. What method was sql server shutdown(service system/t-sql)
10. History of DSS and transaction log backup
11. summary of DBCC CheckDB Execution.

There are many ways we can hide and show some extra information on error log by enabling/desabling respective trace( I will discuss it somewhere in my blog later).

Thanx.

Vinay

Hi Eveyone.

Now on I will be bloging on this site….

Thanx.

Vinay

http://rdbmsexperts.com/Blogs/

http://vinay-thakur.spaces.live.com/

twitter @thakurvinay