SQL Server Editions 2008/R2/2012

SQL Server Editions 2008/R2/2012
Here are the different editions available for SQL2012:

1.     SQL Server 2012 Standard Edition

SQL Server 2012 Standard Edition is limited to 16 cores and 64GB of RAM. It provides the core relational database engine and basic business intelligence (BI) capabilities.

It doesn't include support for the advanced availability features or the more powerful BI features such as PowerPivot, Power View, and Master Data Services. The Standard Edition does include support for two-node AlwaysOn Failover Clusters, and it's licensed either per core or per server.

2.     SQL Server 2012 Business Edition

SQL Server 2012 Business Intelligence Edition is a new member of the SQL Server family. Like the Standard edition, the Business Intelligence edition is limited to 16 cores for the database engine and 64GB of RAM. However, it can use the maximum number of cores supported by the OS for Analysis Services and Reporting Services.

The Business Intelligence edition includes all of the features in the Standard edition and support for advanced BI features such as Power View and PowerPivot, but it lacks support for the advanced availability features like AlwaysOn Availability Groups and other online operations. The Business Intelligence edition supports two-node AlwaysOn Failover Clusters, and it's licensed per server.

3.     SQL Server 2012 Enterprise Edition

SQL Server 2012 Enterprise Edition is the high end of the SQL Server 2012 product lineup. It supports the maximum number of cores and RAM in the host OS and provides the complete SQL Server feature set, including support for all of the advanced availability and BI features.

The Enterprise edition supports up to 16-node AlwaysOn Failover Clusters as well as AlwaysOn Availability Groups, online operations, PowerPivot, Power View, Master Data Services, advanced auditing, transparent data encryption, the ColumnStore index, and more. The Enterprise edition is licensed per core.

4.     SQL Server 2012 Express Editions and LocalDB

SQL Server 2012 will continue to offer three versions of the free SQL Server Express Edition: Express (Database Only), Express with Tools, and Express with Advanced Services. Microsoft will also continue to offer a download of SQL Server Management Studio Express.

The Express editions are limited to support for one CPU and 1GB of RAM. Databases are limited to 10GB per database. In addition, a new option called LocalDB will also be available.

LocalDB isn't the old Compact Edition: It uses the same sqlservr.exe engine as the other editions of SQL Server and is designed for developers. It requires no configuration and runs as a user process, not as a service.

For more information about LocalDB, check out the SQL Server Express Web Log at MSDN: "Introducing LocalDB, an improved SQL Express."

5.     SQL Server 2012 Web and Developer Editions

SQL Server 2012 Web Edition and SQL Server 2012 Developer Edition will continue to be part of the SQL Server 2012 family. The Developer edition provides the same feature set as the Enterprise edition. However, it's licensed per developer and can't be used for production work. The Web edition is licensed only to hosting companies with a Services Provider License Agreement (SLPA).

Here are the different editions available for SQL Server 2008.

1. Enterprise Edition
   Data management and business intelligence platform providing enterprise class scalability, high availability, and security for running business-critical applications
   
   
2. Standard Edition
   Data management and business intelligence platform providing ease of use and manageability for running departmental applications
3. Workgroup Edition
   Data management and reporting platform providing secure, remote synchronization, and management capabilities for running branch applications
   
   
4. Developer EditionMay be installed and used by one user to design, develop, test, and demonstrate your programs on as many systems as needed
   
   
5. Web EditionA low-TCO, scalable, and manageable database option for web hosters and end customers looking to deploy publicly facing web applications and services
   
   
6. Express Edition
   A free edition of SQL Server ideal for learning and building desktop and small server applications and for redistribution by ISVs
   
   
7. Compact Edition
   A free, SQL Server embedded database ideal for building stand-alone and occasionally connected applications for mobile devices, desktops, and web clients
   
   
8. Evaluation EditionThis edition may be installed for demonstration and evaluation purposes until an expiration period of 180 days.

Here are the different editions available for SQL2008 R2

