Basics Of Database Administration In SQL Server: Part 7

Introduction

In my previous article Basics of Database Administration in SQL Server: Part 6, I discussed database filegroups, why filegroups are important, and how it helps in database administration. There I mentioned that we can bring some parts of the database online quickly (piecemeal restore/partial). This article is all about Piecemeal Restore / Partial Restore, so without wasting time let’s get started with piecemeal restore.

 

If you missed previous articles you can check them out from the following link:

• Basics of Database Administration in SQL Server: Part 1
• Basics of Database Administration in SQL Server: Part 2
• Basics of Database Administration in SQL Server: Part 3
• Basics of Database Administration in SQL Server: Part 4
• Basics of Database Administration in SQL Server: Part 5
• Basics of Database Administration in SQL Server: Part 6

What is Piecemeal Restore

Piecemeal restore is a more advanced form of partial restore in SQL Server 2000. Actually piecemeal restore is composed of sequential restores and recovers a database in stages at the filegroup level, started with the primary filegroup. Piecemeal restore was introduced in SQL Server 2005.

Piecemeal restore works with only Enterprise Edition of SQL Server 2005 or later versions contain multiple filegroups.

 

Piecemeal Restore Exampled Step by Step:

To understand the concept of piecemeal restore, let’s take it with an example step by step:

 

Step 1:

We are going to create a database named TestDB with FOUR filegroups named Primary, FG1, FG2 and FG3 in which Primary filegroup is the default filegroup.

 

We can use the following script to create our desired database for testing piecemeal restore.

1. CREATE DATABASE[TestDB] ON  
2. PRIMARY(NAME = N 'PrimaryTest', FILENAME = N 'D:\TestDB\TestDB.mdf'),  
3.   
4.     FILEGROUP[FG1](NAME = N 'TestDB_FG1', FILENAME = N 'D:\TestDB\TestDB_FG1.ndf'),  
5.   
6.     FILEGROUP[FG2](NAME = N 'TestDB_FG2', FILENAME = N 'D:\TestDB\TestDB_FG2.ndf'),  
7.   
8.     FILEGROUP[FG3](NAME = N 'TestDB_FG3', FILENAME = N 'D:\TestDB\TestDB_FG3.ndf')  
9.   
10. LOG ON(NAME = N 'TestDB_log', FILENAME = N 'D:\TestDB\TestDB_log.ldf')  
11. GO  

Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model. In our example we have chosen FULL RECOVERY MODEL so that we are able to perform log backups as well.

 

                                       Figure 1: TestDB created with 4 filgroups

Step 2:

After creating TestDB with FOUR filegroups, we are going to create and insert values in table T1 on filegroup FG1, table T2 on FG2, table T3 on FG3 and table T4 on Primary filegroup with the following script:

1. USE testdb  
2. Go  
3.   
4. CREATE TABLE T1  
5.     (ID INT)  
6. ON FG1  
7. Go  
8. INSERT INTO T1 VALUES(1)  
9. Go  
10.   
11.   
12. CREATE TABLE T2  
13.     (ID INT)  
14. ON FG2  
15. Go  
16. INSERT INTO T2 VALUES(1)  
17. Go  
18.   
19.   
20. CREATE TABLE T3  
21.     (ID INT)  
22. ON FG3  
23. Go  
24. INSERT INTO T3 VALUES(1)  
25. Go  
26.   
27.   
28. CREATE TABLE T4  
29.     (ID INT)  
30. ON[PRIMARY]  
31. Go  
32. INSERT INTO T4 VALUES(1)  
33. Go  

Step 3:

In step 3 we will take backup of all filegroups separately, apart from that you can also take full backup of the database to be on safer side and it’s always a best practice to take full backup of databases. The following are the scripts to take full backup of the database and filegroups backup.

 

Remember: We have taken full backup of the database only for emergency cases so that we can revert back the changes if required but we will not use it during the piecemeal restore.

1. --BACKUP DATABASE[TestDB] TO  
2.     --DISK = N 'D:\TestDB Backups\TestDBFullBackUp.bak'  
3. WITH NOFORMAT, NOINIT,  
4. --NAME = N 'TestDB-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
5.     --GO  
6.   
7.   
8. --Backup of Primary filegroup  
9. BACKUP DATABASE[TestDB] FILEGROUP = N 'PRIMARY'  
10. TO DISK = N 'D:\TestDB Backups\PrimaryFG.bak'  
11. WITH NOFORMAT, NOINIT,  
12. NAME = N 'PrimaryFG Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
13. GO  
14.   
15.   
16. --Backup of FG1 filegroup  
17. BACKUP DATABASE[TestDB] FILEGROUP = N 'FG1'  
18. TO DISK = N 'D:\TestDB Backups\fg1.bak'  
19. WITH NOFORMAT, NOINIT,  
20. NAME = N 'FG1 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
21. GO  
22.   
23.   
24. --Backup of FG2 filegroup  
25. BACKUP DATABASE[TestDB] FILEGROUP = N 'FG2'  
26. TO DISK = N 'D:\TestDB Backups\fg2.bak'  
27. WITH NOFORMAT, NOINIT,  
28. NAME = N 'FG2 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
29. GO  
30.   
31.   
32. --Backup of FG3 filegroup  
33. BACKUP DATABASE[TestDB] FILEGROUP = N 'FG3'  
34. TO DISK = N 'D:\TestDB Backups\fg3.bak'  
35. WITH NOFORMAT, NOINIT,  
36. NAME = N 'FG3 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
37. GO  

Step 4:

In step 4, further we will insert values in tables T1, T2, T3 and T4.

1. INSERT INTO T1 VALUES(2)  
2. INSERT INTO T2 VALUES(2)  
3. INSERT INTO T3 VALUES(2)  
4. INSERT INTO T4 VALUES(2)  

Step 5:

In step 5, we are going to take log backup of TestDB with below script:

1. BACKUP LOG [TestDB] TO  
2. DISK = N'D:\TestDB Backups\translog1.trn' WITH NOFORMAT, NOINIT,  
3. NAME = N'TransactionLog1 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
4. GO  

Step 6:

In step 6, we again inserted values in all tables i.e. T1, T2, T3 and T4 and taken log backup as well.

1. INSERT INTO T1 VALUES(3)  
2. INSERT INTO T2 VALUES(3)  
3. INSERT INTO T3 VALUES(3)  
4. INSERT INTO T4 VALUES(3)  
5. Go  
6.   
7. BACKUP LOG [TestDB] TO  
8. DISK = N'D:\TestDB Backups\translog2.trn' WITH NOFORMAT, NOINIT,  
9. NAME = N'TransactionLog2 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
10. GO  

