MongoDB CRUD Operations

MongoDB provides rich semantics for reading and manipulating data .CRUD stands for Create Read Update & Delete . CRUD terms are the foundation for all interactions with the database .

CREATE :

In MongoDB we can create the database , collection .

How to create the database in MongoDB ?

Syntax : Use < Database Name >

Ex: use MODEL

Test is the default database in MongoDB .

How to verify what is the current database ?

Just Type db in mongoshell

How to check the databases present in the server ?

Show dbs

Note: by default it will not show the database . In order to display the database we need to insert at least one document into it .

Creating Collection :

Syntax :  db.createCollection(name,options)

Name : name of the collection to be created .

Options : It is an optional statement  used to specify memory size and indexing .

Ex: 

READ:

You can find more about read from my blog post .

UPDATE:

MongoDB introduced update( ) and save( ) methods  to update the document into a collection .

The update( ) method update values in the existing document .

The save( ) method replaces the existing document with the document passed in the save ( ) method .

update( ) syntax :

db.collectionname.update (selection_criteria , updated_data)

Ex:

SQL Server DBA Interview question and answers Part 2

1. What is Trace Flag ? Where do we use it ?

Trace Flags are used to switch on specific server characteristics temporarily .
DBCC TRACEON is the command to set the trace flags .
Once activated , trace flags will be in effect until the server is restarted .
Trace Flags are frequently used for diagnosing performance issues .

Ex: the trace flag 3205 is used for disabling hard compression for tape drives when an instance of SQL Server starts .

2. If the SQL Server crashes and if all the differential backups are bad , when is the latest point in time you can successfully restore the database ? Can you recover the database to the current point in time without using any of the differential backups ?

You can recover to the current point in time as long as you have all the transaction log backups available and they are all valid .
Differential backups do not effect the transaction log backup chain.

3. What methods are available for removing fragmentation of any kind on an index in SQL Server ?

ALTER INDEX ... REORGANIZE
ALTER INDEX ... REBUILD
CREATE INDEX ...DROP EXISTING (cluste)
DROP INDEX
CREATE INDEX

4. What is the fundamental unit of storage in SQL Server log files and what is it's size ?

A Log Record , size is variable depending on the work being performed .

5. How many different types of pages exist in SQL Server ?

There are 9 pages available .

Data Page
Index Page
Text / Image (LOB , ROW_OVERFLOW,XML)
GAM (Global Allocation Map)
SGAM ( Share Global Allocation Map)
PFS ( Page Free Space )
IAM (Index Allocation Map )
BCM (Bulk Change Map )
DCM (Differential Change Map )

6. If you need REBUILD a non-clustered index that is 10 GB in size and have 5 GB of free data-file space available with no room to grow the data file(s) , How can you accomplish the task ?

You can use SORT_IN_TEMPDB=ON during index rebuild operation .

SQL Server uses tempdb to store the intermediate sort results when SORT_IN_TEMPDB=ON  which are used to build the INDEX while reindexing / rebuilding .

Ex:

Use AdventureWorks2012
GO
Alter Index All on Production.product REBUILD
with
(
Fillfactore=80,Sort_In_tempDB=on,Statistics_Norecompute=ON
);


7.  What special type of page with in SQL Server that is responsible for tracking (via simple bit map ) extents that have been modified since the last "BACKUP DATABASE" statement ?

BCM Pages . Bulk changed map stores all extents which have been modified since last full backup .


8. How is it possible to capture the IO and time statistics for your queries ?

Use SET STATISTICS IO and SET STATISTICS TIME setting in your queries or enable the setting in your management studio .

9. What is fill factor ? What is default fill factor value ?

Fill factor is a setting that is applicable to indexes in SQL Server . The fill factor value determines how much data is written to an index page when it is created / rebuit .

By default the fill factor value is set to 0.

10. What are the different types of database compression introduced in SQL Server 2008 ?

Row compression & Page compression .

11. What are the different types of upgrades that can be performed in SQL Server ?

