Upgrading SQL Server.

When upgrading SQL Server there is limitation on the direct upgrade possible i.e. you cannot upgrade SQL Server 2000 directly to SQL Server 2012 or later versions. And you cannot upgrade from SQL Server 2000 to 2008 or 2008R2  if you are not running on SP4.

For quick reference here is the table showing which is the SQL Server version you can upgrade from and also what is minimum requirement of Service Packs even if you can upgrade.

From\To SQL Server 2000 SQL Server 2005 SQL Server 2008 SQL Server 2008 R2 SQL Server 2012 SQL Server 2014 SQL Server 2016
SQL Server 2000 SP3+ SP4+ SP4+  Not Possible Not Possible Not Possible
SQL Server 2005 N/A RTM+ SP2+ SP4+ SP4+  Not Possible
SQL Server 2008 N/A N/A RTM + SP2+ SP3+ SP3+
SQL Server 2008 R2 N/A N/A N/A SP1+ SP2+ SP2+
SQL Server 2012 N/A N/A N/A N/A SP1+ SP2+
SQL Server 2014 N/A N/A N/A N/A N/A RTM+
SQL Server 2016 N/A N/A N/A N/A N/A N/A

Securing SQL Server.

  1. Physical Security

It’s important for physical and logical securing the server. Secure the physical access to the server by allowing only the authorized persons to the place of the server.

The servers should not be directly connected to the internet.

No matter how much secure you make the SQL Server if it is not physically secure than everything else is of no use.

  1. Securing the data and log files.

OS needs to be installed with latest patches and security updates as they help in fixing the loopholes and bugs which are exploited to reach the important files.

Its important to follow the least privilege account for service accounts and any other account as well.

Provide the permissions to the Data and log files only to the users or groups which require access to these. Such as SQL Server service accounts. Service account used by any third party tool for backing up the database files.

Use the NTFS file system instead of FAT32, and is preferred for the installation of SQL Server. As its more secure.

Use RAID for securing the data files against any HDD related hardware failure.

Firewalls are important to secure the network. Database servers, application servers, Clients, SAN and many things more are all on network so it is important to limit the access to the network by firewall.

Only allow the defined ports for SQL Server through firewall. Such as 1433,1434 and so on.

  1. Service Accounts

Isolate the service accounts used in SQL Server and provide only those permissions and access rights to the accounts that are required i.e. least privilege required.

  1. Authentication mode\ Strong passwords.

Its preferred to use Windows authentication for connecting to SQL Server.

When using SQL Server authentication use strong password. Check Enforce password policy and Enforce password expiration.

Note: It’s not preferred to enforce password expiration for the logins which are used as service accounts

  1. Surface Area Configuration.

Using sp_configure disable the features and components that are not required in the environment. Lesser the features enabled lesser the area to secure. Some features that needs to be disabled if not used are: Database Mail XPs, cross db ownership, xp_cmdshell Ad hoc distributed queries and more.

 

  1. Server and Database Roles

Assign appropriate Server roles to logins as they provide access on instance level and thus should be assigned to the logins carefully.

To control the access and permissions on database level use database roles. Provide appropriate permissions to users so that they limited permissions based upon there requirement.

  1. Encryption

SQL Server provides various encryption options to choose from like TDE, always encrypted, column level encryption.

 

How To: Rebuild Log when SQL Server Services were not properly closed and log file is deleted.

Rebuilding Log when SQL Server Services were not properly closed and log file is deleted.

  1. We have a database that is in a “Recovery Pending State”.

1

We can see “AdventureWorks2012” is in Recovery Pending state.

We can check the logs to see what happened.

2

  1. Put the database in Emergency mode.

3

4

When the database is in Emergency mode we can only read the data from the database. As the Startup Recovery was not done the database is in inconsistent state.

  1. To bring back the database ONLINE. (Remember it is inconsistent and SHOULD NOT be used in production). Run DBCC CHECKDB with REPAIR ALLOW_DATA_LOSS.

But before running DBCC CHECKDB we have to put the database in SINGLE_USER mode. Because Emergency is a MULTI_USER mode and REPAIR_ALLOW_DATA_LOSS needs a database that is in SINGLE_USER mode.