Step 7:

In step7, we again inserted some values in table T1, T2, T3 and T4 and performed log backup.

1. INSERT INTO T1 VALUES(4)  
2. INSERT INTO T2 VALUES(4)  
3. INSERT INTO T3 VALUES(4)  
4. INSERT INTO T4 VALUES(4)  
5. Go  
6.   
7. BACKUP LOG [TestDB] TO  
8. DISK = N'D:\TestDB Backups\translog3.trn' WITH NOFORMAT, NOINIT,  
9. NAME = N'TransactionLog3 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
10. GO  

Step 8:

In step 8, we again inserted some values in table T1, T2, T3 and T4.

1. INSERT INTO T1 VALUES(5)  
2. INSERT INTO T2 VALUES(5)  
3. INSERT INTO T3 VALUES(5)  
4. INSERT INTO T4 VALUES(5)  
5. Go  

Step 9:

In step 9, we are performing tail log backup.

1. Use master  
2. go  
3. BACKUP LOG [TestDB] TO DISK = N'D:\TestDB Backups\translogtail.trn'  
4. WITH NO_TRUNCATE , NOFORMAT, NOINIT,  
5. NAME = N'TLogTail Backup',  
6. SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10  
7. Go  

We need to use master database while performing tail log backup and restore operations, otherwise it will through error: Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database 'TestDB' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally. [Error for tail log backup]


Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database 'TestDB' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally. [Error for restore operations]

 

                  Figure 2: tail log backup can only operate using master database

                     Figure 3: Tail log backup successful when used master database

Step 10:

In step 10, as we have all backups now we will perform restore operation for PRIMARY filegroup in the following sequence. First we will apply primaryfg.bak and then all transaction log backups i.e. translog1.trn, translog2.trn, translog.3 and translogtail.trn:

1. RESTORE DATABASE TestDB FILEGROUP = 'Primary'  
2. FROM DISK = N 'D:\TestDB Backups\primaryfg.bak'  
3. WITH PARTIAL, NORECOVERY  
4. Go  
5.   
6. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog1.trn'  
7. WITH NORECOVERY  
8. Go  
9.   
10.   
11. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog2.trn'  
12. WITH NORECOVERY  
13. Go  
14.   
15.   
16. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog3.trn'  
17. WITH NORECOVERY  
18. Go  
19.   
20.   
21. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translogtail.trn'  
22. WITH RECOVERY  
23. Go  

Step 11: Now we will check the status of our TestDB database with the following query:

1. SELECT [name], [state_desc]  
2. FROM TestDB.sys.database_files;  
3. GO  

      Figure 4: State of TestDB database after restoring PRIMARY filegroup

From the above output we can see that PRIMARY filegroup is now ONLINE while other filegroups i.e. FG1, FG2 and FG3 are still in RECOVERY_PENDING state. This is the actual beauty of doing piecemeal restore. It is very helpful when we have a very large database. In this situation we can restore PRIMARY filegroup first then other filegroup in stages and business or users will not get hampered. Here we can remember one thing that restore that filegroup first in which important tables are there and users use frequently, after primary filegroup restore.

 

At this stage now we can select data from T4 table which is created in PRIMARY filegroup but we will not be able to select data from T1, T2 and T4 tables because these tables resides in FG1, FG2 and FG3 filegroup which are in RECOVERY_PENDING state.

Figure 5: able to fetch data from T4 table which resides in PRIMARY filegroup

We got the following error message when we tried to fetch data from T3 table which resides in FG3 filegroup and same error message will also show when we try to fetch data from T1 and T2 table because FG1 and FG2 are in RECOVERY_PENDING state.

 

Msg 8653, Level 16, State 1, Line 1

 

The query processor is unable to produce a plan for the table or view 'T3' because the table resides in a filegroup which is not online.

Step 12: In step12, let’s apply restore operations on other filegroups as well to make TestDB database online.

For this you need to run the following queries in sequential order:

1. use master  
2. go  
3. RESTORE DATABASE TestDB FILEGROUP = 'fg1'  
4. FROM DISK = N 'D:\TestDB Backups\fg1.bak'  
5. WITH NORECOVERY  
6. Go  
7.   
8. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog1.trn'  
9. WITH NORECOVERY  
10. Go  
11.   
12.   
13. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog2.trn'  
14. WITH NORECOVERY  
15. Go  
16.   
17.   
18. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog3.trn'  
19. WITH NORECOVERY  
20. Go  
21.   
22.   
23. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translogtail.trn'  
24. WITH RECOVERY  
25. Go  

Now check the state of TestDB database again using the following query:

1. SELECT [name], [state_desc]  
2. FROM TestDB.sys.database_files;  
3. GO  

            Figure 6: State of TestDB database after restoring FG1 filegroup

 

Now FG1 filegroup is also ONLINE, only FG2 and FG3 are in RECOVERY_PENDING state.

Step 13: In step13, do restore operation for FG2 filegroup with the following query:

1. use master  
2. RESTORE DATABASE TestDB FILEGROUP = 'fg2'  
3. FROM DISK = N 'D:\TestDB Backups\fg2.bak'  
4. WITH NORECOVERY  
5. Go  
6.   
7. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog1.trn'  
8. WITH NORECOVERY  
9. Go  
10.   
11.   
12. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog2.trn'  
13. WITH NORECOVERY  
14. Go  
15.   
16.   
17. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog3.trn'  
18. WITH NORECOVERY  
19. Go  
20.   
21.   
22. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translogtail.trn'  
23. WITH RECOVERY  
24. Go  

Step 14: In step14, repeat the restore operation for FG3 filegroup using the following query:

1. use master  
2. go  
3. RESTORE DATABASE TestDB FILEGROUP = 'fg3'  
4. FROM DISK = N 'D:\TestDB Backups\fg3.bak'  
5. WITH NORECOVERY  
6. Go  
7.   
8. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog1.trn'  
9. WITH NORECOVERY  
10. Go  
11.   
12.   
13. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog2.trn'  
14. WITH NORECOVERY  
15. Go  
16.   
17.   
18. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog3.trn'  
19. WITH NORECOVERY  
20. Go  
21.   
22.   
23. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translogtail.trn'  
24. WITH RECOVERY  
25. Go  

Step 15: In step15, now check the final state of TestDB database with the following query:

1. SELECT [name], [state_desc]  
2. FROM TestDB.sys.database_files;  
3. GO  

   Figure 7: State of TestDB database after restoring FG2 and FG3 filegroup

Wow! It’s cool now all filegroups are ONLINE now and we can fetch data from all tables also which belongs with different filegroups.

