Microsoft SQL Server 2012 Builds

Build	SQLSERVR.EXE Build	File version	Q	KB	KB / Description	Date Updated	Availability
11.00.2845	11.0.2845	2011.110.2845.0			SQL Server 2012 Service Pack 1 Customer Technology Preview (CTP)	September 20, 2012 *new	Downloadable
11.00.2332	11.0.2332	2011.110.2332.0	Q272374	KB272374	2723749 Cumulative update package 3 (CU3) for SQL Server 2012	August 29, 2012	Downloadable
11.00.2325	11.0.2325	2011.110.2325.0	Q270327	KB270327	2703275 Cumulative update package 2 (CU2) for SQL Server 2012	June 18, 2012	Downloadable
11.00.2316	11.0.2316	2011.110.2316.0	Q267936	KB267936	2679368 Cumulative update package 1 (CU1) for SQL Server 2012	April 12, 2012	Downloadable
11.00.2214	11.0.2214	2011.110.2214.0	Q268530	KB268530	2685308 FIX: SSAS uses only 20 cores in SQL Server 2012 Business Intelligence	April 6, 2012	Downloadable
11.00.2100	11.0.2100	2011.110.2100.60			SQL Server 2012 RTM	March 6, 2012	Downloadable (MSDN/TechNet subscribers only)
11.00.1913	11.0.1913	2011.110.1913.37			Microsoft SQL Server 2012 Release Candidate 1 (RC1)	December 16, 2011	Downloadable
11.00.1750	11.0.1750	2011.110.1750.32			Microsoft SQL Server 2012 Release Candidate 0 (RC0)	November 17, 2011	Downloadable
11.00.1440	11.0.1440	2010.110.1440.19			Microsoft SQL Server 2012 (codename Denali) Community Technology Preview 3 (CTP3)	July 11, 2011	Downloadable
11.00.1103	11.0.1103	2010.110.1103.9			Microsoft SQL Server 2012 (codename Denali) Community Technology Preview 1 (CTP1)	November 8, 2010

MS SQL Server Build Numbers

	RTM (Gold, no SP)	SP1	SP2	SP3	SP4
SQL Server 2012      codename Denali	11.00.2100.60
SQL Server 2008 R2      codename Kilimanjaro	10.50.1600.1	10.50.2500	10.50.4000
SQL Server 2008      codename Katmai	10.00.1600.22	10.00.2531	10.00.4000	10.00.5500
SQL Server 2005      codename Yukon	9.00.1399.06	9.00.2047	9.00.3042	9.00.4035	9.00.5000
SQL Server 2000      codename Shiloh	8.00.194	8.00.384	8.00.532	8.00.760	8.00.2039
SQL Server 7.0      codename Sphinx	7.00.623	7.00.699	7.00.842	7.00.961	7.00.1063

Service Broker DMV

More on:- http://msdn.microsoft.com/en-us/library/ms176110.aspx

sys.dm_broker_activated_tasks	sys.dm_broker_connections
sys.dm_broker_forwarded_messages	sys.dm_broker_queue_monitors

Service Broker

Service Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances. The basic functions of sending and receiving messages forms a part of a “conversation.” Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.

Service broker find applications when single or multiple SQL server instances are used. This functionality helps in sending messages to remote databases on different servers and processing of the messages within a single database. In order to send messages between the instances, the Service Broker uses TCP/IP.

This transaction message queuing system enables the developers to build secure and reliable applications, which are scalable. The developers can design applications from independent components known as “services.” If the applications need to avail the functionality of these services, then it sends message to the particular “service.”

Loosely coupled applications (programs that exchange messages independently) are supported by the Service broker. The three components of the Service broker are as follows: conversation components (which consist of the conversation groups, conversations and messages); service definition components (which define the conversations); and networking and security components (defines the infrastructure used for exchanging messages between instances)

The maintenance of Service Broker is easy and it is a part of the routine database administration procedure. This is because this functionality forms a part of the Database Engine. Service Broker also provides security by preventing unauthorized access from networks and by message encryption.

More on :- http://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/

http://msdn.microsoft.com/en-us/library/ms345108(v=sql.90).aspx

Replication Related Dynamic Management Views

More on http://msdn.microsoft.com/en-us/library/ms188754.aspx

Replication DMV:- http://msdn.microsoft.com/en-us/library/ms176053.aspx

sys.dm_broker_activated_tasks	sys.dm_broker_connections
sys.dm_broker_forwarded_messages	sys.dm_broker_queue_monitors

Easy Notable Differences in 2008 SQL Cluster

• Up to 8 nodes in SQL Server 2005, 16 nodes in SQL Server 2008.
• No remote execution on the cluster nodes. We can now upgrade or patch a cluster with minimal downtime. Updates and service packs must be apply on each node separately; setup must be executed on each node. The same to add a new node, setup must be executed on each node.
• Service packs can now be uninstalled.
• SQL Server 2008 are not supported on WOW mode.
• MSDTC was required as a cluster resource to install SQL Server 2000 and SQL Server 2005 clusters. SQL Server 2008 does not have that requirement anymore to install a cluster.

Difference Between CheckPoint and LazyWriter

Checkpoint:

* Flush dirty pages to Disk
* By default Occurs approximately every 1 minute
* Does not check the memory pressure
* Occurs for any DDL statement
* Occurs before Backup/Detach command
* Can be managed with sp_confige -recovery interval option
* Checkpoint occurs on database level.

* To find when the checkpoint occur use undocumented function

select  * from ::fn_dblog(null,null)  WHERE [Operation] like ‘%CKPT’

In sql server 2000

select top 10  [Operation],[checkpoint begin],[checkpoint end] from ::fn_dblog(null,null)  WHERE [Operation] in(‘LOP_BEGIN_CKPT’, ‘LOP_END_CKPT’,'LOP_XACT_CKPT’)


Also enabling trace flag will provide information on error log when checkpoint started at what database.

DBCC TRACEON(3502, -1)


* Checkpoint impact the performance (very low IO) for heavy system, so we can even disable automatic checkpoint —-Never do this, using trace flag 3505

* Simple recovery it flush the tlog file after 70% full.


LazyWriter:

* Lazy writer is on the server

* To check when lazy writer occurs use

SQL Server Buffer Manager Lazy writes/sec

* Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)

* Sql server manages by its own.