in-place upgrade and side-by-side upgrade .

12. What operations comes first during recover - Rollback of Roll forward ? 

Roll Forward

13 .What does sp_validatename ?

It checks for a valid SQL Server identifier .

14. What options / arguments can be specified in a BACKUP LOG statement to keep inactive log records from being truncated ?

For SQL Server 2005 and previous versions BACKUP LOG with NOTRUNCATE option .
After 2005 database recovery model should be changed from FULL to SIMPLE .

15. What are the primary differences between an index reorganize and index rebuild . ?

A reorganize in an "online " operation by default where as rebuild is an "offline" operation .
A reorganization only affects the leaf level of an index.
A reorganization swaps data pages in-place by using only the pages already allocated to the index
A rebuild uses a new pages / allocations.
A reorganization is always a fully-logged operation ; a rebuild can be a minimally-logged operation.
A reorganization can be stopped mid-process and all completed work is retained; a rebuild is transnational and must be completed in entirety to keep changes.




I hope the article was useful! And I need to let you know that this won’t stop here .keep in touch and feel free to ask any doubts related to this topic .

SQL Server DBA Interview question and answers Part 1

1. A user had a hard-disk crash where .ldf files were located . But mdf and ndf files are accessible. How the user can over come this situation ?

CREATE DATABASE FOR ATTACH_REBUILD_LOG can help the user to create a database without log.

2. What LOCK does truncate table statement acquire ?

Page Lock & Table Lock .

3. You have a table with 4,294,296,295 rows . You issues an update that should effect all but 3 or 4 Rows . You want to determine how many rows were effected by the update . How can you do this .

We need to use SELECT ROWCOUNT_BIG () function to get the value .

Note : We cannot use @@ROWCOUNT to check about it hence @@ROWCOUNT is an integer and it can work only for 2^31 rows .

4. What is the default protocol when you install SQL Server ?

TCP/IP 

5. What are different protocols available in SQL Server ?

TCP/IP , Named Pipe and Shared Memory .

6. If you don't specify the size with cast and convert , what is default size of char data ?

30

7.  What is the maximum size of Index Key ?

900 bytes .

8. What is maximum number of columns allowed in an index key ?

16

9. Can you run a differential backup on a database with Simple Recovery Model ?

 

Yes . It has nothing to do with differential backup.

10. Does transnational replication replicate CLR , Stored procedure , Full-Tex and CLR data types .

Yes .

11. In service broker , if your message does not send and has an error , where do you find this error ? .

In sys.transaction_queue.transmission_status .

12. What is default interval of check point .

SQL Server decides itself .

13. What is the function of SQL Server Agent ?

SQL Server Agent is a windows service which handles the tasks scheduled within the SQL Server environment . 

These tasks also called as Jobs .

These jobs may run on a predefined schedule or on demand through a Trigger .

This service is useful in determining why a particular job did not run as intended .

14. What is Buffer Cache ?

Buffer Cache is a memory pool in which data pages are read .

Default Buffer Cache value is 95 %  , another 5 % is needed for physical disk access .

If the value falls below 90% then it is the indication of more physical memory required on the server.

15. What is Log Cache ?

Log Cache is a memory pool used to  read and write the log pages .

A set of cache pages are available in each log cache .

The synchronization is reduced between log and data buffers by managing log caches separately from the buffer cache .

SQL DBA : How to restart an interrupted database restore in SQL Server

Route Cause :

My database restoration failed due to network failure .

Solution :

A database administrator can use RESTORE DATABASE .... WITH RESTART command to finish restoring an interrupted database restore operation .

In this scenario Database state would be in Restoring ...  Ex : Adventureworks (Restoring...) .

Now we can resolve this situation in 2 steps .

Step 1.  Restore FILELISTONLY from disk =' C:\Backups\Adventureworks .bak '

Note : The above step is used to get list of the backups on the file i.e  get backup information from backup file

Step 2. Restore database Adventureworks  from disk = ' C:\Backups\Adventureworks .bak ' with restart .