Final Words

Now after the explained example it is very clear how we can make our database ONLINE using multiple filegroups. This is actually the piecemeal restore which helps us a lot to restore large databases and makes the important part of database ONLINE first after making PRIMARY FILEGROUP online. You can contribute with your comments if any correction is needed.

 

Note: Note: All the screenshots are applied to SQL Server 2012 Enterprise Evaluation Edition.

 

References

1. Piecemeal Restores
2. Piecemeal Restore SQL Server Customer Advisory Team
3. SQLSoldier
4. LessThanDot

Basics Of Database Administration In SQL Server: Part 6

SQL Server Storage Basics: Database Filegroups

In this part we will discuss some basic things about SQL Server File Groups. If you missed previous articles you can check them out from below link:

• Basics of Database Administration in SQL Server: Part 1
• Basics of Database Administration in SQL Server: Part 2
• Basics of Database Administration in SQL Server: Part 3
• Basics of Database Administration in SQL Server: Part 4
• Basics of Database Administration in SQL Server: Part 5

1. What is Filegroup

In SQL Server filegroup is a logical structure which contains objects like data file, tables and indexes. In other words we can say that a filegroup is a logical unit in which all database files are grouped together and simplifies database administration resulting into improved performance by controlling the placement of objects into specific filegroups on specific drive.


Figure 1: Pictorial representation of filegroups

2. Why Filegroups

Filegroups make administration easier for a DBA. Using multiple filegroups we can gain following benefits;

1. We can separate user data with internal system data using multiple filegroups.
2. We can overcome with maintenance overhead by putting archive (or even read-only) data onto their own filegroups and dedicated set of disks.
3. We can gain performance improvement by putting larger tables/indexes on their own filegroup and/or dedicated set of disks.
4. We can bring some parts of the database online quickly ( piecemeal restore ).

3. Types of Filegroups

There are two types of filegroups:

3.1. Primary Filegroup
3.2. User defined/Secondary Filegroup

3.1. Primary Filegroup:

The filegroup which contains the primary data file and any other files that are not associated to another filegroup is termed as Primary filegroup.

3.2. User-defined Filegroup:

The Filegroups which we create from FILEGROUP keyword using CREATE DATABASE or ALTER DATABASE is termed as user-defined filegroups. This file is created by user or later modifies database by a user.

4. Filegroup Examples

I am using below query to create filegroup with new database:

1. CREATE DATABASE[FG]  
2. ON PRIMARY  
3.     (NAME = N 'FG_data',  
4.         FILENAME = N 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG_data.mdf',  
5.         SIZE = 4096 KB, FILEGROWTH = 1024 KB)  
6. LOG ON  
7.     (NAME = N 'FG_log',  
8.         FILENAME = N 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG_log.ldf',  
9.         SIZE = 1024 KB, FILEGROWTH = 10 % )  
10. GO  
11. ALTER DATABASE[FG] ADD FILEGROUP[FG2]  
12. GO  
13.   
14. ALTER DATABASE[FG] ADD FILEGROUP[FG3]  
15. GO  

Using above query we created database name ‘FG’ and primary filegroup which is the default filegroup and FG2 and FG3 two user-defined filegroups.

5. How to view filegroups

By executing below query we can view filegroups in a database:

1. use FG  
2. go  
3. select * from sys.filegroups  
4. go  

Figure 2: Viewing filegroups

6. Creating a file and assigning it to filegroup

To add file in a filegroup we execute ALTER DATABASE query.

1. ALTER DATABASE FG  
2. ADD FILE  
3.     (NAME = FG3_data,  
4.         FILENAME = N 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG3_data.ndf')  
5. TO FILEGROUP FG3  

7. Changing default filegroup

Again ALTER DATABASE query will execute to change the default filegroup.

1. ALTER DATABASE FG  
2. MODIFY FILEGROUP FG3 DEFAULT  

Now check the default value for filegroup FG3 using sys.filegroups.

1. use FG  
2. go  
3. select * from sys.filegroups  
4. go  

Figure 3: Changing default filegroups

Now you can clearly see that the is_default value is 1 for FG3 filegroup. It means the default filegroup is FG3.

8. Filegroups Backup

We can take filegroups backup with two ways:

8.1. With SQL Server Management Studio (SSMS)
8.2. With T-SQL

8.1. With SQL Server Management Studio (SSMS):

To take backup of filegroups with ssms follow below steps:

1. Select database>>do right click.
2. Go to task >> click on backup,
3. Backup database window will appear, here choose option file and filegroups under backup component.
4. When you select file and filegroups another window will open which will show you all the filegroups for that database.
5. Click on check boxes to take backup of one or more filegroups according to your requirement.

Step 1 and step 2:


Figure 4: Showing how to reach backup option

Step3:


Figure 5: Choosing files and filegroups backup

Step 4 and step 5:


Figure 6: Selecting filegroups for backup

8.2. Filegroups backup with T-SQL:

You can execute below query to take backup of filegroups. In below example my database name is ‘FG’ and I am taking backup of all 3 filegroups i.e.

1. PRIMARY, FG2, FG3  
2.   
3. BACKUP DATABASE[FG]  
4. FILEGROUP = N 'PRIMARY',  
5.     FILEGROUP = N 'FG2',  
6.     FILEGROUP = N 'FG3'  
7. TO  
8. DISK = N 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\FG.bak'  
9. WITH NOFORMAT, NOINIT, NAME = N 'FG-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
10. GO  

Figure 7: Taking filegroups backup with t-sql

Points to remember:

• All pages for the system tables are allocated in the primary filegroup.
• Log files ( .ldf) does not use filegroups.
• No file can be a member of more than one filegroup.

9. References:

• Database file and filegroups. Technet Microsoft
• Files and Filegroups Architecture. Technet Microsoft
• Backup File and Filegroups. Technet Microsoft
• Journey to SQL Authority with Pinal Dave
• Microsoft SQL Server 2012 Internals By Kalen Delaney, Craig Freeman 

10. Wrap Up

In this section we discussed some basic things and saw some examples how to perform the task about filegroups which is closely related with article Basics of Database Administration in SQL Server: Part 5. 

We will discuss some other points about Database storage basics in upcoming articles.

 

For part 7 you can click on following link: Basics of Database Administration in SQL Server: Part 7

 

Keep sharing your knowledge, happy reading.  

Basics Of Database Administration In SQL Server: Part 5

This article will explain about the storage basics in SQL Server. After reading this article we will get to know physical implementation of database in SQL Server. We will explore about the features, work of data files and transaction log files in SQL Server through this article. In case if you missed previous articles you can check them out from the following links:

• Basics of Database Administration in SQL Server: Part 1
• Basics of Database Administration in SQL Server: Part 2
• Basics of Database Administration in SQL Server: Part 3
• Basics of Database Administration in SQL Server: Part 4

1. Introduction

Like every other DBMS software, SQL Server has also its storage engine which we need to know for better understanding of basics. SQL DBA should know well about these storage basics. In this article we will discuss about SQL Server storage engine. SQL Server storage engine has itself a vast topic, one article will not be sufficient for this because it has many things in it that we should know. After thinking a lot how to start this topic, I decided to discuss these basics in bunch of short articles because if I will try to cover all the things in one article it will get lengthy and we will not easily grasp. So let’s move ahead into SQL Server Storage Basics.

2. Physical Database Architecture

Every SQL DBA knows that the data in SQL Server is stored and organized into the logical components such as tables, views, procedures in databases, which a user can view easily but from the point of database administration we will talk here about physical implementation of data in databases. A database is physically implemented as two or more files on disk, which we called primary data files (.mdf), secondary data files (.ndf) and log files (.ldf). For better understanding we can put the following points:




Figure 1: User view & physical implementation of database

3. Storage Basics Explained

3.1 Primary Data Files:

3.1.1 What is Primary Data File?

Primary data file is a SQL Server database file which contains the startup information and act as a starting point for any database. It holds user data and all objects such as tables, indexes and stored procedures.




Figure 2: Illustrating physical files, extents, pages and page structure in SQL Server

3.1.2 Extension of Primary Data File:

It is recommended that primary data file should have .mdf extension but really you are free to give any extension to it like .abc, .ian, .jim, .cathy, .xyz
The following is the example:


1. USE [master]  
2. GO  
3. ALTER DATABASE [tempdb]   
4. ADD FILE   
5. (NAME = N'tempdev_Data06',   
6. FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev_Data06.ian',   
7. SIZE = 100MB, FILEGROWTH = 1024KB)  
8. GO  
9.   
10. ALTER DATABASE [tempdb]   
11. ADD FILE   
12. (NAME = N'tempdev_Data07',   
13. FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev_Data07.jim',   
14. SIZE = 100MB, FILEGROWTH = 1024KB)  
15. GO  
16.   
17. ALTER DATABASE [tempdb]   
18. ADD FILE   
19. (NAME = N'tempdev_Data08',   
20. FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev_Data08.cathy',   
21. SIZE = 100MB, FILEGROWTH = 1024KB)  
22. GO  

Figure 3: Showing that the data files have different extensions i.e .ian, .jim and .cathy

3.1.3 How many Primary Data Files, a database can have:

Every database can only have ONE primary data file (mdf), but do you really agree with this point. Let’s dig it more with some experiments:

Step 1: I changed the name of primary data file and transaction log file with the following query to make it easy to understand:

1. USE [master]  
2. GO  
3. ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'templog', NEWNAME= N'tempdev_Log')  
4. GO  
5. ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempdev', NEWNAME=N'tempdev_Data01')  
6. GO  
Step 2: Now I added four more data files: 'tempdev_Data02', 'tempdev_Data03', 'tempdev_Data04'and 'tempdev_Data05' with the following script:

