Different States of Database

What are the states of the database?

There are seven states, they are as follows

• Online
• Offline
• Standby
• Suspect
• Restoring
• Single user
• Restricted user

Online: When the database is in online mode, it is available to access the data.

Offline: Here the database is unavailable and it is used when to move the database files from one disk to other.

Standby: When the database is in standby mode it has only read access. This mode is used to restore the secondary server in the log shipping configuration.

Suspect: The database is unavailable because of there is corrupted/deleted the database files ie., data file and log file. 

Restoring: There may be restoring of one or more files, it is seen in the mirror server in mirroring configuration.

Single user: Only one connection is allowed for database, it is used in  restarting the server and rebuilding master database.

Restricted user: Only sysadmins can access the database.

Microsoft SQL Server

SQL Versions and EOL Support 

Introduction to MongoDB

MongoDB is a No SQL database. It is an open-source, cross-platform, document-oriented database written in C++.

MongoDB is an open-source document database that provides high performance, high availability, and automatic scaling.

What is the purpose of building MongoDB?

All the modern applications require big data, fast features development, flexible deployment, and the older database systems not competent enough, so the MongoDB was needed.

The primary purpose of building MongoDB is:

Scalability

Performance

High Availability

Scaling from single server deployments to large, complex multi-site architectures.

Key points of MongoDB

Develop Faster

Deploy Easier

Scale Bigger

Apply a new sql server collation

 Sometimes we will get a requirement to change server collation post installations.

In such cases please follow these steps.

Execute the command below, a lot of information will appears and no user action is required, just close the prompt window after the execution ends. The parameter "-s" is only necessary if more than one sql instance exists on the target machine.

Sql server -m -T4022 -T3659 -s"SQLEXP2014" -Q"SQL_Latin1_General_CP1_CI_AI"

After restore a database with override option, while trying to map a user to that database it will throw error as : User, group, or role 'hello' already exists in the current database. (Microsoft SQL Server, Error: 15023)

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for User 'hello'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

User, group, or role 'hello' already exists in the current database. (Microsoft SQL Server, Error: 15023)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=15023&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

In this case we have to delete/drop that user from that particular database, again we have to create a user under that database.

How to enable SQL Server Agent?

How to enable SQL Server Agent

Procedure:

Execute the following Queries and refresh the server

Step1:

exec sp_configure 'agent XPs'
if it display an error that advanced options are disabled then by running the following query enable the show advanced options and reconfigure

Step2:  Run the Query to enable advanced options

exec sp_configure 'show advanced options' , 1
go
reconfigure
go

step 3:
exec sp_configure 'show advanced options'
go

Step4:
Enable agent XPs by using the query

exec sp_configure 'agent XPs'
go
exec sp_configure 'agent XPs',1
go
reconfigure
go
exec sp_configure 'agent XPs'

Step5:
now restart the SQL Server Agent / Server. so that SQL Server Services should be enabled

still if it doesn't start then

Step 6:
goto start-->run type services.msc and press OK

Step 7:
Services window will be opened then goto SQL Server Agent, Right click on it and go for start

Now we can see the SQL Server Agent started in our Object Explorer

When we are trying to attach the database it will throw an error as CREATE FILE encountered operating system error 5(Access is denied.) While attempting to open or create the physical file.... (Microsoft SQL Server, Error 5123).

For this error the solution is that there are two different logins in the server, here detaching is happened with one login and while attaching with other login. So there is no access to attach the database, while detaching the database whoever detaching the database that user is the owner of the mdf file so we need to change the permissions.

right click on that mdf file --> select properties--> check permissions of the mdf file--> there we can see only one account has permission on that mdf file, because that account is detached the database so that the user have full rights.

To solve this issue click on 'Add..' add that other login and give full rights to that user, do this process for the ldf file as well. Once you have completed this task click on 'OK' button.

Now try the attach process, it will attach the database successfully.