1. SQL Server 2008 R2 Parallel Computing Edition

One huge change is the Parallel Computing edition (formerly code-named Madison), essentially the SQL Server equivalent to Windows Server High Performance Computing (HPC) Edition. It will utilize a Massively Parallel Processing (MPP) scale-out architecture to support data warehousing ranging from 10TB to 1+ PB. The Parallel Computing Edition is priced at $57,489 per processor.

2. SQL Server 2008 R2 Datacenter Edition

The biggest change is undoubtedly the new SQL Server 2008 R2 Datacenter Edition. This edition parallels Windows Server Datacenter Edition, and with support from up to 256 logical processors, it's designed to address the highest levels of scalability. The Datacenter edition has no memory limitation and offers support for more than 25 instances. It also will be priced at $57,489 per processor.

3. SQL Server 2008 R2 Enterprise Edition and Developer Edition

The Enterprise edition is the one that’s targeted toward most businesses. With the advent of the new Datacenter edition, the Enterprise edition will be limited to support for 64 logical processors and 25 instances, which really isn’t much of a change. It will be priced at $28,749 per processor or $13,969 per server with 25 CALs. The Developer edition shares the same feature set and is licensed at $50 per developer.

4. SQL Server 2008 R2 Standard Edition
Designed for small and medium businesses, the Standard edition supports up to 4 CPUs and 2TB of RAM, with unlimited database size. It includes support for all BI subsystems including Analysis Services, Integration Services, and Reporting Services, but lacks many of the enterprise-oriented features from the Enterprise edition. It will be priced at $7,499 per processor or $1,849 per server with five CALs.

5. SQL Server 2008 R2 Web Edition

This edition is designed to be run by web hosting providers. It provides support for up to 4 CPUs, 2TB of RAM, and unlimited database size. In addition to its relational database engine, SQL Server 2008 R2 Web Edition supports Reporting Services. It's priced at $15 per processor per month.

6.     SQL Server 2008 R2 Workgroup Edition

Workgroup edition is designed to provide basic relational database services for departments and branch offices. It supports a maximum of 2 CPUs, 4GB of RAM, and unlimited database size. Its only BI feature is Reporting Services. It's priced at $3,899 or $739 per server including five CALs.

7. SQL Server 2008 R2 Express Edition

Targeted toward developers and ISVs, SQL Server Express Edition comes in three versions. SQL Server 2008 R2 Express provides relational database services; SQL Server 2008 R2 Express with Tools Edition adds SSMS; and SQL Server 2008 R2 Express with Advanced Services adds SSMS and a local instance of Reporting Services. All of the SQL Server 2008 R2 Express editions will continue to be free downloads

A page which tells you all released ServicePack/CumulativeUpdate/Hotfix for all versions of SQLServer

A page which tells you all released ServicePack/CumulativeUpdate/Hotfix for all versions of SQLServer

Following link is an one stop shop, which gives you up to date information about ServicePack/CumulativeUpdate/Hotfix all versions of SQLServer.

http://sqlserverbuilds.blogspot.in/

 

Free Download SQLServer 2012 Express edition (5 Flavors)

Free Download SQLServer 2012 Express edition

SQLServer2012 Express, is a relational database management system and it is a freely downloadable and distributable version of Microsoft's SQL Server, it comprises a database specifically targeted for embedded and smaller-scale applications. It includes 10GB of storage per database, easy backup and restore functionality and compatibility with all editions of SQL Server and Windows Azure SQL Database.

SQLServer2012 express is available in 5 different flavors.

The best flavor is Express with Advanced Services because it has got tools, reporting services

and Fulltext search.

Express (Containing only the database engine)

The core Express database server. Use this if you need to accept remote connections or administer remotely.

Express with Tools (with LocalDB, Includes the database engine and SQL Server Management Studio Express)

This package contains everything needed to install and configure SQL Server as a database server. Choose either LocalDB or Express.

SQL Server Management Studio Express (Tools only)