1. USE [master]  
2. GO  
3. ALTER DATABASE [tempdb]   
4. ADD FILE   
5. ( NAME = N'tempdev_Data02',   
6. FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev_Data02.mdf' ,   
7. SIZE = 100MB , FILEGROWTH = 1024KB )  
8. GO  
9.   
10. ALTER DATABASE [tempdb]   
11. ADD FILE   
12. ( NAME = N'tempdev_Data03',   
13. FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev_Data03.mdf' ,   
14. SIZE = 100MB , FILEGROWTH = 1024KB )  
15. GO  
16.   
17. ALTER DATABASE [tempdb]   
18. ADD FILE   
19. ( NAME = N'tempdev_Data04',   
20. FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev_Data04.mdf' ,   
21. SIZE = 100MB , FILEGROWTH = 1024KB )  
22. GO  
23.   
24. ALTER DATABASE [tempdb]   
25. ADD FILE   
26. ( NAME = N'tempdev_Data05',   
27. FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev_Data05.mdf' ,   
28. SIZE = 100MB , FILEGROWTH = 1024KB )  
29. GO  
Step 3: Let’s see all the files with the following query:

1. Use tempdb;  
2. select name, physical_name from sys.database_files   

Figure 4: Showing that the data files tempdev_data01, tempdev_data02, tempdev_data03, tempdev_data04, tempdev_data05 having the same extension .mdf

So still the statement “Every database can only have ONE primary data file ( mdf )” is true or something wrong with this statement or the statement is not correct.

The answer lies in the statement which we made in 3.1.2 section: “It is recommended that primary data file should have .mdf extension but really you are free to give any extension to it such as .abc, .ian, .jim, .cathy, .xyz “ what we discussed earlier and the statement “Every database can only have ONE primary data file ( mdf )” is also true because a primary data file is the data file associated with any database when database is created for the very first time, and YES this can only be ONE. Same is true for transaction log file ( .ldf ) and secondary data file ( .ndf )

Here, I want to specify one more thing, suppose we have several mdf files for a particular database & we want to know that which is the main; to uncover it let’s take the above example which is in figure 4, here we have FIVE data files tempdev_data01, tempdev_data02, tempdev_data03, tempdev_data04, tempdev_data05. Now suppose someone changed the name then how we will identify the only ONE primary data file. Let’s take an example:

Step 1: In figure 4 I showed you FIVE mdf files for tempdb database and three data files which have .ian, .jim and .cathy extension. Now I am going to change the name of tempdev_data01.mdf to tempdev_data10.mdf with the following query:

1. USE master  
2. GO  
3. ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempdev_data01', NEWNAME= N'tempdev_Data10',  
4. FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdev_Data10.mdf')  

Figure 5: Modifying data file

Step 2: Check the name and physical_name for files in tempdb database with the following query:

1. use [tempdb]  
2. GO  
3. select name, physical_name from sys.database_files  
4. GO  

Figure 6: Showing multiple data files with .df extension and some different extension

Now there are again FIVE data files with .mdf extension and THREE data files with other extensions: .jim, .ian and .cathy. So many data files how we find that the only ONE primary data file which we are discussing, because till this discussion we know that the primary data file should be one and only ONE. So let’s find out the only ONE primary data file.

Step 3: To find out one and only ONE primary data file for a database execute the following query:


1. Use tempdb;  
2. select name, filename from sys.sysdatabases;  


Figure 7: Fetching the name and location of only ONE primary data file