5

Now run DBCC CHECKDB

6

The Message is:

File activation failure. The physical file name “C:\AdventureWorks2012_Database\AdventureWorks2012_log.ldf” may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Warning: The log for database ‘AdventureWorks2012’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

We can see the message that Log has been rebuilt.

7

Put the database back in MULTI_USER mode.

8

NOTE: DATABASE IS STILL CORRUPT AND SHOULD NOT BE USED IN PRODUCTION AS IT COULD BE INCONSISTENT.

How to rebuild Master database.

The need to rebuild Master database comes when it is corrupted and we are not able to start SQL Server Service and getting error like this:

 

2016-01-01 21:21:20.19 spid5s      Starting up database ‘master’.

2016-01-01 21:21:20.23 spid5s      Error: 5172, Severity: 16, State: 15.

2016-01-01 21:21:20.23 spid5s      The header for file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’ is not a valid database file header. The PageAudit property is incorrect.

2016-01-01 21:21:20.25 spid5s      Error: 5173, Severity: 16, State: 1.

2016-01-01 21:21:20.25 spid5s      One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

2016-01-01 21:21:20.26 spid5s      Error: 5173, Severity: 16, State: 1.

2016-01-01 21:21:20.26 spid5s      One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.

 

Or any other error specifying that Master database is corrupt.

If any user database gets corrupt than we can restore that database, as the services will not be hampered but that’s not the case with Master database.

We don’t have any other option left than rebuilding the Master database and then restoring the latest backup on it.

  1. Open Command prompt with administrator privileges. By right clicking on cmd and clicking “Run as administrator”.

1

  1. Now we have to go to the setup directory in cmd.

The “setup” is in the C:\Program Files\Microsoft SQL Server\110\ Setup bootstrap\SQLSERVER2012 directory.

The location of “Setup.exe” in different version of SQL Server is almost same (keeping in mind that the directory for 2005 is 90, 2008 is 100, 2012 is 110, 2014 is 120 and 2016 is 130).

As I am working in SQL Server 2012 that’s why the location of setup.exe is “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012”.

2

To go to this directory use ‘cd’ command.

3

  1. To rebuild the master database, we have to use ‘setup’ with some additional parameters.

/ACTION=REBUILDDATABASE

/SQLSYSADMINACCOUNTS= Provide the account name which have administrator privileges.

/SAPWD= <Strong Password>If you are using mixed authentication mode than provide a strong sa password.

/Q For silent mode without user interaction.

/INSTANCENAME= <Name of the instance of SQL Server if its default than MSSQLSERVER>

As I am rebuilding default instance that is mixed authentication mode the command I need to run is:

4

If you don’t get any message and just moves to the next line that means everything’s good and the Master database has been rebuilt.

To verify check the SQL Server Services from configuration manager. It will be running without any issue this time.

5

But when you open SQL Server Management studio and will find that all the user databases are gone.

6

That’s because as your master database has been rebuilt there is no information in it about the user databases. As it’s the master database that holds this information.

Now we have to restore the master database from a backup.

  1. To restore master database, we have to open the SQL Server services in the single user mode. For that add ‘-m’ in the startup parameter of SQL Server service.

Open SQL Server Configuration manager. Right Click the instance and click properties. Go to Startup parameters tab. In ‘Specify a startup parameter’ write ‘–m’. And click ‘Add’.

7

  1. We have to use the SQLCMD utility for restoring the master database. Open command prompt with administrative privileges. And write SQLCMD if its default instance but if it is named instance than we have to provide the name of the instance by using –S flag.

8

Now Restore the master database.

9

AS you can see when taking backup I named the backup file as .bac (By mistake). But it still it restores successfully as the extension doesn’t matter.

Remove –m from the startup parameter and start the SQL Server Services. Check SSMS for the verification.

10

Now we have all our beloved user databases. J

Moving Tempdb database data and log files

  1. Find out the location of Tempdb Data and Log file.

1

  1. We cannot move the tempdb by detach and attach method or backup and restore method.

We can move the Tempdb by Alter Database method.

 