This package does not contain the database, only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure, etc. Use this if you already have the database and only need the management tools.

Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search)

This package contains all the components of SQL Server Express. This is a larger download than “Express with Tools,” as it also includes both Full Text Search and Reporting Services.

https://www.microsoft.com/betaexperience/pd/SQLEXPAS/enus/

Here is the link to download all flavors of SQLServer2012 Express

http://www.microsoft.com/sqlserver/en/us/editions/2012-editions/express.aspx

 

How to change default backup location after installing SQLServer 2008/R2/2012?

How to change default backup location after installing SQLServer 2008/R2/2012?

Changing default locations of Data and Log file is simple, connect to SQL instance through SSMS and go to properties of the instanceà Database Settings àunder Database Default Locationsà type the desired locations.

But if you want to change the default backup location after installation you need to connect to SQL instance through SSMS and go to properties of the instanceàFacets à Select Server Facets from the drop down list and click on BackupDirectory -à type the desired location.

You can also change the data & log file locations through facets

Service Pack -1 for SQLServer 20012 is available

Service Pack -1 for SQLServer 20012 :

Microsoft has released Service Pack -1 for SQLServer 2012 on 12th November 2012, as a best practice, deploy any new SPs first on lower (non-prod) environments and do some basic smoke test and later deploy it on Production servers if no issues found.

SQL Server 2012 SP1 contains fixes to issues that have been reported through our customer feedback platforms and Hotfix solutions provided in SQL Server 2012 Cumulative Update 1 and Cumulative Update 2. Service Pack 1 also includes supportability enhancements and issues that have been reported through the Windows Error Reporting system.

This upgrade is free and doesn’t require an additional service contract. Both, the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. This includes new Slipstream installation packages that can be utilized with existing licenses to install (or upgrade to) new instances with SP1 pre-installed.

You can download SP1 from here:
http://www.microsoft.com/en-us/download/details.aspx?id=35575

How to add exceptions in Windows Firewall related to SQLServer through command prompt?

How to add exceptions in Windows Firewall related to SQLServer through command prompt?

What is WindowsFirewall:

Windows Firewall is a software component of Microsoft Windows that provides firewalling and packet filtering functions. It was first included in Windows XP and Windows Server 2003. Prior to the release of Windows XP Service Pack 2 in 2004, it was known as Internet Connection Firewall.

Basically we have to open 3 exceptions in windows firewall pertain to SQL Server.

Ø  SQLServerEngine  -  default port/ custom port, Port Type - TCP/IP

Ø  SQLServerSSIS – 135, Port Type - TCP/IP

Ø  SQLServerBrowser – 1434, Port Type – UDP

Following command will create exceptions in firewall pertain to SQL Server

netsh advfirewall firewall add rule name = SQLServerEngine dir = in protocol = tcp action = allow localport = 22001 remoteip = ANY profile = ANY

netsh advfirewall firewall add rule name = SQLServerSSIS dir = in protocol = tcp action = allow localport = 135 remoteip = ANY profile = ANY

netsh advfirewall firewall add rule name = SQLServerBrowser dir = in protocol = UDP action = allow localport = 1434 remoteip = ANY profile = ANY

How to change SQLPort number through a VBScript/SQLServerConfigurationManager ?

How to change SQLPort number through a VBScript/SQLServerConfigurationManager ?

After successful installation of SQL server every organization follows some set of security standard to secure Sql server. One of those standard is changing the sql default port to some other port(1433 is the default port. )

How would I do that: it is very simple you can do it either through script or GUI -SQLServerConfigurationManager.

You have to restart sql services then only the SQL server starts listening new port.

Following script has two sections, one is to change the port to 22001and the other section is to restart sql services (Engine and Agent). Copy the following script in to notepad and save as PortChangeScript.VBS and open commandprompt

C:\ PortChangeScript.VBS    Enter

-------------------------------------------------------------------------------------------------------------------------------------------.