Now it is clearly visible that the only ONE primary data file is tempdev_Data10.mdf. Someone here can ask one question why we use sysdatabases only for query not other query, the answer is simple because the information about the only ONE primary data file is stored in the filename column of the sysdatabases in the master database.

3.1.4 Location of Primary Data Files:

The location of the primary data file is stored in the filename column of the sysdatabases in the master database.

When SQL Server starts up database it looks for this file because in this file the information for all databases exists.

Primary data file contains sys.database_files system table which stores information about all other files in a specific database. Here someone can ask you why sys.database_files not sysfiles [sysfiles also give you the information about all files in the database]. So let’s move ahead to know why I said sys.database_files.

sysfiles vs sys.database_files

The compatibility view sysfiles returns one row for every file that is associated with a database.


Figure 8: Showing output from sysfiles

The catalog view sys.database_files is the replacement for sysfiles. Like sysfiles, sys.database_files also returns one row for every file that is associated to a database. sys.database_files gives more information than the sysfiles and have more readable output. It gives information about state i.e. ONLINE/OFFLINE, lsn, growth, media etc. Below is the output of sys.database_files split in 3 parts because of space.


Figure 9: Showing output from sys.database_files

So from the above discussion we can conclude also that the primary data file have pointer to all other files in the specific database.

3.2 Secondary Data Files

• A database can have any number of secondary data files, these files are optional and user-defined. We can use secondary files to spread data across multiple disks.
  
  
• Data files other than the primary data file make secondary data files.
  
  
• It is not necessary to have secondary data files as these are optional. We can create it depending on the requirement & database environment.
  
  
• We can put secondary data files in default filegroup or any other filegroup defined by user.
  
  
• .ndf is the recommended extension for secondary data files.

3.3. Transaction Log Files

3.3.1 What is Transaction Log File?

Transaction log file is a sequential record of transactions which holds the log information that is used to help in disaster recovery scenarios to recover the database.

3.3.2 Extension of Transaction Log File:

Recommended extension for log file is .ldf, again it is not mandatory you are free to give any extension to it.

3.3.3 How many Transaction Log Files, a database can have:

We can create multiple log files for a database, but there can be only one log file will be active at a time because transactions are written to first file until it will get full. Once it will get full transactions will get written in second file and so on.

3.3.4 Location of Transaction Log Files:

Location of transaction log file is stored in sys.database_files, we can use the following query to get the location of transaction log file for a specific database:

1. use TestDB  
2. select name, type_desc, physical_name from sys.database_files   


Figure 10: Getting name and location of transaction log files

4. Storage Limitation/Capacity of SQL Server

SQL Server database engine has the = following storage capacity for different SQL Server objects:



References

• Files and Filegroups Architecture SQL Server 2008 R2. Microsoft Developer Network
• Files and Filegroups Architecture. Technet Microsoft
• Microsoft SQL Server 2000 Unleashed By Ray Rankins, Paul Jensen, Paul Bertucci. Sams Publishing.
• Microsoft SQL Server 2012 Administration: Real-World Skills for MCSA Certification and Beyond
• Maximum Capacity Specifications for SQL Server. Technet Microsoft

Conclusion

This is all about SQL Server database physical files, many more things still need to uncover which you will see probably in coming articles like files and Filegroups, pages, extents, transaction log file in more details. Give your feedback so that in future I can serve you more better. To know more about SQL Server Database Administration click on below link:

Basics of Database Administration in SQL Server: Part 6

 

 Happy reading and keep sharing your knowledge.

C# Razor Syntax Quick Reference

I gave a presentation to another team at Microsoft yesterday on ASP.NET MVC and the Razor view engine and someone asked if there was a reference for the Razor syntax.
It turns out, there is a pretty good guide about Razor available, but it’s focused on covering the basics of web programming using Razor and inline pages and not just the Razor syntax.
So I thought it might be handy to write up a a really concise quick reference about the Razor syntax.

Syntax/Sample	Razor	Web Forms Equivalent (or remarks)
Code Block	@{ int x = 123; string y = "because."; }	<% int x = 123; string y = "because."; %>
Expression (Html Encoded)	<span>@model.Message</span>	<span><%: model.Message %></span>
Expression (Unencoded)	<span> @Html.Raw(model.Message) </span>	<span><%= model.Message %></span>
Combining Text and markup	@foreach(var item in items) { <span>@item.Prop</span> }	<% foreach(var item in items) { %> <span><%: item.Prop %></span> <% } %>
Mixing code and Plain text	@if (foo) { <text>Plain Text</text> }	<% if (foo) { %> Plain Text <% } %>
Using block	@ using (Html.BeginForm()) { <input type="text" value="input here"> }	<% using (Html.BeginForm()) { %> <input type="text" value="input here"> <% } %>
Mixing code and plain text (alternate)	@if (foo) { @:Plain Text is @bar }	Same as above
Email Addresses	Hi philha@example.com	Razor recognizes basic email format and is smart enough not to treat the @ as a code delimiter
Explicit Expression	<span>ISBN@(isbnNumber)</span>	In this case, we need to be explicit about the expression by using parentheses.
Escaping the @ sign	<span>In Razor, you use the @@foo to display the value of foo</span>	@@ renders a single @ in the response.
Server side Comment	@* This is a server side multiline comment *@	<%-- This is a server side multiline comment --%>
Calling generic method	@(MyClass.MyMethod<AType>())	Use parentheses to be explicit about what the expression is.
Creating a Razor Delegate	@{ Func<dynamic, object> b = @<strong>@item</strong>; } @b("Bold this")	Generates a Func<T, HelperResult> that you can call from within Razor. See this blog post for more details.
Mixing expressions and text	Hello @title. @name.	Hello <%: title %>. <%: name %>.
NEW IN RAZOR v2.0/ASP.NET MVC 4
Conditional attributes	<div class="@className"></div>	When className = null <div></div> When className = "" <div class=""></div> When className = "my-class" <div class="my-class"></div>
Conditional attributes with other literal values	<div class="@className foo bar"> </div>	When className = null <div class="foo bar"></div> Notice the leading space in front of foo is removed. When className = "my-class" <div class="my-class foo bar"> </div>
Conditional data-* attributes. data-* attributes are always rendered.	<div data-x="@xpos"></div>	When xpos = null or "" <div data-x=""></div> When xpos = "42" <div data-x="42"></div>
Boolean attributes	<input type="checkbox" checked="@isChecked" />	When isChecked = true <input type="checkbox" checked="checked" /> When isChecked = false <input type="checkbox" />
URL Resolution with tilde	<script src="~/myscript.js"> </script>	When the app is at / <script src="/myscript.js"> </script> When running in a virtual application named MyApp <script src="/MyApp/myscript.js"> </script>

