Sql linked server to query on remote server

Its provides the connection to external data objects or data source, using linked server you can extract the data from the different data source for a example oracle, ms access and other databases. you can execute DML operation to other sql server provided you should have permissions to do the same. Providers acts as bridge ...

SQL Server service account in SQL Server R2

Two ways to find out the sql server service accounts details in SQL server 2008 R2 either using SQL Server Configuration manager or using DMVs (Dynamic management Views) 1.   Connect to SQL Server Configuration manager 2.   Check out the service accounts, Here you will find all the sql server services and there logon credential. 3.   Right click on the ...

Add disk to sql server cluster

As sql dba, you have to take care of cluster resources and perform task like adding new disk in the cluster environment, putting resources offline and bring back online after maintenance. Our production server is running on failover cluster sql server 2005. As the instance was running out of space, we got a request to ...

SQL Server 2008 Database Backup compression Example

In the previous version of sql server, whenever you take backups that consume lots of space on disk based on the database size. Every time you have to clear backup files to create space for new backups. This issue has been resolved by introducing backup compression option in sql server 2008. SQL Server 2008 comes up ...

Stored procedure in sql server

Stored procedure is very good example of sql scripting. Early in my career, i worked as a sql developer in that phase learnt a lot like how to write store procedures and triggers? How to implement business functionality using stored procedure without changing front-end code of the application? Writing scripts in sql server is less costly compare ...

BCPError – Unable to open BCP host data-file

Troubleshooting bcp error : SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file As we already know, how to use bcp to export data from sql server. BCP is largely known command for importing data in sql server as well as exporting data from sql server. Here you ...

Indexed view in sql server

Indexed views are also called as materialized view that means result of data manipulated and stored in the view. We can create unique clustered index on view to improve performance of the queries based on it. Indexed views can use to help those queries that generates large result set. It is not advisable to create index on such ...

Test cases for log shipping

Test cases for log shipping 1. We disconnected the log shipping and tried to recover the database in the minimum down time. it took 10 to 15 minute to recover depends on pending log file size.   2. We shifted the primary database on secondary server and vice versa also tested the log shipping functionality ...

SQL Server – MSDB database – Stores backup and sql job information

MSDB is part of system databases which is necessary for sql server to perform sql server jobs related tasks. MSDB database is used to stores sql jobs and database backup related data in sql server. Sql server agent makes use of MSDB database for scheduling sql jobs and alerts. Whenever we install sql server, MSDB ...

Resource database in sql server

Resource database is read only database includes all Sql server system objects. Resource database deals with sql server system Meta data whereas master database contains user metadata. It is use for compatibility changes that why it is not accessible to user. Resource database makes sql server version upgrade much faster where as in old version ...