Note : Step 2 does the actual restore with restart option .

Now the database will restore successfully and will allows use connectivity .


I hope the article was useful! And I need to let you know that this won’t stop here .keep in touch and feel free to ask any doubts related to this topic .

SQL DBA : Error 5030 Fix Unable to rename database name

Route Cause :

Still sessions or user connections exists on this database .

Solution :

1. Kill all the existing connections with the user acceptance (I would believe you are the owner and the user to this DB ) and rename the db. 

Note : If you are only the person using this DB then close all opened sessions pointing to your database and rename .
                                                          (or)  

1. Set the database to single user mode :

  ALTER DATABASE  <OldDBName > SET SINGLE_USER WITH ROLLBACK IMMEDIATE  

2.  Try to rename the database : 

 ALTER DATABASE <OldDBName >  MODIFY NAME = WRITE HERE YOUR NewDBName 

 3. Set the database to Multiuser mode:  

ALTER DATABASE <NewDBName> SET MULTI_USER WITH ROLLBACK IMMEDIATE

SQL DBA :What are the database states and explain them ?

The database states are

                                   Offline
                                   Online
                                   Recovering
                                   Recovery Pending
                                   Restoring
                                   Suspect
                                   Emergency

Offline :

When db is offline state then the database is not accessible for user connections. If you don't want to connect to the database then you can make it as offline .

Ex:  You have migrated the db to new server and don't want to users connect to old db accidentally then you can make the db offline .

Online :

When the db is online state then the db is available to the users for access .The primary file group is in online even though the undo phase of recovery may not have been completed .

Recovering :

The db state would be in transient during recovery . The db will become to online automatically once recovery succeeds . If the recovery fails then the db will goes to suspect mode and db is unavailable.

Recovery Pending :

When the db is in recovery pending state means the db encountered a resource related error during recovery .The db is missing files and DBA interventions is required in such case .

Ex: we faced this situation when the developer moved database files either mdf or ldf files to different location without DBA awareness.

Suspect :

When the db in suspect state then db is unavailable for the user connections .

Emergency :

User will set the db status to emergency to repair or restore the database during db goes to suspect. Database would be in single user mode and read only operations possible . Sysadmin permission is required to make the db into emergency mode .

SQL DBA : Troubleshooting database suspect mode issues in SQL Server

Problem 

My database went to suspect mode then how can I resolve it ?

Cause  :

Database will go to Suspect mode due to various reasons which are listed below .

If 

Database files are corrupted or there is disk issue . 

Database restoration process was failed unexpectedly .

If data file was full.

After detach and attach the actual files path is not recognized by SQL Server when you ran sp_helpdb

Solution

First Check the error logs to identify the route cause

1. If the data file was damaged or disk failure then db engine issues 17204 error

Take tail log backup

Restore full backup with No Recovery 

Restore recent differential backup with No Recovery 

Restore all log backups if any made after recent differential backup with No Recovery 

Restore tail log backup with Recovery .

2. If the log file was damaged then db engine issues 17207 error 

Try to take tail log backup with another copy of log file if available with RAID level .

If the log file is not available then make it online by running the following commands where there may be data loss .

Step 1: Make the db into single user .

Alter database < database name > set sing_user  with rollback immediate

Step 2 : Set the db into emergency mode .

Alter database < database name > set emergency

Step 3 :  Run the check db with required repair level

DBCC CHECKDB ('Database Name', REPAIR_ALLOW_DATA_LOSS)

Step 4 : Set the db into multi user mode .

Alter database < database name > set multi_user

SQL DBA : Troubleshotting SQL Server Service Problems .

Problem :

My SQL Server service is not started . What may be the possible scenarios ?