Basics of Database Administration in SQL Server: Part 4

SQL Server Users Explained and Exampled

In this part we will try to understand each and every thing related to SQL Server users. So let's move ahead to explore SQL Server users. In case if you missed Part 1 and Part 2 you can check them out from the following links:

• Basics of Database Administration in SQL Server: Part 1
• Basics of Database Administration in SQL Server: Part 2
• Basics of Database Administration in SQL Server: Part 3

I recommend that before reading this article, read Basics of Database Administration in SQL Server: Part 2 first so that you can easily relate the concepts of users and logins.

Introduction

A user is a database level security principal that interact with database objects and only has scope across the database for which it is created. To connect to a specific database on the SQL Server, the login should be mapped with the database user. In addition to it, a login can be mapped to multiple databases but users can only be mapped as one user in each database.

Properties of SQL Server Users

1. A user is a database-level principal.



Figure 1: User is a database-level principal

From Figure 1 it is very clear that "yashwant" is a "user" that belongs to "AdventureWorks2008R2" which is a database. So we can say user is an account specific to the database.

2. A valid user should be associated with a login to work with the database.

Interesting Note: sys.sysusers and sys.syslogin are both linked with a common column called SID (Security Identifier). The following is an example to make that more clear.


Figure 2: Showing how sys.sysusers and sys.syslogins linked together

In the preceding example we have executed three queries together labeled with 1, 2 and 3 in Red color within Black circles.

• The first query output shows that user "yashwant" is linked with "XYZ\yashwant.kumar" login with the SID in a Red rectangular box.
  
• The second query simply shows the username and their SID for the "AdventureWorks2008R2" database.
  
• The third query shows the loginname and their SID.

Now if we compare all three SIDs in rectangular Red boxes then we will find all SIDs are the same. Hence it is very clear from the preceding example that sys.sysusers and sys.syslogins are linked together with a common column called SID.

3. The Information about users are stored in sys.sysusers, in the database where it's mapped.

4. The scope of a user is the database only that is mapped to the user and the user can only be mapped to one database. If we try to create a new user (either with the same name or another) for the other database with the same login then SQL Server will throw the error 15063.


Figure 3: Showing that one user can map only to one database

• Read the following sentence carefully and try to understand the relation between user and login.

In the preceding example I tried to create a new database user "yashwant" for the "TestDB" database, that is also a database user for the "AdventureWorks2008R2" database and mapped to the "XYZ\yashwant.kumar" login that is a Windows authenticated login. Now in this case SQL Server will throw error 15063, because the database user "yashwant" is already mapped with "XYZ\yashwant.kumar" for the database "Adventureworks2008R2".

5. Multiple users can be associated with one server login in multiple databases.


Figure 4: Illustrating one login can be associated with multiple users in multiple databases.

From Figure 4 we can clearly see that database users "Jim", "Catherine" and "Katie" are associated with the single login "yashrox".

We can execute the following query also if we want to see all the users and logins mapping.

1. exec sp_msloginmappings [ click here for more on sp_msloginmappings ]  

Or:

1. --Step 1 : Create temp table  
2. CREATE TABLE #tempMappings   
3. (  
4.     LoginName nvarchar(1000),  
5.     DBname nvarchar(1000),  
6.     Username nvarchar(1000),  
7.     Alias nvarchar(1000)  
8. )  
9.   
10. --Step 2:Insert the sp_msloginmappings into the temp table  
11.   
12. INSERT INTO #tempMappings  
13. EXEC master..sp_msloginmappings --Step 3 : List the results . Filter as required  
14. SELECT  
15. loginname,  
16. username,  
17. DBName  
18. FROM  
19. #tempMappings ORDER BY LoginName  
20.   
21. --Step 4: Manage cleanup of temp table  
22.   
23. DROP  
24. TABLE #tempMappings

6. We can grant or deny permissions to a user inside a database. For example:

1. GRANT INSERT, UPDATE, SELECT ON Sales.Customer TO yashwant;  

In this query the user "yashwant" is getting permission to insert data and update the table Customer that is in the Sales Schema. (We will explain Schema later in this article).

Property Page of Database User

We can open the property page of a database user using the following procedure:

Step 1: Expand the database.

Step 2: Go to security and expand it.

Step 3: Expand the Users.

Step 4: Right-click on username "yashwant" (in my case).

Step 5: Click on properties.

After clicking on properties you will get the following page with five tabs. 

• General.
• Owned Schemas.
• Membership.
• Securables.
• Extended Properties.

Figure 5: Showing property page of database user "yashwant"

1. General: General tab has five sections in it.

• User type.
• User name.
• Login name.
• Default language.
• Default schema.

2. User type: There are the following five ways by which we can create a user:

• SQL user with login.
• User mapped to a certificate.
• User mapped to an asymmetric key.
• Windows user.
• SQL user without login.

Figure 6: Showing user types

For a), b), c) and d) the user types refer again to the Basics of Database Administration in SQL Server: Part 3.

For e) (SQL Server user without a login) we will explain here so let's move ahead to explore more about it.

SQL User without login:

• A login does not need to exist to create this type of user.
  
• The authentication of these types of users happen at the database level.

Use of SQL User without login:

• From SQL 2005 we have the ability to create users without logins. This feature was added to replace application roles.
  
• By using SQL users without logins it is easier to move the application to a new instance and limits the connectivity requirements for the function.
  
• We can use this type of user in the database using impersonation (allowing one user to act on behalf of another user).

For a better understanding of a user without a login let's do some practical work.

Prerequisites for experiment:

• Login and mapped user.
• User without login.

Step 1
Create a login "ianrox" with the following query in the SSMS of login "XYZ\yashwant.kumar".


Figure 6.1: Create Login

Step 2
Create user "ianrox" in the "Adventureworks2008R2" database in the SSMS of "XYZ\yashwant.kumar".


Figure 6.2: Create User

Step 3
Create a user "Joe_UserWithoutLogin" in the "Adventureworks2008R2" database in the SSMS of "XYZ\yashwant.kumar".


Figure 6.3: User Without Login

Step 4
Connect SQL Server with the loginname "ianrox".


Figure 6.4: Connect SQL Server

Step 5
Run the following query in the SSMS of login "ianrox".


Figure 6.5: Run The following Query

The preceding query is giving an error and it is quite obvious because user "ianrox" doesn't have access to the "AdventureWorks2008R2" DB.

To rectify this problem we will get the benefit of a user without a Login. We have already created the user "Joe_UserWithoutLogin" in Step 3 that doesn't have a login.