set wmiComputer = GetObject( _

    "winmgmts:" _

    & "\\.\root\Microsoft\SqlServer\ComputerManagement10")

set tcpProperties = wmiComputer.ExecQuery( _

    "select * from ServerNetworkProtocolProperty " _

    & "where InstanceName='MSSQLSERVER' and " _

    & "ProtocolName='Tcp' and IPAddressName='IPAll'")

for each tcpProperty in tcpProperties

    dim setValueResult, requestedValue

    if tcpProperty.PropertyName = "TcpPort" then

        requestedValue = "22001"

    elseif tcpProperty.PropertyName ="TcpDynamicPorts" then

        requestedValue = ""

    end if

    setValueResult = tcpProperty.SetStringValue(requestedValue)

   

next

strComputer = "."

Set objWMIService = GetObject("winmgmts:" _

    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colServiceList = objWMIService.ExecQuery("Associators of " _

   & "{Win32_Service.Name='MSSQLSERVER'} Where " _

        & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

For each objService in colServiceList

    objService.StopService()

Next

Wscript.Sleep 20000

Set colServiceList = objWMIService.ExecQuery _

        ("Select * from Win32_Service where Name='MSSQLSERVER'")

For each objService in colServiceList

    errReturn = objService.StopService()

Next

Wscript.Sleep 20000

strComputer = "."

Set objWMIService = GetObject("winmgmts:" _

    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colServiceList = objWMIService.ExecQuery("Associators of " _

   & "{Win32_Service.Name='MSSQLSERVER'} Where " _

        & "AssocClass=Win32_DependentService " & "Role=Antecedent" )

For each objService in colServiceList

    objService.StartService()

Next

Wscript.Sleep 20000

Set colServiceList = objWMIService.ExecQuery _

        ("Select * from Win32_Service where Name='MSSQLSERVER'")

For each objService in colServiceList

    errReturn = objService.StartService()

Next

-------------------------------------------------------------------------------------------------------------------------------------

Other way: GUI- SQLServerConfigurationManager

Openà SQLServerConfigurationManagerà SQL Server Network Configuration à select the instance à double click on TCP/IP à click on IP Address Tab  à Scroll down and in section IP ALLà Type TCP Port =22001 and delete if you see anything in TCPDynamic Port and then click on apply then click on OK. Finally you have to restart SQL Services.

How to run a batch file or executable file through a Windows Service?

How to run a batch file or executable file through a Windows Service?

Some times we would like to run a batch file or exe file from windows service, this is very simple.

For example you have a batch file – ABC.bat and you want to run it from windows service, following is the command to do that.

Open command prompt.

C:\ABC.bat /R <Password to run the service as log on>

What is a Robocopy?

What is a Robocopy?

Robocopy, or "Robust File Copy", is a command-line directory replication command. It has been available as part of the Windows Resource Kit starting with Windows NT 4.0, and was introduced as a standard feature of Windows Vista, Windows 7 and Windows Server 2008. The command is robocopy

Sytanx to copy from one location to another location

set source=\\ABCServer\MyFolder
set target=E:\MyFolder
set logfile=D:\Logs\MyfolderCopy.log

ROBOCOPY "%source%" "%target%" /COPYALL /S /ZB /V /E /R:1 /W:3 /TEE /NP /XO /LOG:"%logfile%"

Or

ROBOCOPY <Source Path> <Destination Path  /COPYALL /S /ZB /V /E /R:1 /W:3 /TEE /NP /XO /LOG: <Log Path>

How do I list all the windows groups pertain to domain account/user?

How do I list all the windows groups pertain to domain account/user?

Some time we would like to know about all windows groups pertain to a Domain user.

Following command will list you all the windows groups pertain to xyz user

Syntax:

net user <Domain username> /Domain

net user xyz /Domain

How to kill a windows session – unable to logon windows……

How to kill a windows session – unable to logon windows……

We often face “Sessions has reached maximum limit” while connecting to a windows server to troubleshoot SQLServer/other applications, actually when you RDP to a window box, it will create a session with ID and windows allows only 2 remote connections, the next connection whoever tries to connect will get rejected. So as soon as we are done with our work on any windows box you have to log off the box otherwise that session will remain for some time based on the Administrator time setup.

So what do we do in case of sessions are already reached maximum limit.

First you have to list what are the connections are connected to server. Below command will list you that information.

Syntax: Qwinsta /server:<Servername>

Qwinsta /server:ABWZP4154

It displays two connection connected to ABWZP4154.

Connections are two types- Active and Disconnected

If you see disconnected connection, note down the session –ID and kill it by executing below command, after killing you are able to RDP to the server.

Syntax:

Rwinsta /server:<servername> <Session ID>

Rwinsta /server: ABWZP4154 2

Incase if you see both the connection are active then  you have to choose which to kill. It is up to you J

Recycle SQLServer Error Log file

Recycle SQLServer Error Log:

SQL Server error log captures and stores server level and db level information(including errors) and stores it in a file called “ERRORLOG” by default location is….

D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG

This will be re-created everytime whenever SQL Services are restarted and sometimes opening error log file takes longer time to open because it is very big, instead of maintaining one big errorlog file you can slim down by running below query.

sp_cycle_errorlog

To read current errorlog file

Xp_readerrorlog

To know the SQL Port through Query Analyzer

SQL Port:

 

Everyone knows about the default SQL port – 1433 but for security reasons most of us will change it to other number. We can check this either through Configuration Manager or Query Analyzer.

Following command will give you the port number in which the sql server is listening…..

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
 @rootkey = 'HKEY_LOCAL_MACHINE',
 @key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP', @value_name = 'TcpPort',
 @value = @tcp_port OUTPUT

select @tcp_port

To find out any traces are running on SQL Server?

To find out any traces are running on SQL Server?

Following query will list you the total number of traces (default and custom) which are running on a SQL server.

SELECT *

FROM fn_trace_getinfo(default);

GO

How to enable Agent XPs?

How to enable Agent XPs?:

When you restart SQL services, sometimes SQLServer Agent will not expand in SSMS, run following query to put the Agent back to online.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'AGENT XPs', 1;

GO

RECONFIGURE;

GO

Data Compression: What, types of compression and how to configure...

Data Compression:

Data Compression is a new feature introduced in SQL2008, this method compress database objects table/cluster-nonclusterIndex/Indexed View/Partitioned Tables in sizable manner, selection of compression will definitely improves the performance.

Let us dig in to the deeper like…

How does it work….

How many types of methodologies…..

Would this impact performance of my server……

How does it work?

Data compression is entirely handled under the covers by the SQL Server Storage Engine. When data is passed to the Storage Engine, it is compressed and stored in the designated compressed format (on disk and in the Buffer Cache). When the Storage Engine passes the information to another component of SQL Server, then the Storage Engine has to uncompress it. In
other words, every time data has to be passed to or from the Storage Engine, it has to be compressed or uncompressed. While this does take extra CPU overhead to accomplish, in many cases, the amount of disk I/O saved by compression more than makes up for the CPU costs, boosting the overall performance of SQL Server

Let’s say that we want to update a row in a table, and that the row we want to update is currently stored on disk in a table that is using row-level data compression. When we execute the UPDATE statement, the Relational Engine (Query Processor) parses, compiles, and optimizes the UPDATE statement, ready to execute it. Before the statement can be executed, the Relational Engine needs the row of data that is currently stored on disk in the compressed format, so the Relational Engine requests the data by asking the Storage Engine to go get it. The Storage Engine (with the help of the SQLOS) goes and gets the compressed data from disk and brings it into the Buffer Cache, where the data continues to remain in its compressed format.

Once the data is in the Buffer Cache, the row is handed off to the Relational Engine from the Storage Engine. During this pass off, the compressed row is uncompressed and given to the Relational Engine to UPDATE. Once the row has been updated, it is then passed back to the Storage Engine, where is it again compressed and stored in the Buffer Cache. At some point, the row will be flushed to disk, where it is stored on disk in its compressed format.

Data compression offers many benefits. Besides the obvious one of reducing the amount of physical disk space required to store data—and the disk I/O needed to write and read it—it also reduces the amount of Buffer Cache memory needed to store data in the Buffer Cache. This in turn allows more data to be stored in the Buffer Cache, reducing the need for SQL Server to access the disk to get data, as the data is now more likely to be in memory than disk, further reducing disk I/O.

Just as data compression offers benefits, so it has some disadvantages. Using compression uses up additional CPU cycles. If your server has plenty to spare, then you have no problem. But if your server is already experiencing a CPU bottleneck, then perhaps compression is better left turned off.

How many types of methodologies:

Ø  Row-level Data Compression: Row-level data compression is essentially turning fixed length data types into variable length data types, freeing up empty space. It also has the ability to ignore zero and null values, saving additional space. In turn, more rows can fit into a single data page.

o    Reducing the amount of metadata used to store a row.

o    Storing fixed length numeric data types as if they were variable-length data types. For example, if you store the value 1 in a bigint data type, storage will only take 1 byte, not 8 bytes, which the bigint data types normally takes.

o    Storing CHAR data types as variable-length data types. For example, if you have a CHAR (100) data type, and only store 10 characters in it, blank characters are not stored, thus reducing the space needed to the store data.

o    Not storing NULL or 0 values

o    Row-level data compression offers less compression than page-level data compression, but it also incurs less overhead, reducing the amount of CPU resources required to implement it.

Ø  Page-level Data Compression: Page-level data compression starts with row-level data compression, then adds two additional compression features: prefix and dictionary compression. We will take a look at what this means a little later in this chapter. As you can imagine, page-level compression offers increased data compression over row-level compression alone

Page-level data compression offers greater compression, but at the expense of greater CPU utilization. It works using these techniques:

• It starts out by using row-level data compression to get as many rows as it can on a single page.
• Next, prefix compression is run. Essentially, repeating patterns of data at the beginning of the values of a given column are removed and substituted with an abbreviated reference that is stored in the compression information (CI) structure that immediately follows the page header of a data page.
• And last, dictionary compression is used. Dictionary compression searches for repeated values anywhere on a page and stores them in the CI. One of the major differences between prefix and dictionary compression is that prefix compression is restricted to one column, while dictionary compression works anywhere on a data page.
• The amount of compression provided by page-level data compression is highly dependent on the data stored in a table or index. If a lot of the data repeats itself, then compression is more efficient. If the data is more random, then little benefits can be gained using page-level compression

Implementation:

Data compression can be performed using either SQL Server Management Studio (SSMS) or by using Transact-SQL.

First step is to perform estimation,

Out of the box there are two ways to estimate compression. I suspect the both use the procedure sp_estimate_data_compression_savings.

sp_estimate_data_compression_savings

sp_estimate_data_compression_savings

     [ @schema_name = ] 'schema_name' 

   , [ @object_name = ] 'object_name'

   , [@index_id = ] index_id

   , [@partition_number = ] partition_number

   , [@data_compression = ] 'data_compression'

[;]

The following example estimates the size of the start.WorkOrder table if it is compressed by using ROW compression.

USE ABC;

GO

EXEC sp_estimate_data_compression_savings 'start', 'WorkOrder', NULL, NULL, 'ROW' ;

GO

Management studio can do an estimation. Right click on the table, choose Storage and Manage Compression:

Then choose the compression you are interested in and click calculate.

There is also a tool on Codeprex that will estimate all tables in a database. This is handier then a table by table estimate.

I just tried this tool. The estimation worked great. There isn’t support for these tools though.

This page is helpful. http://ssce.codeplex.com/documentation

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

SQL Server Compression Estimator http://ssce.codeplex.com/