SQL Server 2012 Express Installation Tutorial

SQL Server 2012 Express Installation Tutorial

Before we begin the installation process, it is important that you choose the correct version of SQL Server Express. To learn more about which version to download, read the following article and download the right SQL Express version:
SQL Express 2012 Versions
After downloading the correct version, make sure that you have the right hardware and software requirements.
Environment Check

• Supported OS: Server 2008 R2 SP1, Server 2008 SP2, Windows 7 SP1, and Vista SP2.
• Processor:
• Minimum for x64 installations: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support. Processor Speed: 1.4 GHz
• Minimum for x86 installations: Pentium III compatible or higher. Processor Speed: 1.0 GHz
• Memory: Minimum: 512 MB

If you do not have the minimum requirement, you will be prompted with a message box similar to this:
(I was trying to install SQL Server 2012 Express on Windows 7 which did not have Service Pack 1 installed.)

You can also perform System Configuration Check using a tool provided by the SQL Server 2012 Express application installation. Run the executable file after you have downloaded. Installation files begin to be extracted to a temporary folder, then the below window will appear. From the Planning menu on the left, choose System Configuration Checker:


1. If you pass the minimum requirements, then you can proceed with the software installation. From the Installation menu on the left side, choose New SQL Server stand-alone installation or add features to an existing installation:

2. Setup will perform a quick system check. You can see below that one of the conditions failed and asks for a system reboot. This is because I have installed Window 7 Service Pack 1 (using Window Update) but did not restart my computer:

3. If you also required to do a reboot, then you have to start over the installation process from the beginning. In this step you have to accept the License Terms to continue:

4. Another quick check is done and this time the system passed all:

5. We can select the features that we want to install. Note that in this example, I have downloaded SQL Server 2012 Express with Advanced Services. This includes the Reporting Services:

6. Name the instance of your new SQL Server Express. I am also changing the Instance root directory:

7. You can define separate account for each service. I am leaving everything as the default:

8. You can also specify your Database Engine Collation. I am leaving as the default:

9. Choose the authentication type. It is recommended to choose Mixed mode for better security. You can also change this at a later time. Read this article to learn how to: Change SQL Server Authentication mode
If you choose mixed mode, then you have to enter a password for the SQL Server system administrator (sa) account:

10. If you ticked in the feature’s section to install Reporting Services, then here it will ask you whether to have the default configuration or do installation only and do Reporting Services configuration manually later. For this exercise, I am leaving as the default – to do both installation and configuration. Read the following article if you want to learn how to do configuration manually:
Installing and Configuring Reporting Services

11. The system will now continue with installation:

12. Finally, the last screen shows the installation process is completed successfully:

13. It may ask you to restart your computer:

14. We can see now that SQL Server 2012 Express with other tools installed under All Programs:

15. We can run the SQL Server Management Studio (SSMS) and see that we have already two databases installed. These are related to the Reporting Services.

16. We can also verify that the Reporting Services is running by typing the URL: http://localhost/Reports_SQLExpress


All done. Note that Reporting Services when access by IE9, will prompt you to enter username and password. To learn how to resolve this issue, please read the article:
SSRS Prompt for username and password
Reporting Services may also give you some User Access Control authorization error (UAC). To learn how to resolve this issue, read the following article:
SQL Server Reporting Services User Access Control (UAC)

Move database files .mdf & .ldf to different physical location

Here is a brief and simple way to move your Data files from one location to another.
This method involves using SSMS (SQL Server Management Studio)
1- Open SSMS and locate to database you want to change Data file directory
2- Remember/Note the current Data file directory path. You can do this by Right Clicking on the database and choose Properties. Then, from the left side menu, click on Files to see the Path of your current Data file directory path. Note it down or click on Control + C to copy the path name.

3- Detach Database by Right clicking on the database and choosing >> Tasks >> Detach
4- A new window will appear. Click on Drop Connections check box and then click OK.