Possible Reasons 

      * Logon Failure

              Problem with service account .

      * 3417

              Files are not present in the respective path or there are no permissions on target folder where the files are present .

      * 17113

              Master files are moved to different location but not mentioned in startup parameters.

      * Service cannot be started in timely fashion consult the event log or applicable error logs for detail .

         

       Solution 1 :  Insufficient resources , try to stop some other instances and start again .

       Solution 2 : Go to windows application error log to get additional details and if you find any error such as service didn't start due to a log on failure then immediately go to the SQL Server service properties and change the updated passwords.

       How to find errors ?

       1. Using windows event log : Start --> eventvwr --> In the Right side check for the errors .

       2. Using SQL Server error log file .

 Go to respective instance Log folder and open error log in notepad and check for the errors.

               

SQL DBA : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support strong text requested protocol

Problem :

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support strong text requested protocol .

Solution :

In general this issues will be occurred in SQL Express editions in order to fix it follow the below steps .

1. Start -->  Microsoft SQL Server (Version ) --> Configuration Tools --> SQL Server Configuration Manager

2. Make sure for any client protocols for SQL Express needs that TCP/IP is set to "Enabled" and Named Pipes are set to "Disabled".

After the above steps you need to restart SQL Server Browser and SQL Server (Instance ) services.

SQL DBA : Error 26 Error Locating Server / Instance specified .

The reason for throwing this error could be Wrong Server Name , Disabled remote connections , firewall blocking , SQL Server browser services were stopped .

Work Around 

1. Make sure the server on which SQL Server is running can be accessible ? 

2. Make sure the server name is reachable by pinging command from command prompt .

3. Make sure SQL Server browser services are running .

4. Corresponding TCP / IP port or pipe is not specified in the connection string .

5. If firewall is enabled on the server then you need to put sqlbrowser.exe and / or UDP port 1434 into exception .

SQL DBA : Trouble shooting User Connection .

Scenario :

One of a user is unable to connect to SQL Server . What may be the possible scenarios ? How to troubleshoot it ?

Possible Scenarios

1. Error :26

                SQL Browser
   
                Firewall

                No connectivity between client and server

2. Error :28 

                Named Pipes & TCP /IP was disabled

3. Error :40

                If you are getting the error in Local then Instance service is not running in case of in Remote server then TCP / IP is disabled .

4. Error : 18456

                Login Failed .( Invalid login or password )

5. Expired Timeout

                 Network Issue

                 Service is busy

                 In server max sessions are open

                 No available session memory

6. Connection forcibly closed

                 Update the client compute to the server version of the SQL Server Native Client .

7. In single user mode if any other service is connected with the db engine then it doesn't allow                 connections.
             
               

SQL DBA : Troubleshooting Host Name Change

Scenario :

When the machine name is changed where we have installed SQL Server . All the instance services are started but replication , jobs , Alerts and Maintenance plans causes errors .

Solution :

We have to rename the instance .

Steps :

1. Check the old server name as follows

          Select @@Servername

2. Drop the server and add the new server name

          sp_dropserver <Old Server Name>

          sp_addserver <New Server Name > ,local

3. Restart the instance

4. Check the server name again

          Select @@Servername

SSIS : Name some of new feautures from DTS to SSIS ?

The below are the few points in SSIS over DTS

* SSIS Check Points.

* SSIS Logging.

* SSIS Package Configuration.

* SSIS Breakpoints.

* SSIS Batch Processing.

* Dynamic flat file connections.

* Merge Join

M102 : MongoDB for DBA's Home Work for Week 1

 Homework 1.1 

What would be the output of the below script if you run in mongo shell ?

db.isMaster().maxBsonObjectSize

16777216

Below is the referenced screen shot 

 Homework 1.2 
Now, import its contents into MongoDB, into a database called "pcat" and a collection called "products". Use the mongoimport utility to do this.When done, run this query in the mongo shell what would be the result ?

      db.products.find( { type : "case" } ).count()

Result is 3

Below is the referenced screen shot

       Homework 1.3 
At this point you should have pcat.products loaded from the previous step. You can confirm this by running in the shell:

       
       db.products.find()

          // or:

         db.products.count()

        // should print out "11"

     Now, what query would you run to get all the products where brand equals the string ACME”?

      db.products.find({brand:"ACME"})