Step 6
Here we will grant db_datareader access to "Joe_UserWithoutLogin" in the SSMS of "XYZ\yashwant.kumar" to access the "AdventureWorks2008R2" DB.


Figure 6.6: AdventureWorks2008R2

Step 7
Here we will impersonate the user "Joe_UserWithoutLogin" who already has access to the "AdventureWorks2008R2" DB to login "ianrox" in the SSMS of the login "XYZ\yashwant.kumar".


Figure 6.7: Already Success

Step 8
Now the user "ianrox" should be able to Execute As the user "Joe_UserWithoutLogin" to read the tables from the "AdventureWorks2008R2" database in the SSMS of the login "ianrox".


Figure 6.8: Execute

From the query, now ianrox is able to fetch records from the "AdventureWorks2008R2" database. It is giving 290 rows as a result.

So here it is clear how to provide permissions to "Joe_UserWithoutLogin" and impersonate it in any user to access the database or fetch records. It is also clear we were not able to fetch records from the "AdventureWorks2008R2" database from the SSMS of login "ianrox" that is clearly visible in Step 5 but after impersonating of the user "ianrox" we can fetch records which is very clear in Step 8.

At this stage I am assuming that you are able to understand the importance of the user without login concept.

T-SQL to list users without logins

Execute the following query to list users without logins:

1. use AdventureWorks2008R2 go  
2. SELECT  
3.     name,  
4.     principal_id,  
5.     type_desc,  
6.     authentication_type_desc,  
7.     sid  
8. FROM  
9.     sys.database_principals  
10. where  
11.     authentication_type_desc = 'none'  
12.     and type_desc = 'sql_user'  
13.     or use AdventureWorks2008R2 go  
14. SELECT  
15.     name,  
16.     principal_id,  
17.     type_desc,  
18.     authentication_type_desc,  
19. sid  
20. FROM  
21.     sys.database_principals  
22. WHERE  
23.     DATALENGTH(sid) > 16  
24. AND sid not in   
25. (  
26.     SELECT  
27.     sid  
28.     FROM  
29.         sys.server_principals  
30. )  
31. AND type = 'S'  
32. AND principal_id > 4 [ Read More ]  

Figure 7: T-SQL to determine user without logins

Drawback of the preceding T-SQL
The only drawback is that you must execute the preceding T-SQL for every database but if you are good in coding then this is not a problem for you. This is a problem for me because I am a lazy guy and average in coding.

Problem you can experience

We are talking about permissions and impersonation here. There is a common problem also that you can encounter when deleting a user or login and the following common error message you will see.

“The database principal has granted or denied permissions to objects in the database and cannot be dropped, (Microsoft SQL Server, Error: 15284”).


Figure 8: Showing error 15284, when deleting a user or login

Troubleshooting of problem
Here I am trying to delete the user "UserwithoutLogin" that has impersonate permissions to user "ianrox" and from the error message it is quite obvious we cannot delete it. So what do we do next to solve this problem.? We can solve the problem in the following procedure.

Step 1
Try to determine the permission name and grantee with the following query for the database in which the user exists and you are getting an error.

1. use AdventureWorks2008R2   
2. go  
3. select * from sys.database_permissions  
4. where  
5. grantor_principal_id = user_id('UserWithoutLogin') GO  

Figure 9: Finding out grantee_principal_id using grantor_principal_id and user_id

Step 2
Determine the user name where the impersonate permission is given by the "UserWithoutLogin" user with the following query.

From step 1 we are able to find grantee_principal_id, so using this we will try to determine the user name for that grantee_principal_id using the following query.

1. SELECT * FROM sys.[database_principals] WHERE [principal_id] = 8  
2. go  

Figure 10: Finding out user that have impersonate permission

Step 3
Now we have every detail to rectify our problem. The user "ianrox" has impersonate permission from "UserWithoutLogin".

So here we can revoke permissions from the user "ianrox" using the following query and then we will be able to delete the user "UserWithoutLogin".

REVOKE IMPERSONATE ON User::[UserWithoutLogin] TO [ianrox]

Step 4
You can delete the user by right-clicking on the user name and select the option to delete. The user will be deleted successfully.

Orphaned Users vs User Without Logins

