Introduction of SQL Server 2012

Hi All,

let's talk about SQL server 2012  as it's a new trend in  SQL server products and released with new features.
SQL Server 2012 released on April 1,2012.
Now We have SP1 for SQL Server 2012. In RTM there were major issues and those were fixed in SP1(Error : Unable to creating the Packages and Maintenance Plan in SQL server 2012 RTM)

Two types of licensing is available for SQL Server 2012 Enterprise edition: Core-Based Licensing and Server+CAL Licensing.

Prerequisites for Installing the SQL server 2012.
1) .Net Frem Work 3.5 SP1
2)SQL Server Native Client
3)SQL Server Setup support files
 4)SQL server 2012 runs on computer with NTFS file systems and  also runs on FAT32 file systems but it's not recommend as it's less secure than NTFS file systems.
5)SQL Server 2012 requires a minimum of 6 GB of available hard-disk space.
6)Internet Explorer 7 or a later version is required for Microsoft Management Console (MMC), SQL Server Data Tools (SSDT), the Report Designer component of Reporting Services, and HTML Help.
7)Windows PowerShell 2.0 is an installation prerequisite for Database Engine components and SQL Server Management Studio

• If you install SQL Server 2012 on a computer with the Windows Vista SP2 or Windows Server 2008 SP2 operating system,
  
• If you install SQL Server 2012 on a computer with the Windows 7 SP1 or Windows server 2008 R2 SP1 or Windows Server 2012 or Windows 8 operating system,
  

SQL Server 2012 supports upgrade from the following versions of SQL Server:

• SQL Server 2005 SP4 or later
  
• SQL Server 2008 SP2 or later
  
• SQL Server 2008 R2 SP1 or later
  
  
  • SQL Server 32-bit editions can be upgraded to SQL Server 2012 on the 32-bit subsystem (WOW64) of a 64-bit server.
    
  • SQL Server 64-bit versions can be upgraded to SQL Server 2012 64-bit server only.
    
  
    
  Note :  SQL Server 2012 supports upgrade from only the following versions: SQL 2005 SP4 or SQL 2008 SP2 or SQL 2008 R2 SP1. You will be not able to migrate from SQL 2000 to 2012 directly. Migration from 2000 to 2012
  
  You will have to make the migration in two steps:
   Step 1: Make a first migration from SQL 2000 to SQL 2008 for instance. You need to be SQL 2000 SP4, then follow this step : Migration SQL Server 2000 to SQL Server 2008  Step 2: Make a second migration from SQL Server 2008 to 2012.
  
  
  The following scenarios are not supported for SQL Server 2012 failover clusters.
  
  • SQL Server 2012 Enterprise to SQL Server 2012 Developer, Standard, or Enterprise Evaluation.
    
  • SQL Server 2012 Developer to SQL Server 2012 Standard or Enterprise Evaluation.
    
  • SQL Server 2012 Standard to SQL Server 2012 Enterprise Evaluation.
    
  • SQL Server 2012 Enterprise Evaluation to SQL Server 2012 Standard.
    

SQL Server Database Engine object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)

Database size	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes

Rows per table Limited by available storage Limited by available storage Tables per database3 Limited by number of objects in a database Limited by number of objects in a database Partitions per partitioned table or index    1,000 Important Creating a table or index with more than 1,000 partitions is possible on a 32-bit system, but is not supported. 15,000

Commands for getting cluster stats as well as Fail over information

Hello ,

Below listed commands ,we can use in SQL Cluster Environment instead of GUI.

1) View SQL Clusters

Run --> CMD

          cluster /list

2) View Status of Nodes

           cluster node 

            -or 

           cluster node /status

3) View the cluster Groups 

                    Cluster Group 

                         or 

                cluster Group /status

4) View Status of Cluster Networks

                    Cluster Network 

               or 

                 Cluster Network /Status

5) View Properties of All Network Interface Devices

                    Cluster netinfterface 

                         or

                    Cluster netinterface /status

6) View the listing of all available cluster resources.

             cluster resource 
                --or 
             cluster resource /status 

                 or

             cluster res

7) Fail over the resource to New node

           cluster group "groupname" /move:nodeName

Thanks

A.S.Reddy  

Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed ,SQL Server 2005

One of the  maintenance plan has been failing with below error Message.

Error Message “Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.”

So I started the  investigation what is missed  in the maintenance plan.

I saw that in the job step where the information for the maintenance plan is mentioned the ‘\’ at the beginning of the plan name was missing.

 

The typical plan names are mentioned as

\Maintenance Plans\Full Backups

 

But this time it was mentioned as

Maintenance Plans\Full Backups

So I added the ‘\’ at the beginning of the plan name in the job step after that maintenance plan executed successfully.

Thanks

ASR

 

MSDB size Growing too Bid...

I usually  faced this issue in my environment.Customer has dropped mail about why MSDB database growing to big..After that I logged into the server and checked the MSDB Database Size.It was occupied 20 GB.
Then I started investigation why MSDB database is growing.

Here is the simple information ..I found to resolve the issue.

MSDB Database Size

After some time of investigation.I have checked the sizes of the Objectes in MSDB Database.
Here is the script to find out the Objects sizes details in MSDB Database.
---------
select
        object_name(i.object_id) as ObjectName,
        i.[name] as IndexName,
        sum(a.total_pages) as TotalPages,
        sum(a.used_pages) as UsedPages,
        sum(a.data_pages) as DataPages,
        (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
        (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
        (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
    from
        sys.indexes i
        inner join sys.partitions p
            on i.object_id = p.object_id and i.index_id = p.index_id
        inner join sys.allocation_units a
            on p.partition_id = a.container_id
    group by
        i.object_id,
        i.index_id,
        i.[name]
    order by
        sum(a.total_pages) desc,
        object_name(i.object_id)
    go
----
Output of the object details..
-===============


 Based on the output SYSSSISLOG Object stored lots of data since 2010.Packges executeion information tracked into this object. so that MSDB growing to big.
After That we have created Job for deleted old entries...Then Shrinked the MSDB.Then controlled size.
.

DBCC SHRINKDATABASE('MSDB')

For growing of the MSDB Database may be caused by Database Mail ,backup and Restore history Objects..Because these objects will be tracked backup,restore and Mails operations history in MSDB .So that MSDB database growing too big...

Thanks
ASR