Upgrade: SQL 2005 on Windows server 2003 to SQL 2012

Environment: Windows 2003 R2 , SQL Server 2005, SSRS 2005

The fact is, SQL Server 2012 is not supported in Windows Server 2003. While we attempt to do so, we will always end up with an error message as in screenshots.

Test Server configuration

Run Setup

Error Message blocking further installation

How ever, we have many workarounds. One like,Upgrade your Windows server and then upgrade your SQL Server.... Oouch...! (is a very painful task thereafter). 

The cool way is migrate to a new server ;-)

EnJoY!!!!...
CoOoOlLlL.....

Source Control: Using TFS 2010 with BIDS 2008 (or) Adding your BIDS Solution to TFS 2010

Steps:

•     Install Visual Studio 2008 Team Explorer.
•     Install Visual Studio 2008 SP1. Download it from here.  (VS90sp1-KB945140-ENU.exe)

Screen Shots - Installing Visual Studio Service Pack 1: 

• 

  Extraction of files

Products dependent on this update

Accept Terms

Installation Progress

•       Install “Visual Studio Team System 2008 SP1 Forward Compatibility Update for Team Foundation Server 2010”.  Download it from here. (VS90SP1-KB974558-x86.exe)

VS 2008 Forward Compatibility update

Accept terms for Forward Compatibility

Installation of Forward compatibility update

Successful completion of forward compatibility update

EnJoY!!!!...
CoOoOlLlL.....

SQL Administration: Dynamic Management Views

Introduction

The DMVs; newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. DMVs are designed to be used instead of system tables and various other functions provided in SQL Server 2000. Here, we will see only about the frequently used DMVs.

Two types of dynamic management views:

a)   Server-scoped DMV: Stored in Master Database

b)   Database-scoped DMV: Specific to each database

Permission to Execute DMV [Security]

To query a server scoped DMV, the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV, the user must have SELECT privilege on VIEW DATABASE STATE.

• GRANT VIEW SERVER STATE to
• GRANT VIEW DATABASE STATE to

If you want to deny a user permission to query certain DMVs, you can use the DENY command to restrict access to a specific DMV.

Getting Started

All the DMVs exits in SYS schema and their names start with DM_. So when you need to query a DMV, you should prefix the view name with SYS. As an example, if you need to see the total physical memory of the SQL Server machine; then execute the below TSQL command:

SELECT 

(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb 

FROM 

sys.dm_os_sys_info

In this article, I will be explaining some of the DMVs which can be used frequently to understand the current behavior of SQL Server:

1. SQL Server related [Hardware Resources] DMV
2. Database related DMV
3. Index related DMV
4. Execution related DMV

1.   SQL Server Related DMV

This section details the DMVs associated with SQL Server system. SQL DMV is responsible to manage server level resources specific to a SQL Server instance.

This section covers DMVs related to OS, Disk and Memory.

a)   sys.dm_os_sys_info

b)   sys.dm_os_hosts

c)   sys.dm_os_schedulers

d)   sys.dm_io_pending_io_requests

e)   sys.dm_io_virtual_file_stats

f)    sys.dm_os_memory_clerks

g)   sys.dm_os_ring_buffers

a)   sys.dm_os_sys_info

This view returns the information about the SQL Server machine, available resources and the resource consumption.

This view returns information like the following:

1. CPU Count: Number of logical CPUs in the server
2. Hyperthread-ratio: Ratio of logical and physical CPUs
3. Physical_memory_in_bytes: Amount of physical memory available
4. Virtual_memory_in_bytes: Amount of virtual memory available
5. Bpool_commited: Committed physical memory in buffer pool
6. OS_Priority_class: Priority class for SQL Server process
7. Max_workers_thread: Maximum number of workers which can be created

b)   sys.dm_os_hosts

This view returns all the hosts registered with SQL Server 2005. This view also provides the resources used by each host.

1. Name: Name of the host registered
2. Type: Type of hosted component [SQL Native Interface/OLE DB/MSDART]
3. Active_tasks_count: Number active tasks host placed
4. Active_ios_count: I/O requests from host waiting

c)   sys.dm_os_schedulers

SQL Administration: Database and Execution Related DMVs:

sys.database_permissions: Contains information about all the permissions held by users and roles

sys.database_permissions: Contains information about all the permissions held by users and roles

sys.columns: Contains data about each column of an object that has columns, such as views or tables

sys.database_principals: Contains information about all database users and database roles

sys.index_columns: -- Index columns

List of Execution DMVs:

sys.dm_exec_background_job_queue

sys.dm_exec_cached_plans

sys.dm_exec_connections

sys.dm_exec_plan_attributes

sys.dm_exec_query_optimizer_info

sys.dm_exec_query_resource_semaphores

sys.dm_exec_requests

sys.dm_exec_sql_text

sys.dm_exec_xml_handles

sys.dm_exec_background_job_queue_stats

sys.dm_exec_cached_plan_dependent_objects

sys.dm_exec_cursors

sys.dm_exec_query_memory_grants

sys.dm_exec_query_plan

sys.dm_exec_query_stats

sys.dm_exec_sessions

References: MSDN

EnJoY!!!!...

CoOoOlLlL.....