use SKUUPGRADE=1 swtich from the command prompt with setup.
Eg :
start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine SKUUPGRADE=1 /qb
Refer
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade
Evaluation to EE Upgrade
Refer :
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2111140&SiteID=1
Sunday, September 30, 2007
Saturday, September 22, 2007
Troubleshooting Performance Problems in SQL Server 2005
Good resource on Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Wednesday, August 29, 2007
Msg 3154, Level 16, State 4, Server YourDatabaseName, Line 1
This error tells you that the database being restored is already existing in the server. Use WITH REPLACE or Change the database Name which you are restoring.
RESTORE DATABASE YOURDATABASENAME FROM DISK='D:\YOURDB.BAK' WITH REPLACE
RESTORE DATABASE YOURDATABASENAME FROM DISK='D:\YOURDB.BAK' WITH REPLACE
Wednesday, August 22, 2007
FAQ : SQL Server Certifications
The New Generation of Microsoft Certifications http://www.microsoft.com/learning/mcp/newgen/default.asp
Microsoft Next Generation of Certifications: Frequently Asked Questions http://www.microsoft.com/learning/mcp/newgen/faq/
Microsoft Certifications for IT Professionals
http://www.microsoft.com/learning/mcp/mcitp
MCITP Database Developer
- SQL Server MCTS (70-431)
- 70-441 (Designing DB Solution)
- 70-442 (Designing and Optimizing Data Access
MCITP DB Administrator
- 70-431 (MCTS)
- 70-443 Designing a DB Server Infrastructure
- 70-444 Optimizing and Maintaining a Database Administration Solution
Or If you are upgrading from MCDBA
- 70-431(MCTS )
- 70447 ( Database Administration by Using Microsoft SQL Server 2005 )
MCITP: Business Intelligence Developer
- MCTS: SQL Server 2005 (70-431) +
- 70-445: PRO: Designing Business Intelligence Solutions by Using Microsoft SQL Server 2005 Analysis Services +
Friday, August 10, 2007
SQL Server 2005 Service Pack and hot fixes
single window for all Service packs and other hot fixes of SQL Server 2005.
http://support.microsoft.com/kb/913089/
http://support.microsoft.com/kb/913089/
Monday, July 2, 2007
Next version of BPA forSQL Server 2005
The new version of the Best Practices Analyzer from MSDN Downloads.
Bookmark Lookup Logical Operation removed from SQL Server 2005
The Bookmark Lookup logical operation was removed in SQL Server 2005 and replaced with clustered index seek and RID lookup. Previous versions of SQL Server displayed the Bookmark Lookup icon but actually performed the clustered index seek and RID lookup.
patterns & practices Performance Testing Guidance project Community site.
Perfect resource for Performance Testing. Worth to read and implement. Atleast gothrough once ,stuffed with lot of information.
http://www.codeplex.com/PerfTesting
http://www.codeplex.com/PerfTesting
Few SQL OS DMVs Query- Courtesy to Slava
Taken from Slava Oks's Blog.
http://blogs.msdn.com/slavao/archive/2006/09/28/776437.aspx
sys.dm_os_schedulers
Q. Do I need to by more CPUs?
In order to answer this question you have to find out if your load is really CPU bounded. Your load is really CPU bounded if a number of runnable tasks per each scheduler always greater than 1 and all of your queries have correct plan. The latter statement is very important, your load can be CPU bounded due to the fact that somehow optimizer generated bad plan – it can happen if your statistics out of date or you tried to perform handcrafted optimization. In this case you don’t want to run to Circuit City to buy more CPUs right a way – you want to fix the plan. Here is the query to find out average length of a runable queue on the system:
select
AVG (runnable_tasks_count)
from
sys.dm_os_schedulers
where
status = 'VISIBLE ONLINE'
Buying more CPUs has also to do with capacity planning. You have to be very careful when performing capacity planning on hardware with HT enabled – remember you don’t have extra physical CPUs. Keep in mind that if your load runs at 60% CPU utilization - it doesn’t mean that you have 40% of extra CPU capacity. You will be very surprise how fast CPU load will jump from 60% to 80% and then even faster to 100% once you apply more and more load.
Q. What is affinity of my schedulers to CPUs?
select
scheduler_id,
CAST (cpu_id as varbinary) AS scheduler_affinity_mask
from
sys.dm_os_schedulers
Does my machine have either hard or soft NUMA configuration enabled?
select
CASE count( DISTINCT parent_node_id)
WHEN 1 THEN 'NUMA disabled'
ELSE 'NUMA enabled'
END
from
sys.dm_os_schedulers
where parent_node_id <> 32
Q. Should I configure SQL Server to use more threads – sp_configure ‘max server threads’?
You can answer this question by looking at the work queue length for each scheduler. If on average such value is above 1 then you might benefit from adding more threads to the system but only if
A. Your load currently is not CPU bounded (See info above on how to find out if your load is CPU bound)
B. Your load currently doesn’t experience any other heavy waits (If you add more threads in this case they will just end up waiting as everyone else)
select
AVG (work_queue_count)
from
sys.dm_os_schedulers
where
status = 'VISIBLE ONLINE'
Q: Is my system I/O bound?
You can answer this question by monitoring length of I/O queues.
select
pending_disk_io_count
from
sys.dm_os_schedulers
If over time they keep on growing or you are seeing periodic jumps or numbers stay relatively high most likely your system is I/O bound. In order to identify the cause you will have to dive further.
http://blogs.msdn.com/slavao/archive/2006/09/28/776437.aspx
sys.dm_os_schedulers
Q. Do I need to by more CPUs?
In order to answer this question you have to find out if your load is really CPU bounded. Your load is really CPU bounded if a number of runnable tasks per each scheduler always greater than 1 and all of your queries have correct plan. The latter statement is very important, your load can be CPU bounded due to the fact that somehow optimizer generated bad plan – it can happen if your statistics out of date or you tried to perform handcrafted optimization. In this case you don’t want to run to Circuit City to buy more CPUs right a way – you want to fix the plan. Here is the query to find out average length of a runable queue on the system:
select
AVG (runnable_tasks_count)
from
sys.dm_os_schedulers
where
status = 'VISIBLE ONLINE'
Buying more CPUs has also to do with capacity planning. You have to be very careful when performing capacity planning on hardware with HT enabled – remember you don’t have extra physical CPUs. Keep in mind that if your load runs at 60% CPU utilization - it doesn’t mean that you have 40% of extra CPU capacity. You will be very surprise how fast CPU load will jump from 60% to 80% and then even faster to 100% once you apply more and more load.
Q. What is affinity of my schedulers to CPUs?
select
scheduler_id,
CAST (cpu_id as varbinary) AS scheduler_affinity_mask
from
sys.dm_os_schedulers
Does my machine have either hard or soft NUMA configuration enabled?
select
CASE count( DISTINCT parent_node_id)
WHEN 1 THEN 'NUMA disabled'
ELSE 'NUMA enabled'
END
from
sys.dm_os_schedulers
where parent_node_id <> 32
Q. Should I configure SQL Server to use more threads – sp_configure ‘max server threads’?
You can answer this question by looking at the work queue length for each scheduler. If on average such value is above 1 then you might benefit from adding more threads to the system but only if
A. Your load currently is not CPU bounded (See info above on how to find out if your load is CPU bound)
B. Your load currently doesn’t experience any other heavy waits (If you add more threads in this case they will just end up waiting as everyone else)
select
AVG (work_queue_count)
from
sys.dm_os_schedulers
where
status = 'VISIBLE ONLINE'
Q: Is my system I/O bound?
You can answer this question by monitoring length of I/O queues.
select
pending_disk_io_count
from
sys.dm_os_schedulers
If over time they keep on growing or you are seeing periodic jumps or numbers stay relatively high most likely your system is I/O bound. In order to identify the cause you will have to dive further.
Connecting to SQL Server 2005 on Vista and Longhorn
I have seen lots of post in forums connecting to sql server 2005 on Vista. Check out this post by Protocol Team. http://blogs.msdn.com/sql_protocols/archive/2007/06/18/connecting-to-sql-server-2005-on-vista-and-longhorn.aspx#comments
Subscribe to:
Posts (Atom)
Posts