2

  1. Restart SQL Server Service. Using SQL Server Configuration manager.

3

  1. Now time to verify the new location of tempdb files.

4

  1. Delete the Old tempdb files.

How To Attach a Database Without a Log File.

To attach a database for which we have only the .mdf file and not .ldf file of the database. The methods will work only when the database has been detached properly.

Method 1

1. We only have a .mdf file

1

2. Right click “Databases” and click “Attach” in the object explorer.

2

3. Now click on “Add”. And browse to the location of the .mdf, select it and click “OK”.

3

4. Now in the database details it will show the message the message “Not Found” for the Log file.

4

5. Click in the Log file and then “Remove”.

5

The file will be removed.

6

6. Now click “OK”. It will attach the database and will rebuilt the Log.

7

8

Method 2.

The above step actually runs the Create Database query. So instead of doing it in GUI you can write it the query as:

 

We can also use sp_attach_single_file_db. But as per BOL this feature will be removed from SQL Server 2016 onwards and CREATE DATABASE with FOR ATTACH does the same thing, so that’s why not adding it in the method.

NOTE: This method only works when the database had only 1 Log file.

When I add one more log file to the database and try to attach this database by the above two methods I get the following errors.

When using GUI.

9

As GUI does the same thing as running the query so its obvious that we will get the error when running the Create database query.

10

If you are thinking that might be sp_attach_single_file_db will be helpful here.

11

Answer is NO.

The only method I know off to attach this database is using CREATE DATABASE with FOR ATTACH_REBUILD_LOG.

12

Attaching the database creates only 1 Log file instead of two.

13

So if you really need two log files than create it.

Moving Master Database

Open the SQL Server configuration manager.

1

We are moving the master database of Default Instance. Right click on the SQL Server service and open properties.

2

Go to “Startup Parameters”.

3

In the startup parameters ‘-dC:\Program Files….’ is the directory of the master database data file. Click on the existing parameter for data file, change the directory and click “Update”.

4

“-lC:\Program….” Is for the directory of transaction log file of master change we have to relocate that too to the new location. Click “Update”.

5

When we “Apply” the changes we get warning stating that changes will be only be applied once we restart the services.

6

Stop SQL Server Service.

7

Move the Master database (both .mdf and .ldf file) from the old location to the new location stated in “Startup Parameters”.

8

8b

Start the SQL Server service.

9

To verify the results run “Select * from sys.master_files”. Check the location of master database.

10

Recovering Suspect Database.

When a database is in SUSPECT state that means SQL Server knows that the database is Inconsistent.

The best thing to do when you have a SUSPECT database is Restore a FULL backup. But if you are not having Backups (For the reason only God and you knows) than the only option left is to get the most you can and compromise in the consistency of the database.

There could be a case that you have to extract some data from your suspect database that you know is consistent and your backup is not having that info. Example a table you created after the backups and want that table in your restored database.

The steps to access the SUSPECT database are:

  1. Make sure that the database you are having is in SUSPECT mode.

1

  1. As the database is in SUSPECT mode that means we cannot access it. The best to do as mentioned earlier is Restore it from a Full backup. But if you don’t have it or you need something from this one. Than set the database is EMERGENCY mode.

2

You can see that the database is in EMERGENCY Mode.

3

 

  1. We have to run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. But as Emergency mode is a MULTI USER mode we have to set the the database in SINGLE USER mode.

4

  1. Now as the database is set in SINGLE_USER mode we can run DBCC CHECKDB on it.

5

The Information that we got is:

 

File activation failure. The physical file name “C:\AdventureWorks2012_Database\AdventureWorks2012_log.ldf” may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Failed to restart the current database. The current database is switched to master.

Warning: The log for database ‘AdventureWorks2012’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x0c660550; actual: 0x0c668550). It occurred during a read of page (2:0) in database ID 5 at offset 0000000000000000 in file ‘C:\AdventureWorks2012_Database\AdventureWorks2012_log.ldf’.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

  1. Set the database back to MULTI_USER mode.

6

Check the status of the database to confirm that it is ONLINE.

7

 

SQL Server Broswer and SQL writer skipped when Uninstalling SQL Server