5- Using the windows explorer, go to your Data file directory which you copied or wrote down in step 2:

6- Copy/Move the Data File (.MDF) and the Transaction Log file (.LDF) to your new physical location. Note that I always recommend copying first instead of moving in case the file gets corrupted during the move, you will still have the original one to go back to.

7- Once you copied the two files, click on Database(s) main folder and choose Attach. Then, from the new opened window, click on Add:

8- Navigate to the new path where files are copied and choose the .mdf file and click on OK:

9- Now you can see that both the Data File (.MDF) and Transaction Log File (.LDF) are shown in the new location. Click OK and you are done (You need to refresh the Databases for the new attached database to appear again).


To improve the quality of this user-guide, please help me with some feedback or by simply commenting below.

 

Change column type in SQL Server

Change column type in SQL Server

There are two ways to change the column type in SQL Server:
1- Is by writing a quick code in query script.
2- Is by using the enterprise manger: SSMS (SQL Server Management Studio).

Method 1

Run the following code in SQL server:
ALTER TABLE <table name> ALTER COLUMN <column name> DATA TYPE

Example Code:

ALTER TABLE tblExampleTable ALTER COLUMN ID INT

See Microsoft webpage for full Alter Table syntax details:

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

Method 2

To do the changes using the second method follow these steps:

a- Open SSMS
b- Navigate to your table which you want to modify column
c- Find column and Right click the choose Modify

d- A window will open in Edit mode so that you can change the type of each column:

e- Click on Save once you are done.

Note that when you click Save, you might show the below warning message. Saving changes is not permitted:

In this case, you can change some settings to allow this save and avoid the warning. To do this simply follow these steps:

1- In SSMS, click on Tools
2- Choose Options
3- Click on the Designers section from the left side menu
4- Un-check the box for: Prevent saving changes that require table re-creation

Now try to save again and this time it will work.

SQL SERVER

Below I have listed some frequently used objects.

USEFUL TABLES AND VIEWS

1. master..sysperfinfo
2. <db>..sysindexes
3. master..sysprocesses
4. syslogins
5. sys.messages
6. sys.dm_exec_requests
7. sys.dm_exec_sql_text
8. sys.dm_exec_connections
9. sys.dm_db_index_physical_stats
10. sys.dm_db_missing_index_groups
11. sys.dm_db_missing_index_details
12. sys.dm_db_missing_index_group_stats
13. sysindexes – Using this you can count the rows in a table faster. make use of below query.
    

     select rows from sysindexes
     where id= OBJECT_ID('tablename') and indid < 2

USEFUL STOREDPROCS

1. sp_spaceused
2. sp_helpdb
3. sp_helpfile
4. sp_change_users_login
5. sp_changedbowner
6. sp_readerrorlog

USEFUL FUNCTIONS

1. fn_virtualfilestats()

USEFUL LINKS

1. Port numbers a DBA should know.
2. SQL Server DBA Interview Questions.

Read Disk Read/ Write for all databases in an instance and Sort them

 

Below Query will help you read live file level Disk read and write information. WIll behandy for performance tuning capacity planning.

select db.name,files.filename,*
 from sys.dm_io_virtual_file_stats(NULL, NULL) dmv
 join sys.databases as db on db.database_id=dmv.database_id
 join sys.sysaltfiles as files
 on dmv.database_id=files.dbid and dmv.[file_id]=files.fileid
 order by dmv.num_of_bytes_written DESC
select db.name,files.filename,*
 from sys.dm_io_virtual_file_stats(NULL, NULL) dmv
 join sys.databases as db on db.database_id=dmv.database_id
 join sys.sysaltfiles as files
 on dmv.database_id=files.dbid and dmv.[file_id]=files.fileid
 order by dmv.num_of_bytes_read DESC

 

Executing CHECKDB on all user databases via TSQL

 Run below Query. And once the query executes completely, go through the SQL Server Error log to see the results.

