Issue 1: SQL service did not turn up after the Service pack Upgrade and received the message at Event viewer “service specific error code 3417”
To resolve the issue we have to repair the sql server service.
- Launch the SQL Server 2012 Setup program (setup.exe) from the distribution media.
- In the left navigation area, click Maintenance, and then click Repair.
- Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
- On the Select Instance page, select the instance to repair, and then click Next.
- The repair rules will run to validate the operation. To continue, click Next.
- From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.
The important note is the sql server is appeared along with all existing user databases so safely we can go for the existing environment. Earlier we have to attach the user database again after rebuild the system databases but in 2012 no need to worry for the same. As a safety you can copy the data file and log files for user database and msdb database. The repair command rebuilds the system databases and the above option applicable if you don’t have the master database backup. Once the repair has been done successfully afterwards you have to start the sql server service manually from configuration manager.
Issue 2: : The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.
To resolve the above issue we have to install the Visual studio service pack. It is a known bug in SQL 2012 SP1 and it forces the need for a complete install of Visual Studio 2010. The version that is installed I don’t think will work.
Note: SQL cannot be updated until Visual Studio is installed, and SP1 applied to VS2010. Then we can update SQL to SP1. Reference link http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/2867fe21-6414-4bd9-879e-ded00716ba70
Since couple of month I have started using the SQL server 2012 extensively and here everybody curious about that SQL server 2012 Business Intelligence Edition and what is additional point keep it in mind during Installation
Microsoft has changed the available editions with the launch of SQL Server 2012. A new Business Intelligence Edition now joins Standard and Enterprise; however if you are looking for Datacenter, Workgroup, or Web Editions, you will not find them as Microsoft has dropped them. Developer and Express Editions are still very much alive.
If you do not want the whole feature set that Enterprise Edition offers, but need to do some business intelligence gathering and processing, then this new edition may do the job. The database engine supports up to 16 cores, while SSRS and SSAS can use the maximum that your OS supports.
It am bit surprised that columnstore indexes feture is not supported on the 2012 Business Intelligence Edition as these new indexes will bring performance gains to any data warehouse. However, if you decide on Business Intelligence Edition, you will benefit from most of the other BI features available in Enterprise Edition including Power View, PowerPivot for SharePoint Server, Data Quality Services (DQS) and Master Data Services (MDS).
Installation Future change
Once your Windows server is prepared, double-click on the setup.exe file to begin installing SQL Server. On the Planning screen, For a new installation, click on the top
link: New SQL Server stand-alone installation…This will start the setup process:
Enter your license key number or choose to run it as an evaluation On the next screen click to accept the software license terms. When you arrive at the Setup Support Rules screen, you will need to address any reported issues before proceeding.
Once you are on the Setup Support Role screen, leave the first option of SQL
Server Feature Installation checked, unless you want to change it, and click on Next.
Business Intelligence Developer Studio (BIDS) has been replaced with SQL Server Data Tools, which is used to develop reports, create packages, and build Analysis Services objects such as cubes and dimensions.
If you are installing Analysis Services, then you will get the so the screen for SSAS options appears. Choose your server mode, either Multidimensional and Data Mining Mode (your analytical objects are stored in the OLAP engine and queried using MDX) or Tabular Mode (your analytical objects are stored in the xVelocity engine and queried using DAX). If you are unsure, choose the default and if you need to run both modes, you can later install a separate instance to run in the other mode.
Here I have mentioned some reasons why SQL Server Always On technology is a good high availability option as compared with other high availability features like Mirroring, Log shipping and clustering.. SQL Server 2012 now has Always On technology, where the database can keep mirror copy/copies of the database that are constantly being refreshed. In the event of a failure of the Primary, it will seamlessly fail over to another copy. SQL Server 2012 Always On is employs mirroring and clustering together to achieve the high availability
SQL Server 2012 Always On — SQL Server Mirroring
This has significant disadvantages over traditional SQL Server Mirroring.
- In traditional mirroring we cannot manage groups of databases, so that when a single database failed they would all failover.
- There is only one Mirror of the database allowed
- The Mirror is not useable unless you are prepared to work with Snapshots.
- In SQL Server 2012 Always On Technology, we have Availability Group option, using which Databases can now be grouped together. When a failure happens, the entire group of database is treated as one. When one fails over they all fail over. This is very powerful when you have an application that uses more than one database in an instance.
SQL Server 2012 Always On — SQL Server Log Shipping.
Log shipping is sort of manual mirroring which allows more than one replica to be kept; perhaps a local one and a remote one. This is more difficult to setup and failover is not automatic The biggest downfall is we cannot use the receiving database since it is in a recovery state and read-only as the transaction logs are being applied. In SQL Server 2012 Always On Technology all the databases can failover automatically.
SQL Server Always On Versus SQL Server Clustering.
With SQL Server clustering, you are dealing with a shared storage system that adds cost and complexity to the equation. AlwaysOn doesn’t need to use shared storage. So here it is more cost effective.