Now I will put some light on another interesting fact, that is Orphaned Users. Some people say that both are the same, I completely disagree with this. According to Mr.Julian Watson, a blogger and owner of the SqlMatters website. In his words, “The users without logins are sometimes confused with orphaned users, however these two types of users are quite different. A user without login is a special type of user that has deliberately been set up without an associated login. In contrast, an orphaned user is one where the user is not currently associated with a login, most commonly because a database has been restored from another server and the association with the login has either been lost or the login does not exist on the new server. Normally when orphaned users are discovered, they are just connected back to their associated logins. However a user without login is one that does not have, and cannot have, an associated login. While this might not sound like a very useful type of user (and indeed in my experience they're not that commonly used) they can be used in conjunction with impersonation from another login. Sometimes they are used as a replacement for application roles”.

Now I completely agree with the preceding statement of Mr. Julian Watson and I assume that everybody reading this article also agrees. Again a huge thanks to Julian Watson for his work, he did a great job in making it very clear in simple words. It makes sense also.

1. User name: user name is the box in which we can provide the name for the database user. In my case the user name is "yashwant".


Figure 11: Illustration of database user name

2. The FOUR by default Database Users: With the creation of every database, whether it is a system database or user database, four types of users are created by default.

• DBO.
• Guest.
• Sys.
• INFORMATION_SCHEMA.

Figure 12: Showing four database users created by default

Database User	Description
DBO	Also known as Database Owner, it has all privileges and rights to do any task in the database. The DBO user also owns the default schema dbo. We cannot drop the DBO user. Note: Members of sysadmin, sa and fixed server role are mapped to dbo.
Guest	The Guest user is disabled by default for security purposes. The Guest user is a member of the public role and has all permissions assigned to that role. We cannot drop the guest user either, we can only enable and disable it.
SYS	The sys user gives other users access to system objects such as system tables, system views, extended Stored Procedures, and other objects that are part of the system catalog. The sys user also cannot be dropped from the database.
INFORMATION_SCHEMA	The INFORMATION_SCHEMA user owns all the information schema views installed in each database. It is used to retrieve the metadata and cannot be dropped.

[ Read More ]

Point to be noted

Guest, sys and INFORMATION_SCHEMA users don't have any logins, we can say these are the users without logins.
We can execute the following query to support this point.

1. use AdventureWorks2008R2  
2. go  
3. SELECT name,principal_id,type_desc,authentication_type_desc,sid   
4. FROM sys.database_principals   
5. where authentication_type_desc='none' and type_desc='sql_user'

Figure 13: Showing guest, sys, information_schema are users without logins

1. Login Name
It is the place where we enter the login for the user. (Refer to Figure 11).

In Figure 8 "XYZ\yashwant.kumar" is the login name for the database user "yashwant".

2. Default language
We can select the desired language from the drop down list (Refer to Figure 11).

3. Default schema
We can specify the schema by browsing from the list of schemas that will own objects created by the user "yashwant".

In our case the default schema is dbo. (Refer to Figure 11.)

What is schema
A schema is a namespace that exists independently of the user who created it. We can also say that it is a container for objects in the databases. Schemas are the new security feature from SQL Server 2005 onwards.

How to view schema

We can see the schema by navigating to Database >> Security >> Schemas in the Object Explorer of SSMS.

or

We can see a list of schemas by executing the following query.

1. use TestDB  
2. go  
3. SELECT * FROM sys.schemas  
4. go  

Figure 14: Showing list of schemas in TestDB database

Features of schema:

• The owner of any schema is the user who created it. We can check the owner of the schema by right-clicking on the schema name and then clicking on properties.
  
• The ownership of a schema can be transferable from one user to another. To do this just right-click on the schema name and click on properties. Under the schema owner you will find a search button. Then we can change the ownership from the given owners.
  
• We can move objects from one schema to another schema.

Example

Step 1
Execute the following query.

1. use AdventureWorks2008R2  
2. go  
3. SELECT name, [schema] = SCHEMA_NAME(schema_id)  
4. FROM sys.tables  
5. WHERE name = 'Employee'  
6. go

Output: Here the "Employee" table is in the "HumanResources" schema as in Figure 15.


Figure 15: Showing default schema for "Employee" table in "Adventureworks2008R2" database

Step 2
In this step we will transfer the "Employee" table in the "HumanResources" schema to the "dbo" schema using the following query.

1. use AdventureWorks2008R2  
2. go  
3.    ALTER SCHEMA dbo --new schema name where we want to move it  
4.    TRANSFER humanresources.Employee --old schema name with table which we want to move  
5. go

Figure 16: Transferring "Employee" table to "dbo" schema 

• Multiple users can share a single default schema.

Execute the following query to the users and schema associated with them.

1. use AdventureWorks2008R2  
2. go  
3.    select name, type_desc, default_schema_name from sys.database_principals  
4. go 

In my case the user "yashwant" and "Jim" are sharing the same schema, "Human Resources", and that is also clear from the screenshot in Figure 17.


Figure 17: Showing multiple users can share a single default schema 

• One schema can contain objects owned by multiple users.

If multiple users can share a single schema then it is also possible that the objects within that schema are also owned by multiple users.

Owned Schemas
This page lists all the possible schemas that can be owned by the database user. We have already discussed schemas refer heading 3 default schema.
Membership
The Membership page lists all the possible database membership roles that can be owned by the user. Refer to Basics of Database Administration in SQL Server: Part 3 for more information.

Securables
Securables are the resources that we can assign permissions, either at the server level that includes resources like Endpoints, Logins, Server Roles and Databases or at the database level that includes resourcess like Users, Database Roles, Certificates and Schemas. Refer again to the basics of Database Administration in SQL Server: Part 3 more information.

Extended Properties

Extended properties are metadata that allow us to customize the information, storing the data within the database and describe table, procedure, column, function, user and the database itself.

We can create, update, delete and of course view extended properties.

Creating Extended Properties
Using the Stored Procedure "sp_addextendedproperty" we can create extended properties. Here I am creating extended properties for the database version of the database TestMore.

Example

1. USE [TESTMore]  
2. EXEC sys.sp_addextendedproperty   
3. @name = N'DatabaseVersion',   
4. @value = N'11.0.3000.0'  

Figure 18: Creating extended properties

Viewing Extended Properties
We can view extended properties in the following three ways. 

• Using table "sys.extended_properties".
• Using the "fn_listextendedproperty" function.
• Using SSMS GUI.

Using table sys.extended_properties
Using the query in Figure 19 we can view extended properties:

1. SELECT * FROM sys.extended_properties;

Figure 19: Viewing extended properties

Using the fn_listextendedproperty function
"fn_listextendedproperty" is a builtin function, it returns the Extended Property values of the database object.

1. SELECT name, value FROM fn_listextendedproperty(default,default,default,default,default,default,default)  

Figure 20: Viewing extended properties by fn_listextendedproperty

Using SSMS GUI
Open the Object Explorer and right-click on the database properties.



Figure 21: Viewing extended properties by SSMS GUI

Updating Extended Properties
Using the Stored Procedure "sp_updateextendedproperty" we can update extended properties.

Example

1. USE [TESTMore]  
2. EXEC sys.sp_updateextendedproperty   
3. @name = N'DatabaseVersion',   
4. @value = N'11.0.3000.1'  

Figure 22: Updating extended properties

Deleting Extended Properties

Using the Stored Procedure "sp_dropextendedproperty", we can delete/drop extended properties.

Example

1. USE [TESTMore]  
2. EXEC sp_dropextendedproperty   
3. @name = N'DatabaseVersion'  

Figure 23: Deleting extended properties

Want to learn more about extended properties? The consult the book Transact-SQL User-defined Functions By Andrew Novick.
Note: All screenshots are applied to SQL Server 2012 Enterprise Evaluation Edition.

References: 

• msdn.microsoft.com
• technet.microsoft.com
• www.sqlmatters.com
• blog.lessthandot.com
• Microsoft SQL Server 2005 Security Best Practices - Operational and Administrative Tasks; SQL Server Technical Article by Bob Beauchemin, SQLskills.com.
• Transact-SQL User-defined Functions By Andrew Novick.
• Microsoft SQL Server 2005 Programming For Dummies By Andrew Watt.
• Mastering Microsoft SQL Server 2005 By Mike Gunderloy, Joseph L. Jorden, David W. Tschanz.
• Beginning SQL Server 2012 for Developers By Robin Dewson.
• Beginning Microsoft SQL Server 2008 Administration By Chris Leiter, Dan Wood, Michael Cierkowski, Albert Boettger.

Conclusion
This is all about SQL Server Users. I tried hard to explain what I know using figures and tried to touch and cover all the things related to SQL Server Users. I hope you will not get bored and will inistead enjoy this. To know more about SQL Server Database Administration click on below links:

• Basics of Database Administration in SQL Server: Part 4
• Basics of Database Administration in SQL Server: Part 5
• Basics of Database Administration in SQL Server: Part 6

Happy reading and keep sharing your knowledge.