Hi all,
You all are thinking, what I will write here on “Database properties”. Right, it’s
a big question in all’s mind. However, I am writing here for each and every property
of SQL Database and will discuss in details in future articles.
Let’s start one by one.
1. General Properties
In above image you can see different properties as below:
- Last Database Backup: It shows the Timestamp of last backup.
- Last Database Log Backup: It shows the Timestamp of last log backup
- Name: It shows name of Database
- Status: It shows status of Database like (Online, Offline etc)
- Owner: It shows name of owner of Database like (sa, dbo or any user)
- Date Created: It shows creation date of Database
- Size: It shows allocated space to Database.
- Space Available: It shows available space to Database (Allocated space – Used space).
- No of Users: It shows no of users those who have permission to Database.
- Collation: It shows the current collation for the database. It is a big thing to
understand. We will see in future series.
2. Files
In above image you can see different files (mdf, ldf, ndf) as below:
- Main file (.MDF): It shows details of .MDF file like name of file with path, size
of file and it’s growing options. The file holds the data for database like (Tables,
SPs, Functions etc)
- Log file (.LDF): It shows details of .LDF file like name of file with path, size
of file and it’s growing options. The file holds the data of transaction occurred
on database and its tables. i.e log of inserted data, deleted data and updated data.
- Partition file (.NDF): when you scattered you data to multiple files then SQL server
created .NDF files. The file also holds the data for database like (Tables,
SPs, Functions etc)
3. File groups
In above image you can see different file groups as below:
- Primary: When you create database it automatically creates primary file group for
Data file and log file.
- Secondary: When you partition you database in different file (.MDF and .NDF) then
the some data will go to Secondary group.
4. Options
In above image you can see different properties as below:
- Auto Shrink: It shows the Flag of Auto shrink of data and log files.
- Close Cursor on Commit Enabled: It shows the flag of cursor automatically close
when commit transaction.
- Default Cursor: Scope of cursor Global or Process level (In same SP, Function etc)
- Database Read-only: It shows the Flag that database is read-only or in write mode.
- Restrict Access: It shows that database has set multiuser access or single user.
5. Change Tracking
In above image you can see different properties as below:
- Change tracking: It shows the database is enabled to track the changes occurred
on database, table (SQL objects).
- Retention period: It shows the cleanup period in numbers (1,2, …) to change history.
- Retention period unit: It shows the cleanup period unit (days, hours, minutes)
to change history.
- Auto cleanup: It shows the database is enabled to clear the change history automatically.
I think, you all have questions what is the change track. It is as simple as like
word. If you enabled the database change track enabled then SQL server manage the
history of each and every change occurred on data.
6. Permissions
In above image you can see different properties as below:
- It shows different grant options (Facets). Granter have to just select the user
and check the check box for permissions which they want to assign.
7. Extended properties
In above image you can see different properties as below:
- Extended property is useful to right comment related to database.
8. Mirroring
When the data is too sensitive and you want high availability of your data then
you have some option to achieve this. Mirroring is one of them.
In that data will be transferred from one server to another for each and every execution
of query excepts (reading of data) like insert, update, delete as well as sql object
creation script.
When ever user execute insert/update/delete data will effect the same thing on mirrored
server. means you will get same data on 2 different server at a time.
9. Log shipping
When the data is too sensitive and you want high availability of your data then
you have some option to achieve this. Log shipping is one of them.
It will not affect the another server at a time but will create image of data and
ship that to remote server in time interval and update the remote database.
Mean by both server databases are in sync but periodically. There may be a change
you will not get updated data when you executing query but you get after execution
of schedule of log shipping.
Hope, you all beginner learned a lot.
Thanks,
Alpesh Patel