Sometimes when you uninstall SQL Server you’ll see that SQL Server Broswer and SQL writer are skipped.

1

This nothing to be worry about as SQL Browser and SQL Writer are shared features of SQL Server, nad if they are skipped that means that there are more instances of SQL Server installed in your environment.

They will be successfully remove only with the uninstallation of the last instance of SQL Server in your server.

The different editions of SQL Server 2012

The edition of SQL Server “made” for you is the one that fulfills your needs. And the best to place for finding the needs that can be fulfilled by Enterprise, Standard, Express and Business Intelligence editions is msdn itself. The link could be find below.

But still just to give a brief of all these:

Express Edition:

The best thing about express edition is that its free. Yes FREE. J . (sorry have to add a but) But the features are limited by limited I mean way too limited in comparison to Enterprise. That doesn’t mean that it its not useful.

Thers no need to buy pricey editions if you just want a server which can hold your subscriber database for replication.

You can run DML and DDL queries in it.

If you need a server to monitor your database mirroring than Express edition can do that for you.

You can create reports, yes by Reporting services, but only for the local databases and that too can be stored locally only. But still creating reports of your database is a benfit considering its FREE. (Like many I love free things)

But obviously there are limitations in SQL Express, if there weren’t who will buy Enterprise. J

There are many but stating some important ones: (For the rest refer the link at the end)

You can have a database of only 10GB size.

Express edition supports 1 core and 1 GB RAM only.

There are no High availability features.

No SQL Server Agent. That means no jobs, no Database mail, no alerts and everything else which requires SQL Server Agent.

But if you come across a need to upgrade your express edition to some other one that could be done.

Standard Edition:

It is a bit serious business SQL Server edition. And by that I mean it will cost you money.

It provides the features of Express edition and many  more above that. You should choose this edition when you need:

The maximum size of the database can be 524PB.

A Server which you know that will not need more than 16 cores processor and 64 GB memory.

You will have High availability features like Log shipping; Database mirroring, Replication and Clustering.

SQL Server Agent service is available in Standard edition.

SSIS, SSRS, SSAS obviously with some limitations. But it still can satisfy the needs of many.

The limitations of Standard edition are:

Only 16 Core and 64 GB support, now a days that could not be sufficient for your production server.

You can set DM in safety only mode.

No Peer-to-Peer replication.

No Transparent database encryption.

That means if you don’t need advanced features of SQL Server (such as Data compression, Bakcup compression ,indexed views, Database snapshot,  Table and index partitioning, Resource governor and much more) than Standard edition is enough for you.

Remember that Standard edition is much much cheaper than Enterprise. That’s why double and triple check your requirements and if you don’t need the advanced features of SQL Server go with Standard edition.

Standard edition licensing is done both on core and Server + CAL basis.

Business Intelligence Edition:

As said by Microsoft, “A comprehensive platform empowering organizations to build and deploy secure, scalable and manageable BI solutions.”

That means it is made for BI. It has all the features of Standard edition plus some additional features for Business Intelligence like BI Semantic Model (Tabular).

It has all the advanced features of Analysis services.

The 16 Core and 64 GB limitation is limited only to Database Engine. That mean SSAS and reporting services can use the OS maximum.

Master Data Services.

Its limitations:

As the name states it is for Business Intelligence. It has all the features of Standard edition and thus all the limitations of Standard edition (as far as HA, scalability and features, and DE is concerned)

BI edition is available under the licensing model of Server + CAL.

Enterprise Edition:

This is the most robust and expensive edition of SQL Server.

It brings all the features that SQL Server has to offer on your plate.

No limitation in any High Availability features.

No Core or RAM limitation.

No hidden features in DE, SSIS, SSAS or SSRS.

The only limitation or we can setback of it is its cost. It is EXPENSIVE. 5 times more expensive than standard edition per core. And my friend SQL Server per core license is not cheap, for SE its 1800$ and for Enterprise its 6900$.

Enterprise edition is available in per core licensing model only.

 

Resources:

Comparison of features of different Editions

https://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx

Reporting services on Express

https://technet.microsoft.com/en-us/library/ms365166(v=sql.105).aspx