Below is the referenced screen shot

 Homework 1.4 
How would you print out, in the shell, just that the value in the "name" field, for all the product documents in the collection, without extraneous characters or braces, sorted alphabetically, ascending? 

      var c = db.products.find({}).sort({name:1}); c.forEach( function(doc){ print(doc.name) } );

      var c = db.products.find({},{name:1,_id:0}).sort({name:1}); while( c.hasNext() ) print( c.next().name);

Below is the referenced screen shot

    

       

MongoDB : Interview question and answers

What were the big differences in hardware over the last few decades that MongoDB attempted to address ?

Parallelism of Cores and Parallelism of Servers.

When Scaling out horizantolly i.e adding more servers to contain your data what are the problem we will face ?

The servers must communicate with one another eating up network band width.

The need of redundancy increases as the likelihood of some failures in the system per unit of time increases.

What causes significant problems for SQL when you attempt to scale horizontally (to multiple servers) ?

We will face significant problems with Joins and Transactions

What are the advantages of representing our data using a JSON like format ?

JSON is optimized for use with Java Script

JSON Syntax is similar to that of data structures used in many programming languages and is there of familiar to developers

JSON presents a flexible and concise framework for specifying queries as well as storing Records.

How Many data types are there in JSON ?

JSON supports 6 types of data types that are Strings , Numbers ,boolean (T/F), Null , Arrays and Objects / Documents .

Why do we need to represent our data in BSON rather than JSON syntax in the system ?

Fast machine scanability and Stronger typing than JSON.

By Default which database does the Mongo shell connect to?

Test

The mongoimport utility can import what types of data ?

mongoimport utility supports JSON , CSV and TSV files

In order to query a collection in the mongo shell what do we need to use ?

db.collection.find()

You have a collection where ever document has the same fields and you want to look at the value of the "_id","name" and "email" fields in order to see their format . Further more , you want to eliminate all other fields from the query results what query you might write ?

db.collection.find({},{name:1,email:1})

You want to query the "people" collection and you want the results of the query to include only documents where age is 50 and you want to look at  all the fields except "email" what query should you write ?

db.people.find({age:50},{email:0})


If you want to add a new key : value pair to the documents in the "shapes" collection what methods could you use ?

db.shapes.update()

db.shapes.save()

Assume we have some sample collection in our products collection such as 
{name:"AC1 Case Green",color : "green" ,price:12.00,for:"ac1",type:["accessory case"],available:true }

How we would query in the shell for all products that are cases for an ac9 phone ? That is , where type contains the value "case" and for equals "ac9"?

Please use filed names in the order they appear in the question and use double quotes around values ?

db.products.find({"type":"case", "for":"ac9"})

If you want to run a query on the collection "books" and sort ASCII betically by title on the query then how you will write ?

db.books.find().sort({title:1})