declare databases cursor for
select name from sys.databases
    where name <> 'master' and name <> 'tempdb'
    and name <> 'model' and name <> 'msdb'
declare @dbname varchar(100)

open databases
fetch next from databases into @dbname

while @@fetch_status=0
begin
print @dbname + ' - ';
DBCC CHECKDB (@dbname) with no_infomsgs;
fetch next from databases into @dbname
end

close databases
deallocate databases

 

Check SQL Authentication Login Account Password

Did you come across any situation where you wanted to make sure that the password you set for a SQL account is what you really wanted? Below is how you can validate the password for a particular account. With this methord, you can find out the forgotten password for a SQL account without causing any audit failures and by not changing any security settings.
SELECT name FROM sys.syslogins WHERE pwdcompare ('SomePasswordWhichYouKnow',password) =1
When you run the above query, you will get the list of accounts which has password set as SomePasswordWhichYouKnow.
This way, you can figure out what password was set while creating an account (SQL authentication account)

Error: Transaction log file got damaged or corrupted

inShare

Once we had an issue in our environment. We had a maintenance activity on the Database server during a weekend and the server was shutdown for a Storage level snapshot backup. After the backup the server was brought back online. But unfortunately the user database was in suspect mode and could not be accessed. SQL Server Error Log says

“An error occurred while processing the log for database ‘<databasename>’.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.”

Solution

Below is what I did to rescue my beloved database.
1. Enabled emergency mode for that database.
ALTER DATABASE set EMERGENCY
2. Then brought the database into single user mode.
ALTER DATABASE set SINGLE_USER
3. Executed DBCC CHECKDB with repair_allow_data_loss.
DBCC CHECKDB (<databasename>,repair_allow_data_loss) with no_infomsgs
I got below warning in Error Log right after I triggered the CHECKDB command.

Warning: The log for database ‘<databasename>’ 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.

And once the CHECKDB completed, The errorlog log reported below message which made me very happy.

EMERGENCY MODE DBCC CHECKDB (<databasename>, repair_allow_data_loss) WITH no_infomsgs executed by SPANSION\admin_sponnamb found 0 errors and repaired 0 errors. Elapsed time: 0 hours 52 minutes 54 seconds.

After the CHECKDB completed, I reverted back the database into multiuser mode with below command.
ALTER DATABASE <databasename> SET MULTI_USER
Now the application was successfully able to access the database and everything was function very well as expected. A full backup has to be triggered to reset LSN numbers and to take transaction log backups.

Error while executing a sql job “The EXECUTE permission was denied on the object ‘sp_start_job’,database ‘msdb’,schema’dbo’.

inShare

SOLUTION:
1) Go to MSDB– Security –Users — UserName — User Name Properties — Assigned the ‘TargetServerRole’ in msdb

2)     Run the query
select pr.name, dp.permission_name, dp.state_desc
 from   msdb.sys.database_permissions dp
 join   msdb.sys.objects o on dp.major_id = o.object_id
 join   msdb.sys.database_principals pr
 on dp.grantee_principal_id = pr.principal_id
 where  o.name = ‘sp_start_job’


3)     Grant  Execute permission to concern user to SP_Start_Job and SP_Stop_Job and it resolved the issue.

Tip: Rename a live production database.

 

I had a requirement from application team that I should rename a database when the application is running and the transactions were happening. The reason was that they didn’t want any outages registered for the entire application. If they stop the services, it would impact the the whole application.

Steps

1. Set the target database into Single user mode.
2. Rename database.
3. Bring the database back into Multi user mode.

Use below query to perform above steps.

USE targetdatabase
GO
/*Enable Single user mode killing all connections*/
ALTER DATABASE targetdatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
/*Rename targetdatabase to targetdatabaseNewName*/
ALTER DATABASE targetdatabase MODIFY NAME = targetdatabaseNewName
GO
ALTER DATABASE targetdatabaseNewName SET MULTI_USER
GO

Here you go !. You are done.