Recall the documents in the score collection 
({"student":0,"type":"exam","score":75}
write a query that retrieves document type "exam" , sorted by score in descending order and skip the first 50 and showing only the next 20.

db.scores.find({type:"exam"}).sort({score:-1}).skip(50).limit(20)

MongoDB : Basic document retrievals queries from Collection in MongoDB

Syntax to view the documents in the collection is

db.collection.find()

db - database connection window

collection - collection name

find - queries

( ) - write queries in JSON format .

Ex:

Read the records from Product collection

db.Product.find()

with the above query we cannot read or query the results in readable format so for this we need to use toArray() method

db.product.find().toArray()

Display only name document from the product collection

db.product.find({},{name:1})

Now you will see the name document details with _id value

Note : To avoid _id value we need to use _id:0 in JSON query

db.product.find({},{name:1,_id:0})

Display top 10 documents details from collection in readable format .

db.product.find().limit(10)

Display 4 documents (records) by skipping first 2 docs from product collection

db.product.find().limit(4).skip(2)

Filter the product details where price is 12.5

db.product.find({price:12.5})

Filter the product details where price is in ascending order

db.product.find().sort({price:1})

In sort 1 is Asc and -1 is Desc

How to view only one record from the Table ?

db.product.findOne()

How to display only name details from the document ?

db.product.find({},{name:1})

How to turn off displaying the ID from the collection during selection

db.product.find({},{name:1,_id:0})

How to display only name ,brand from the document

db.product.find({},{name:1,brand})

How to display only name , brand document details whose price is >=200 from product collection

db.product.find({price:{$gte:200}},{name:1,price:1})

MongoDB : How to import JSON Records to MongoDB in windows environment

We can import JSON , CSV and TSV files into MongoDB  by using mongoimport utility.

Importing JSON records to MongoDB with mongoimport.exe utility

Step  1:

 MongoDB provided mongoimport.exe utility to import JSON documents from outside environment to MongoDB

Step 2: Make sure MongoD server is running before you start importing JSON documents

Step 3 : Connect to command prompt and execute as mentioned below

Syntax :

Mongodimport.exe path -d <database name> -c <Collection Name > space JSON document path location

Ex : I have installed mongodb in  C:\Program Files\MongoDB\Server\3.2\bin and my JSON files are located in C:\Users\snreddy\Desktop\ImportJSONFiles

Referenced screen shot is mentioned below

Now connect to mongo shell and verify the above kingsrestaurent is created or not ?

Points to Remember :

We cannot import JSON or BSON documents in Mongo Shell .

MongoDB : How to create collection in MongoDB

We can use db.collection.insert(document) method to insert or create new collection in the MongoDB.

Ex:

db.product.insert({item:"card",qty:5,company:"Mongo"})

SQL Server Security best practices.

The below are the best practices for security in order to avoid SQL injections etc .

*Minimize the number of sysadmins allowed to access SQL Server.

*Only install required network libraries and network protocols on your SQL Server instances.

*Ensure the physical security of each SQL Server, preventing any unauthorized users from physically access your servers.

* As a DBA, log on with sysadmin privileges only when needed.

*Use a Windows Authentication account to SQL Server as a sysadmin.

* Create separate accounts for DBAs to access SQL Server when sysadmin privileges are not needed.

*Assign the SA account a very obscure password, and never use it to log onto SQL Server.

*Don't use the SA account, or login IDs who are members of the sysadmin group, as accounts used to access SQL Server from applications.

*Give users the least amount of permissions they need to perform their job.

*Use stored procedures or views to allow users to access data instead of letting them directly access tables.

*Use strong passwords for all SQL Server login accounts.

*Don’t grant permissions to the public database role.

*Remove user login IDs who no longer need access to SQL Server.

*Disable the guest user account from each user database by using REVOKE CONNECT FROM GUEST.

*Don’t use cross database ownership chaining if not required.

*Never grant permission to the xp_cmdshell to non-sysadmins until proper requirements.

*Remove sample databases from all production SQL Server instances.

*Use Windows Global Groups, or SQL Server Roles to manage groups of users that need similar permissions.

*Avoid creating network shares on any SQL Server.

*Configure login auditing so you can see who has succeeded, and failed, to login.

*Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.

*In SQL Server 2005 and earlier, remove the BUILTIN/Administrators group to prevent local server administrators from being able to access SQL Server. In SQL Server 2008, the BILTIN/Administrators group does not exist by default.

*Only give SQL Server service accounts the minimum rights and permissions needed to run the service. In most cases, local administrator rights are not required, and domain administrator rights are never needed.

*Run each separate SQL Server service under a different Windows domain account.

*When using distributed queries, use linked servers instead of remote servers. Remote servers only exist for backward compatibility.

*Do not browse the web from a production SQL Server instance.

*Instead of installing virus/antispyware protection on a SQL Server, perform scans from a remote server during a part of the day when user activity is less.

*Add operating system and SQL Server service packs and hot fixes soon after they are released and tested, as they often include security enhancements.

*Encrypt all SQL Server backups with a third-party backup tool, such as Red Gate SQL Backup Pro.

*Only enable C2 auditing or Common Criteria compliance if required, as they add significant performance overhead.

*Consider running a SQL Server security scanner against your SQL servers to identify security holes.
 

*Consider enabling SSL or IPSEC for connections between SQL Server and its clients.

*If using SQL Server 2005/2008, enable password policy checking.

*If using SQL Server 2008 in a high security environment, consider implementing Transparent Data Encryption to protect confidential data.

*If using SQL Server 2005, don't use the SQL Server Surface Area Configuration tool to unlock features you don't absolutely need.

*If using SQL Server 2005/2008 and you create endpoints, only grant CONNECT permissions to the logins that need access to them. Explicitly deny CONNECT permissions to endpoints that are not needed by users.

Database Lelel Roles in SQL Server

Database Roles :

Role is used to group a set of privileges .

We can reduce the process of granting and taking back permissions to large no of users with help of roles.

SS supports 3 types of roles .

                 1. Fixed Database Roles .

                 2. Custom Database Roles.

                 3. Application Database Roles.

1. Fixed Database Roles :

There are 8 fixed database roles which comes along with SS database .

SS supports the following fixed db roles .

1. db_owner :

  Member of db_owner role can perform any task on the db.

2. db_ddladmin :

  Member of ddl_admin can work with DDL commands such as create , alter and drop .

3. db_securityadmin:

  Member of db_securityadmin can create users ,roles , schemas , certificates etc .

4. db_datareader :

  Member of db_datareader can read data from any object.

5. db_datawriter :

  Member of db_datawriter can work with insert , update and delete commands.

6. db_denydatareader :

  Member of db_denydatareador cannot work with any select command.

  No other users of db can grant the permission .

7.  db_accessadmin :

  Member of db_accessadmin can work with disk files.

8.  db_backupoperator :

  Member of db_backupoperator role can take backup of db and can restore db.


2. Custom Database Roles :

We can create role with required privileges .

steps to create custom database roles .

1. Creating Role

 Syn : Create Role <Role_Name>

2. Granting Permissions to the Role

Syn : Grant .... to  <Role Name>

3. Adding user to the role using sp_addrolemember


Requirement:

Create a role with the name customercare _role  Grant select , insert on emp table and also Grant Select on Library Schema and also Grant backup database permission .

Now add the user John to the above role .

Steps to achieve the above requirement

1. Creating the Role

        user test
           Go
create role CusomerCare_Role

2. Granting privileges to the role

         Use Test
            Go
Grant Select , Insert on Emp to CustomerCare_Role
Go
Grant Select on ::[Library] to CustomerCare_Role
Go
Grant Backup database to CustomerCare_Role
Go

3. Adding John to the Role

        Use Test
             Go
sp_addrolemember @rolename='CustomerCare_Role' , @member name='John'

My backup was failed what may be the possible scenarios.

In general backup will fail due to the below possible reasons

                                           * No Disk Space (Error No : 3271 ) ,

                                           * MSDB was offline.

                                           * SQL Agent Services was stropped.

                                           * Server was busy.

                                           * Net Work Problems.

How to give SQL Server Agent permissions to the users

Scenario:

  Whenever user requested to give SQL Server Agent permission then what you will do ?

Solution :

  3 roles are there in MSDB.

1. SQLAgentUserRole

       *Ability to manage the jobs that they own .

2. SQLAgentReaderRole

       *All of the SQLAgentUSerRole rights

       *The ability to review multiserver jobs i.e jobs configurations and its history

3.SQLAgentOperatorRole

       *All of the SQLAgentReaderRole rights.'
   
       *The ability to review operators , proxies and alerts.

       *Execute Start or Stop all local jobs

       *Delete the job history for any local job

       *Enable or Disable all local jobs and schedules

Ex:

use msdb
   go
create user username for login loginname

use msdb
    go
sp_addrolemeber @rolename='SQLAgentUserROle',@membername='UserName'

Server - Level Roles in SQL Server

SQL Server supports 8 types of fixed server level roles which are used to divide the functionality or work among the administrators .

Once the login is created it can't perform any task by default .

Role is nothing but collection of privileges.

1. System Admin

  Member of System Admin role can perform any activity on the server .

2. Server Admin 

  Member of Server Admin role can set server level settings using sp_configure procedure.

3. Setup Admin

  Member of Setup Admin role can install service packs , hot fixes and can execute scripts submitted by the development members.

4. Security Admin

  Member of Security Admin role can create logins , roles etc.

5. DB Creator

  Member of DB Creator role can create and manage databases and also can restore databases.

6. Disk Admin

  Member of Disk Admin can work with Data and T.Log files .

7. Bulk Admin

  Member of Bulk Admin can perform bulk operations like bcp , import and export etc.

8. Process Admin

  Member of Process Admin can work with processes i.e can kill the process .

Note: You can easily remember this roles with short cut called 4S2DBP.

How can I recover my 'sa' password

Scenario :

      I have install SQL Server 2005 or Later versions automatically two logins are created 'sa' and 'BUILTIN\Administrators'. By following security best practices I have removed BUILTIN\Administrators login and I have forgot my 'sa' password. How can I handle this scenario.

Solution:

      No need to reinstall SQL Server

      We can reset 'sa' password by running the server in single user mode.

Steps :

1. Ask windows team to add windows account into administrator role.

2. Stop SQL Server Instance and start it in singe user mode for ex if instance name is test

     Go to command prompt

                       net stop MSSQL$Test

                       net start MSSQL$Test /m

3.Now open SQL Server Management Studio .

4.Connect using windows authentication mode , connection will be successed.

5.Reset 'sa' password or create new login if you want.

6.Restart server in multi-user mode.

7.Now connect using 'sa' and with new password.

Difference between SSIS 2005 and 2008

The below are the key differences between SSIS 2005 and 2008 .

1. Script Task :

In SSIS 2005 users can write the script in VB only.

In SSIS 2008 users can write the script in C# and VB.

2. DataProfilingTask :

In SSIS 2005 Data Profiling Task is not available.

In SSIS 2008 Data Profiling Task is introduced.

3. Cache Transformation :

In SSIS 2005 Cache Transformation is not available.

In SSIS 2008 Cache Transformation is introduced.

4. Look Up Transformation :

In SSIS 2005 Error Output lookup has only

               Fail Component , Ignore Failure & Redirect Row .

In SSIS 2008 "No Match Output " is introduced along with the existing features.

5. Cache Mode :

In SSIS 2005 Cache Mode is not available .

In SSIS 2008 Cache Mode is introduced with 3 types of cache

                Full Cache Mode , Partial Cache Mode & No Cache Mode .

6. OLEDB Provider for Analysis Services :

In SSIS 2005 OLEDB Provider for Analysis Services is not available .

In SSIS 2008 OLEDB Provider for Analysis Services is introduced.

7. Excel Enhancements :

In SSIS 2005 you have to build a new connection based on the office 12.0 OLEDB Client .

In SSIS 2008 Importing excel work sheets is native to SQL 2008.

8. Data Flow Task :

In SSIS 2008 enhanced data flow engine tuned for parallelism.

What’s new in SSIS 2012 for developers

The below are the new enhancements for developers in SSIS 2012

* Undo and Redo Changes is SSIS PACKAGE.

* SSIS tool box enhancements.

* SSIS design surface enhancements.

* SSIS solution explorer enhancements.

*  Group tasks and Transformations.

*  Decimal Data type variable.

* Expression Character Limit.

*  SSIS toolbox and Variable buttons are added to package design surface.

* Zoom IN and Zoom Out.

*  Parameters are added  in design panel where control flow and dataflow , parameters ,event                  handlers ,Package explorer.

     *   PIVOT transformation , Row Count GUI Transformation  GUI Improvements.

     *  ODBC Source and ODBC Destination.

     * DQS - Data Quality Service is introduced used to enable more easily and accurately improve              the quality of data.