SQL Server 2008 Service fails to start after Service Pack Installation

Some times you may find that the SQL Server Service is not starting after applying a service pack and when you check in the Event Viewer you find the below message.

Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

SQL Server Setup creates a database with the data file name temp_MS_AgentSigningCertificate_Database.mdf  during the installation process and if the SQL Server setup is not able to create that database in the default data path then the above error is returned as it is not able to find the path.
To fix this issue.

1.

Go to Registry editor,  ->To open this, ->go to “Run” and type -> ”regedit” and click “ok”

2.

First go to this path and make sure that the path in the key SQLDataRoot exists. If not then give a valid path to this key.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<instance name>\Setup3

Then go to this path and make sure that the path in the keys “BackupDirectory”, “DefaultData” and “DefaultLog” exists. If not then give a valid path to these keys.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<instance name>\MSSQLServer

4.

Now you should be able to start the SQL Server Service without any issues.
Once the Service is started, verify the SQL Server, databases and others to make sure everything is fine.

Also verify if the Service pack or the CU is installed correctly.

SQL Server Migration Checklist

Are you migrating your SQL Servers? Here is a checklist for your SQL Server Migration, easy way to achive your moving goal.

1.Build your New Server, Install SQL Server and required updates and keep the server ready for migration

2.Stop Application Service(s).This is to ensure that the no applications are connected to the Databases during the migration process.

3.Change the Properties of the databases that are part of Migration to “Read-only”. This is to ensure that the data modification is not happening by any other sources.

4. Take a FULL backup of all the User databases that are involved in the Migration Process.

5.Move the backups to the destination server or a Shared location, then restore them to the appropriate drives on the destination.

6.Change the compatibility level of the databases (Optional) Do this if the applications connecting to these databases are independent of the database compatibility level.

7.Transfer logins using SSIS (Transfer Logins Task) or using “sp_help_revlogin” More information about sp_help_revlogin is at http://support.microsoft.com/kb/246133

8.Check for Orphaned Users in the databases and Fix them (if Any)

9.Update Usage on the migrated Databases

10.Update Stats on the migrated Databases

11.Re-Index or Re-Organize Indexes on the migrated Databases

12.Transfer Jobs using SSIS or manually create them

13.Build Maintenance plans (if Any)

14.Recompile database objects if required

15.Move or rebuild SSIS or DTS packages (if Any)

16.Create Alerts and Operators (if Any)

17.Setup High Availability Options (if Any Like Replication, LogShipping, Mirroring)

18.Test the High Availability options that were setup in the previous step

19.Point the Application(s) to new Server and start the Application Service(s)

20.Test the Application(s)

Let me know if you any other question or suggestion.

Mehboob – MCTS

The following readiness videos provide candidates who want to pursue SQL Server 2008 Microsoft Certified Master certification with an overview of what you need to know to prepare for the SQL Server 2008 Microsoft Certified Master Knowledge Exam. All of this free of cost, no fee or regestration require in order to watch, simple just click and enjoy…

Microsoft Certified Masters Video från Brent Ozar.

• Database Structures

• Database Structures – Demo

• New Database Structures in SQL Server 2008

• New Database Structures in SQL Server 2008 – Demo

• Data File Internals and Maintenance

• Data File Internals and Maintenance – Demo

• Log File Internals and Maintenance

• Log File Internals and Maintenance – Demo

• Index Internals

• Index Internals – Demo

• The Clustered Index Debate

• Locking

• Locking – Demo

• Statistics

• Snapshot Isolation

• Partitioning

• Indexing Strategies

• Indexing Strategies – Demo

• Index Analysis

• Sparse Columns

• Sparse Columns – Demo

• Index Fragmentation

• Index Fragmentation – Demo

Immersion Event on Performance Tuning

• Storage Core Concepts

• Storage Guidelines

• DAS vs SAN Architecture

• Storage Testing and Monitoring

• Baselining and Benchmarking

• SQLOS

• Waits and Queues

• Waits and Queues – Demo

• Extended Events

• Extended Events – Demo

• Resource Governor

• Resource Governor – Demo

• Policy-Based Management

• Policy-Based Management – Demo

• Multi-Server Management

• Multi-Server Management – Demo

Immersion Event on HA+DR

• Designing an Availability Strategy

• Backup Internals

• Backup Internals – Demo

• Restore Internals

• Restore Internals – Demo

• Partial Database Availability and Online Piecemeal Restore

• Virtualization

• Consolidation

• Replication

• Replication – Demo

• Database Mirroring

• Database Mirroring – Demo

• Clustering Introduction

• Clustering Setup and Troubleshooting

• Database Snapshots

• Database Snapshots – Demo

• Consistency Checking (Part 1)

• Consistency Checking (Part 2)

Immersion Event on Security and Development Support

• Optimizing Procedural Code

• Service Broker

• SQL Azure

• SQL Server Clients

• Change Data Capture, Change Tracking and Integrated Fulltext Search

• Security: Introduction and Server Setup

• Security: Authentication and Authorization

• Security: Keys and Encryption

• Security: Auditing and Compliance

• Security: EXECUTE AS and SQL Injection

• XML

• SQLCLR

• SQL Server Spatial

Khan – MCTS

Error 1418, fails to connect sql 2008 mirror server:

• The server network address can not be reached
• Alter failed
• Error 1418
• Insufficient Log Data
• Error 1478

Start with: In this example we are inside a DOMAIN. We have a domain user called khan_machine

1)      Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

2)      khan_machine is local admin in both machines. Also is a sysadmin user in both sqlservers.

3)      khan_machine  is the MSSQLSERVER service account in both machines

4)      Machine A is gonna be the PRINCIPAL, B the MIRROR.

5)      Log on in my machine as khan_machine

6)      Open Sql server Studio. Connect to both machines using windows authenticacion.

7)      Create a New Database in A (you may need to have sysadmin role to khan_machine using sa) create a table, add some data

8)       Backup the new DB (Full backup) with .bak extension

9)       Backup the new DB (Transaction log) with .trn extension

10)    Copy the both files to a location in B machine.

11)     Restore .bak into a new DB using NORECOVERY option

12)     Restore .trn into the previous DB using NORECOVERY option

13)    Go to Machine A, open a new query

14)    Take a look to the endpoints

Run this syntax in QA:

SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

15)   Delete existing ones (DROP ENDPOINT [NAME])
16)   Create a new endopoint

Run this syntax in QA:
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

17)   Start it

ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO

18)   Do steps 13 to 17 in machine B.

19)   Return to machine A. Query.

20)   Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

ALTER DATABASE NAME
SET PARTNER =’TCP://MACHINENAME:5023′

21)   Repeat step 20 from MACHINE B pointing to MACHINE A

22)   Note: Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

23)   Lets test them: Add some new data in Machine A database.

24)   Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

25)   Refresh the 2 databases in the UI you should see the new status and roles changed.

26)   Open Machine B databse, the new data should be there.

To establish any mirroring session (with or without certificates) you must use FQDN (Fully Qualified Domain Names) machine name.domain.com.  ** Before going on, be sure that you can reach the 3 machines with a single PING. Also, is a good practice to ensure that you can reach a telnet call over the FQDN + the port you plan to use (5022 in this example)** 

FIX: Over the post above. When establishing a mirroring session using machine accounts (not certificates, so you are inside a DOMAIN)  ensure your 3 mssqlservices are running under NETWORK SERVICE account

För more information have a look here

Khan – MCTS

Microsoft SQL Server 2008 Service Pack 2 download here 

Microsoft SQL Server 2008 Service Pack 2 (SP2) is now available for download.

Top Tips for Effective Database Maintenance

Paul S. Randal

Several times a week I’m asked for advice on how to effectively maintain a production database. Sometimes the questions come from DBAs who are implementing new solutions and want help.  It’s worth to go through here is link: http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

Some other topic which you might want to know:

Twit List
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69568

It’s dead, Jim
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782

Not Even Stupid
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79305

 Khan – MCTS

Log Shipping is one of the solutions for High Availability for business critical application. Log Shipping is first introduced in SQL Server 7.0.  Log Shipping; as name defines does the shipping of Transaction Log to another server; it does exact replica of the database; it does backup of Transaction Log and restores it on a Secondary /Stand by Server which keeps database on secondary server in sync with the database on Primary Server. In case of failure work loss happens only for the time being copy of T-Log backup and restoring back to
Secondary/Stand by Server. Stand by / Secondary server which can act as a primary server in case of failure.

SQL Server DBA has to perform some manual tasks to bring it online for business. There are other options like Clustering and Replication for High Availability of Business Critical Application, so why do we go for Log Shipping? that good question so here we go .. 

Well, there are reasons for choosing Log Shipping against Cluster and Replications: Its easy to maintian and cost$ is less then other technolog, such as cluster, some details why we go for LS.

1. Setting up: It is very easy to create a Log Shipping, only thing you need to identify is you must have two servers with SQL Server Enterprise Edition and a database(s) which is critical to your business environment.
2. Manageability: It is easy to manage Log Shipping comparing to Replication and Cluster Server, generally Log Shipping works well and you don’t feel panic with its monitoring or maintenance work.
3. Act as a Reporting Server: Stand by or Secondary Server can act as a Reporting Server in your organization, your database which is being log shipped can be used for Read Only queries or I would rather say it can act as a Reporting Server. The thing need to be consider here is it will be unavailable at the time of Restoration of Transaction Log e.g. if your Transaction Log backup takes 10 minutes of Restoration Time and you does Log Shipping every 60 minutes then your Reporting/Secondary/Stand by Server will be un-available for 10 minutes.
4. Can use multiple database(s)/server(s): You can use multiple server(s) as a Stand by or Secondary Server for different purpose, e.g. you can create two Stand by server(s) one for Reporting and another for High Availability
5. Cheaper then cluster: If you go for a Cluster Server you will have to meet software and Hardware requirement; I mean you need identical hardware to setup clustering where as in case of Log Shipping you don’t need to have exactly the same set of hardware for Primary and Secondary or Stand by server.
6. Best solution for physical dispersed location: Data Center: In your environment you are having a cluster server configured at your Data Center for high availability so that if in case of any failure or damaged you can recover your data back, imagine what if your data center itself got damaged!!!!!! In this type of scenario Log Shipping does good job you can configure Log Shipping to Dr Server which is at different physically location and if your local Data Center / Server got damaged you may still recover your data. You can do this thing using Replication too; in case of replication again you have to select Snapshot/Transactional/Snapshot Replication, now as you may use Log Shipping you would rather go for Snapshot Replication, you have to monitoring it regularly whether it is running or not!!! Where as with Log Shipping it generally runs smoothly and even if it caught an error it will tell you exact where it breaks so fixing a Log Shipping is not difficult.

Prerequisite
1. Database must be in Full or Bulk-Logged Recovery Model.It is required that Database has to be in Full or Bulk-Logged Recovery Model,One can perform T-Log backup only when Database Recovery Model is set to Full or Bulk-Logged, here is a brief on recovery model.

3. Create a share on Primary Server and Secondary Server with change and write permissions for Windows Account User which is used for SQL Server Agent.

4. You may Restore Full Database on Secondary Server manually or it can be done via Log Shipping installation itself at the time of setup process (Ensure this database on Secondary Server must be restored with NORECOVERY or STANDBY option).It is required to Restore Database with NORECOVERT STANDBY because if the database restored in recovered/write enabled mode you can not apply another T-Log.

Optional Component : Monitoring / Witness Server
Identify your Monitor Server which will look after for your Primary Server and SECONDARY Server’s Activity.It is better to use separate server which monitors the activity of Primary and Secondary Server so that you can get the alert even if server itself won’t works.

Primary Server:
1. Create a Share with read/write permission for domain user which we’ll be using to startup SQLServerAgent
2. Create a Full Database backup for LogShDB
3. Backed up T-Log on the folder you have created earlier to store the log backup You can create a maintenance plan to take a T-Log backup for regular interval and then add it as a Step in job to automate this process
4. Please ensure that the duration must enough to copy the log backup file to Secondary Server.Suppose we are taking T-Log backup each 1/2 hour and if the backup file is big enough which takes more then 1/2 to copy over the network share, it will again creates a new T-Log backup which will then occupies recourse unnecessarily.
5. Copy T-Log backup file to Secondary Server’s shared folder
6. Delete the T-Log backup file after it copied to the Secondary Server’s shared folder.
7. Ensure you have selected Allow Database to assume primary role option so that if in future requirement of role reversal can be possible easily.If in case we need to change the role of primary server or secondary server to act as Primary or Secondary this option should be used while setting up Log Shipping.
8. Ensure that you have set up proper threshold values for Destination Database for Out of Sync, Load Time Delay, File Retention Period and History Retention Period option.These options are self explanatory: Out of Sync=used by witness server to send alert if the specified time has been exceeded between Last T-Log backup on primary and secondary server, Load Time Delay= Destination Database waits before it restores the T-Log, File Retention Period= Time elapse till file gets deleted and History Retention Period= Time till we retain History details
Secondary Server
1. Check if the Database is already exists on Secondary Server, if it is already their drop it.
2. Restore a Full Database Backup for LogShipping database. Restore a T-Log for LogShipping database. on the secondary server which is copied on the shared folder of Secondary Server;
4. Ensure you have given STANDBY or NORECOVERY option while restoring T-Log backup for LogShipping database..

You may find log shipping is a very use full yet simple to configure, use and manage, it is cheaper and easily manageable solution for High Availability of your data(Most of the company want cheep and good solution for there invoriment). It is a good solution over clustering (because it is costly; requires identical hardware to setup cluster) and easily manageable, easy troubleshooting against replication as it clearly says where it breaks.

Generally Log Shipping works very smooth once it is configured. Even if you have to re-configure it from the scratch it is easy job to do, which i love that.

Here is list of Shortcut keys to work with SSMS handy.

In the SQL Server Community:

Aaron Bertrand’s Blog
Brad McGehee’s Blog
Chad Boyd’s Blog
Greg Low’s Blog
Jonathan Kehayias’s Blog
Kevin Kline’s Blog
Kimberly Trip’s Blog
Paul Randal’s Blog
Pinal Dave’s Blog
Satya SK Jayanty’s Blog
Simple Talk
SQL Server Magazine
TechNet Magazine
Tibor Karaszi’s Blog &  Ola Hallengren

SQL Server Central and  CodePlex

SQL Server: Delete Backup History to reduce MSDB database size

SQL Server keeps a track of the backup history of your server, in the msdb database.

Every time a backup or restore operation occurs on your database, additional rows are added to the backup and restore history tables. If you do not keep a check on this, you will find your msdb database growing over time.

SQL Server provides the sp_delete_backuphistory database engine stored procedure which makes it very simple to delete history that is older than the specified date.

Here’s how to use this stored procedure to delete backup history that is older than April 18, 2011, 5:30 p.M. in the backup and restore history tables.

Similarly if you want to automatically delete records that is say 1 or 2 months old,

create a stored procedure that calculates the date and executes the sp_delete_backuphistory procedure

Here’s the same query to try out:

CREATE PROCEDURE [dbo].[DeleteBackupHistory]
AS 
DECLARE @BckDate DATETIME
SET @BckDate = CONVERT(varchar(10), DATEADD(dd, -60, GETDATE()), 101)
EXEC sp_delete_backuphistory @BckDate

Now whenever you want to delete records 1 or 2 or 3 months prior to the current date, just call the DeleteBackupHistory stored procedure.

Similarly, you may also want to look at the sp_purge_jobhistory and sp_maintplan_delete_log

to remove other history information and keep your msdb database from growing over time.

Khan – MCTS

Has everyone heard the news? Microsoft has finally released for free the iSCSI target that was previously only available through OEMs as part of Windows Storage Server.

This will sure make setting up Test/Dev/Experimental clusters easier! VM to setup with FreeNAS.  And it is even supported for production use on Windows Server 2008 R2.

Visit here: http://blogs.technet.com/b/virtualization/archive/2011/04/04/free-microsoft-iscsi-target.aspx

Learn, how to setup iSCSI target, here we go!!! Jose Barreto’s Blog help you.

Khan SQL DBA – MCTS

www.addarr.com

Free Video tutorial

MS SQL Server 2008:-

TechNet Video: How Do I: Add a Node to an Existing Failover Cluster in SQL Server 2008?

24 Hours of SQL Server 2008

TechNet Video: Microsoft Certification Coaching Session: Preparing for your Microsoft SQL Server 2008, Implementation and Maintenance Exam (70-432)

Fill Factor explain,

Fillfactor will specify how full sql server should make each index page when it creates a new index using existing data, it specify how full each page in the leaf level of an index should be. Index value can be from 0 to 100.

1.  Fill factor 100 implies the leaf Pages, data pages if clustered index, else

FID(FileID)
+PN(Page Number)
+RN (Row Number))
will be 100 percent full.

2.  Fill factor 0 implies, the leaf pages will be almost full, but SQL Server leaves some space within the upper level of the index tree. In both the cases even though it fills the leaf pages(data pages), the root and intermediate pages will still have room for two additional rows (1 root + 1 intermediate).
3.  Fill factor from 1 to 100, will be the percentage of each leaf page to fill with rows.

The fillfactor is used only when you create the index,it is not maintained over time.This value is not maintained after index creation, you index leaf pages may become full and experience many page splitting even you specify a very low value at the beginning.

Generally, when page splitting occurs, half of the data rows will be moved to the newly allocated data page, and half of the rows are remained on the original page. the Fill Factor is not maintained during the spliting. For example, if the Fill Factor is 40% full, in this case, the value is not maintained. One exception may be, when inserting a row containing variable-length columns, if this row is very large, more data rows will be kept on the other page to vacate space for this big-size row.

Select an appropriate fill factor for each index. If the data has a minimal amount of changes to the middle of the table, configure the indexes to have a high fill factor, i.e., closer to 100%, which will save on the storage needed. If the data has many changes to the middle of the table, select a lower fill factor, i.e., 65% to 85%, so that as data is added to a page, page splitting is minimized until the indexes are rebuilt.

Information: About fill factor.

Fill factor 100%              =  If DB is read only
Fill factor b/w 50% and 70%   =  If DB is write intensive (writes greatly exceed reads)
Fill factor b/w 80% to 90%    =  If DB is both read and write intensive

Self Test :-

1. Which SQL Server 2008 edition supports IA64?

A. Enterprise and Developer editions

2. How many instances can be created in SQL Server 2008 Standard Edition?

A. 16

3. How many SQL Server default instances can be installed on a single?
No clustered server?

A. 1

4. Which of the following editions has all the features and functionality but is not
licensed for production?

A. Developer Edition

5. Which SQL Server 2008 edition is best suited for mobile applications?

A. Compact Edition.

6. Which SQL Server 2008 edition provides the most Analysis Services?
features?

A. Enterprise Edition

7. Which tool should be on an existing SQL Server 2000 or 2005 database
Instance prior to upgrading to SQL Server 2008?

A. Upgrade Advisor Tool

8. Which of the following is a command line utility used to run PowerShell
command and scripts?

A. sqlps

9. Which of the following editions does not support any Reporting Services
features?

A. Express Edition

10. Which of the following accounts has the least privilege when used as a service
account?

A. Local user account

11. Which of the following accounts should be used to perform a SQL Server
2008 installation or upgrade?

A. Administrator account

12. In which of the following directories are the default instance database data
and log files located?

A. <drive>:\Program Files\Microsoft SQL Server\MSSQL10.
MSSQLSERVER\Data

13. What additional software is required for SQL Server’s Business Intelligence
Developer’s Tool?

A. Microsoft Internet Explorer 6 SP1 or later

14. Which of the following is the SQL Server component that provides the services
for creating, processing, and managing OLAP and data mining?

A. Analysis Services

15. Which of the following is a scalability feature in SQL Server Enterprise
Edition?

A. Partitioning

16. In which directory are the installation log files found?

A. <drive>:\Program Files\Microsoft SQL Server\\100\Setup Bootstrap\LOG\Files\

17. Which system database is hidden to SQL Server Management Studio and is
used to persist a read-only version of system objects?

A. Resource

18. Which is the compatibility version associated with SQL Server 2008?

A. 100

19. Which SQL Server tool is used to check that SQL Server component services
are running?

A. SQL Server Configuration Manager

20. Which type of file contains data and database objects such as tables and
indexes?

A. Data files

1. You are setting up security for your new SQL Server 2008 installation.
Management is concerned about security. What approach should you take to
ensure security settings are optimal?

A. Use SQL Server Configuration Manager to secure the installation

2. You have been tasked with setting up standards for your SQL Server 2008
installation. You need to enforce a table naming convention. What is the best
way to accomplish this in your SQL Server 2008 environment?

A. Create a Declarative Management Framework policy

3. You have been asked to create a backup of your production database and
restore it on a development server. Your production server is using the full
recovery model. Full backups are taken Monday and Wednesday. Transaction
log backups are taken every hour. Today is Friday. The backup needs to be
created as quickly as possible. What’s the fastest way to get the latest database
copy while minimizing impact to production?

A. Create a Copy Only Backup. Use that to restore to development.

4. You have a SQL Server 7.0 database and would like to move it to a new
SQL Server 2008 instance. The database is part of an FDA-validated system
and cannot be changed at all? How can this be accomplished?

A. You must upgrade the database to SQL 2005 or greater.

5. You have an application that is being upgraded from SQL Server 2005 to
SQL Server 2008. You notice that some stored procedures are not working
correctly. An excerpt is as follows:
SELECT *
FROM Territories, Region
WHERE territories.regionid *= region.regionid
What should you do to resolve the issue?

A. The join syntax is incorrect. Replace with left join.

6. Your disk is almost full on the transaction log drive for a database server.
How can you resolve this issue?

A. Change the mode to simple and shrink the log

7. You want to enforce a standard naming convention for stored procedures.
What’s the best way to do this in SQL Server 2008?

A. Use the SQL Server 2008 Declarative Management Framework

8. You want to enforce a standard naming convention for tables and stored
procedures. Your company has two SQL 2008 Servers and 60 SQL 2005
Servers. You need to use the same solution on all servers. What’s the best way
to do this in SQL Server 2005 and SQL Server 2008?

A. Create a DDL trigger for all servers

9. You have a database table with a varchar(600) field in it. Most of the records
in the table have a null value for this field. How can you save space?

A. Move the data into a second table

10. You have a database table with a FileStream field in it. Most of the records
in the table have a null value for this field. What’s the best way to save
space?

A. Move the data into a second table
B. Use sparse columns
C. Use the SQL Server 2008 Declarative Management Framework
D. None of the above

11. You need to store images in for a Web site using SQL Server 2008. How can
you accomplish this?

A. Use a FileStream data type, and the images will be stored on disk

12. You are responsible for a system that is used for both online transaction
processing (OLTP) and reporting. When reports run on the server, the OLTP
process slows way down. How can you allow reports to be run on the server
and minimize impact to the OLTP processes?

A. Use the Resource Governor

13. You are creating an application to track crime in different locations
throughout a large city. What data type could prove useful for storing location
data (longitude and latitude)?

A. Varchar

14. You are running out of space on the drive used to store backups. All of the
servers use the same network location. What can you do to save space with
your backups while maintaining the same number of backups?

A. Use compressed backups

15. You need to store sensitive data in your SQL Server database. The application
has already been written and works fine. What’s the easiest way to do this
without having to change your application?

A. Use transparent data encryption

16. Within your application, you need to log all changes to one table. DDL
and DML changes must be logged. What’s the best approach to solve this
problem?

A. Use the built-in auditing capability

17. You have a server that supports Hot Add CPU. The current CPU utilization
is 95 to 100 percent most of the time. The server is mission-critical and cannot
be shut down. SQL Server 2008 Standard Edition is installed. What should
you do?

A. Schedule an outage and add another CPU to the server

18. You are contemplating using data compression on a table. You would like to
know how much space this will save. How can you determine the savings?

A. Use sp_estimate_data_compression_savings

19. You have a server that supports Hot Add Memory. Performance is sluggish,
and you believe adding more memory will help. The server is mission-critical
and cannot be shut down. SQL Server 2008 Standard Edition is installed.
What should you do?

A. Schedule an outage and add memory to the server.

20. You have a SQL Server 2008 installation, and you want to create a high availability
Solution. What are the ideal approach(es) to solve this problem?
A. Mirroring
A. Clustering

Frequently Asked Questions

Q: Do I have to install a default instance?
A: No. You can install named instances on a server without having a default instance.

Q: If a named instance uses dynamic port allocation how will I know which port
to connect to each time?
A: The SQL Browser service needs to be running on the SQL Server. It will
automatically
return the correct port to your connection without you having to
specify anything.

Q: Why shouldn’t I change a SQL Server service account using the services applet?

A: Changing the service account using SQL Server Configuration Manager will
assign all the file system permissions and advanced user rights needed for
SQL Server to work properly. Using the services applet won’t do this and could
cause problems when the service is next started.

Q: Should I change SQL Server configuration settings using the Management
Studio interface or with sp_configure?

A: The end result is the same so it doesn’t really matter. However, sp_confgure has
many more options than the interface and is easy to script so it tends to be the
popular choice with DBAs.

Q: I’ve enabled AWE on my 32-bit SQL Server but still can’t see more than 2 GB
of RAM. What should I do?

A: First, check that you’re running the Enterprise or Datacenter version of Windows.
Then check in c:\boot.ini for the /PAE switch which enables Windows to
see the extra RAM. If that all looks fine then check that the service account has
the ‘lock pages in memory’ user right. If it doesn’t there will be an error logged to
the SQL Server errorlog on startup.

Q: What is considered to be a Principal in SQL Server 2008?
A: Any entity that requests SQL Server Resources.

Q: What is the definition of “The Principle of Least Privilege”?
A: Only give permissions that are needed. Giving more access violates this
principle.

Q: Can you modify Server-level Roles?
A: No, Server-level Roles are fixed and cannot be modified.

Q: Can you create your own Database-level Roles?
A: Yes, Database-level Roles can be created to better fit a user’s security
requirements.

Q: What is the most secure Authentication Mode and why?
A: The Windows Authentication Mode is more secure because it is managed by
the Windows operating system and subject to additional Windows password
policies.

Q: What SQL Server tool is used to enable the DatabaseMail feature in
SQL Server 2008?
A: The Surface Configuration Tool.

Q: When working with SQL Server services such as SQL Server Agent, which tool
do you use?
A: SQL Server Configuration Manager.

Q: When setting up SQL Server services that need to perform tasks outside of the
local server, which account type should you use?
A: Domain Service Accounts.
There are two types of database-level roles, fixed database roles that are predefined
in the database and flexible database roles that you can create.

The fixed database-level roles are:

db_owner – Can drop the database as well as permission to perform  m all
configuration and maintenance tasks.

db_security_admin – Can modify role membership and manage permissions.
Please be careful when adding principals to this role; an unintended privilege
escalation could result.

db_accessadmin – Can add or remove database access for Windows logins,
Windows groups, and SQL Server logins.

db_backupoperator – Can back up the database.

db_ddladmin – Can run any Data Definition Language command.

db_datawriter – Can add, delete, or change data in all user tables.

db_datareader – Can read all data from all user tables.

db_denydatawriter – Will deny permission in the database to add, modify,
or delete any data in the user tables.

db_denydatareader – Will deny permission in the database to read any data
in the user tables.
These objects can be helpful when working with Database-level roles:

 sp_helpdbfixedrole – Returns a list of the fixed database roles.

 sp_dbfixedrolepermission – Displays the permissions of a fixed database role.

 sp_helprole – Returns information about the roles in the current database.

 sp_helprolemember – Returns information about the members of a role
in the current database.

 sys.database_role_members – Returns one row for each member of each
database role.

 IS_MEMBER – Indicates whether the current user is a member of the
specified Microsoft Windows group or Microsoft SQL Server database role.

 CREATE_ROLE – Creates a new database role in the current database.

 ALTER_ROLE – Changes the name of a database role.

 DROP_ROLE – Removes a role from the database.
sp_addrole – Creates a new database role in the current  Ent database.

 sp_droprole – Removes a database role from the current database.

 sp_addrolemember – Adds a database user, database role, Windows login,
or Windows group to a database role in the current database.

sp_droprolemember – Removes a security account from a SQL Server role
in the current database.

SQL Server Videos

 Here is link for different video’s : Click …

Leverage Microsoft Attunity Connector for Oracle to enhance Package Performance

How to create, modify, execute, save, and open a query with the database manager for SQL Azure?

Vi tar alla en säkerhetskopia av databasen i första taget att återhämta sig från en skada kan uppstå under katastrofen, servern kraschar, eller av någon annan anledning. För att göra det schema vi brukar ett jobb eller använda sig av databasen underhållsplan som utför säkerhetskopiering av databaser, vare sig det full / differentiella eller Logg och vi alla tar ett särskilt försiktig & övervägande under utformningen strategi för säkerhetskopiering!

Men vad som hänt om en vacker dag när du behöver din fil för backup av databaser för att fungera bra och det inte? Tänk om det är skadat! Och du har bara en komplett säkerhetskopiering för den dagen?

Idag kommer jag att diskutera om de manus som jag har skrivit i ladda ner sektion par dagar tillbaka, skriptet kommer inte bara att göra en fullständig säkerhetskopia av databasen för alla användardatabaser men det kommer också “kontrollera validering av backup file “. Det är ett måste för DB omgivning med strategi för säkerhetskopiering som – där i du inte håller flera kopior av säkerhetskopior eller bara har en dag säkerhetskopia period fil retention.

“”

declare @int int ,@dbname varchar(22),@maxdbid int
declare @bkpath varchar(25),@path varchar(50)
select @maxdbid= max(dbid) from master..sysdatabases
set @int = 0
set @bkpath=’C:\SQLDB\backup\’    — du kan ta d:\ eller z:\
USE master
while (@int < @maxdbid)
begin
set @dbname = (select [name] from master..sysdatabases where dbid=@int and status!=1073807392)
if (@dbname != ‘tempdb’)
begin
  select @int as ‘DBID’,@dbname as ‘Database’
  select @path=@bkpath+@dbname+CONVERT(VARCHAR(12),GETDATE(),112)
  BACKUP DATABASE @dbname TO DISK = @path
  print @path
  print @bkpath
  restore verifyonly from disk=@path
  end
set @int = @int + 1
continue
break
end
“”

Kontrollera raden # 17, återställa verifyonly från disk = @ väg är den linje som kommer att hjälpa oss att övervinna ovanstående fråga. Detta är inte en dold kod som ligger på ett hemligt ställe någonstans men detta är något som vi missat i vår kod – Jag vet de flesta av er redan har använder det här alternativet för din databas backup validering, hur många av er använder detta alternativ? Eller av nyfikenhet något annat alternativ!

Note: Denna kod är mycket grundläggande kod som jag har skrev inledningsvis, kan du ändra och använda skriptet enligt dina krav utan förpliktelser.

Dina förslag är välkomna!

Khan SQLDBA – MCTS

How many SQL Server Instances are there ?

In most of the scenario, We would need to identify that how many instances are there or How many instances installed in current server.

* Login into SQL Server in which server you want to…
* Copy & Paste the script given below and hit your ‘F5′ (or) Execute.

CREATE TABLE #Instances
(
InstanceName VARCHAR(100)
)

INSERT #Instances
EXEC(‘xp_cmdshell ”SQLCMD -L”’)

DECLARE @ServerName VARCHAR(100)
SELECT @ServerName = CAST(SERVERPROPERTY(‘MachineName’) AS VARCHAR)

SELECT LTRIM(InstanceName) [Instance Name] FROM #Instances WHERE LTRIM(InstanceName) LIKE @ServerName + ‘%’

DROP TABLE #Instances

* Now you got the list of Instances installed on the current server.

_____________ *** _______________

Can’t drop the index ‘Table.StatisticsName’ because it is not a statistics collection.
Normally, When we try to DELETE the statistics using the following statement, The Error occrred

DROP STATISTICS TableName.StatisticsName
GO

Msg 3739, Level 11, State 1, Line 1

Cannot DROP the index ‘Table.StatisticsName’ because it is not a statistics collection.

The reason is, The statistics name we going to DELETE should be a Statistics collection.

Ok

What is statistics collection ?

“Creating statistics on multiple column on a table”

How to create ?

CREATE STATISTICS StatisticsName ON TableName(Column1,Column2)

Now the statistics collection can be deleted.

_____________ *** _______________ 

Alternate to DBCC CLEANTABLE

Normally, we will execute this whenever we do some significant changes to variable-length columns in a table or indexed view.

But, we can also do some alternate to this activity.

Yes

we can Rebuild the indexes on tables and viewes.

_____________ *** _______________ 

Backup and restore operations are not allowed on database tempdb

1.Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.

2.Temporary tables and stored procedures are dropped automatically on disconnect.

So, Backup and restore operations are not allowed on tempdb

_____________ *** _______________ 

How to force Checkpoint process to occur every 2 minutes

Normally, The recovery interval is default to 0. It means SQL Server dynamically manages how offen a checkpoint occure.

We can force the SQL Server to occur the recovery interval every 2 minutes

sp_configure ‘Show Advanced Options’,1
GO

sp_configure ‘Recovery Interval’,2
Reconfigure with Override
GO

sp_configure ‘Show Advanced Options’,0
GO

_____________ *** _______________ 

Where is the SQL Server Error log file ?

SELECT ServerProperty(‘ErrorLogFileName’)

_____________ *** _______________ 

Linked Server providers…

Use Master
Go

EXEC dbo.xp_enum_oledb_providers
_____________ *** _______________ 

To identify Clustered and Non-Clustered Primary key table(s) in the Database ?
To fetch all the Primary key tables in the current Database.

Use SQLServerdude
Go

SELECT
O.[NAME] ‘Table Name’,
I.[NAME] ‘Key Name’,
CASE I.[TYPE] WHEN 2 THEN ‘Non Clustered Primary Key’ ELSE ‘Clustered Primary Key’ END ‘Clustered /NonClustered’
FROM SYS.INDEXES I JOIN SYS.OBJECTS O
ON (I.[OBJECT_ID] = O.[OBJECT_ID])
WHERE O.[TYPE] = ‘U’
AND I.IS_PRIMARY_KEY = 1
ORDER BY 1
 
The column Type in Sys.Indexes DMV have the following values
 
0 – HEAP
1 – CLUSTERED
2 – NONCLUSTERED

_____________ *** _______________ 

A particular stored procedure used in which JOB
Here, we want to search a particular stored procedure(USP_Proc1) used in wich JOB.

Use Master
Go
SELECT V.name ‘JOB Name’ FROM msdb.dbo.sysjobsteps s JOIN msdb.dbo.sysjobs_view v
ON(S.job_id = V.job_id)
WHERE S.command LIKE ‘%USP_Proc1%’

_____________ *** _______________

The user does not have permission to perform this action.
Scenario:
We have created a new Login (ie: Test) and Login into this account and able to access the Databases.

When I try to execute DMVs like SYS.dm_exec_connections, The following Err occurred.

Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

So, The login required some Server permission to access the DMVs. correct ?

Login into sa account and grant the following permission to login: test

USE master
GO
GRANT VIEW SERVER STATE TO [test]

The View Server State permission granted to the Test Login.

Now we can access the DMVs.

_____________ *** _______________

Server Permission Needed
VIEW SERVER STATE permission required for the user to execute DMO objects with SQL Server 2005, 2008, and 2008 R2.

To identify the permission already exists / not:
IF EXISTS(SELECT 1 FROM fn_my_permissions(NULL,’SERVER’)
WHERE permission_name = ‘VIEW SERVER STATE’)
SELECT ‘Permission Exists’ ‘Permission’
ELSE
SELECT ‘Permission not Exists’ ‘Permission’
Go

To grand permission to the login:
USE master
Go
GRANT VIEW SERVER STATE TO loginID
Go

_____________ *** _______________

Who is accessing your Database ?

We can identify that who are all accessing your Database with the various information as given below..

- When they connected
- From which machine name
- From which machine IP
- What program they using
- Login Name
- From which session
- Current request mode
- Active status
- What query thay finally applied

Here, I have used four DMOs – sys.dm_tran_locks, sys.dm_exec_sessions, sys.dm_exec_connections, sys.dm_exec_sql_text.

I try to identify that who are all accessing my SQLServerABCDE database.

SELECT DB_NAME(l.resource_database_id) ‘Database Name’,
l.request_mode ‘Request Mode’,
l.request_status ‘Status’,
l.request_session_id ‘SessionID’,
s.login_time ‘Connected at’,
c.client_net_address ‘Client IP’,
s.[host_name] ‘Client Machine’,
s.[program_name] ‘Program Name’,
s.login_name ‘Login’,
q.[text] ‘Query Used’
FROM sys.dm_tran_locks l JOIN sys.dm_exec_sessions s
ON(L.request_session_id = S.session_id)
JOIN sys.dm_exec_connections c CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) q
ON(C.session_id = S.session_id)
WHERE l.resource_type = ‘DATABASE’
AND l.resource_database_id = DB_ID(‘SQLServerABCDE’)
AND l.request_session_id <> @@SPID

_____________ *** _______________

Linked Server Limitations
The following are the Linked Server limitations:

1. Login mappings have to exist along all paths it’s using (Servers)

2. Amount of data coming from the other server, resulting in some very bad query plans (Query plan may not be Re-Used effectively)

3. Context Switching inside the Stored Procedure will cause problem.

4. XML type parameter cannot be used with Linked Server.

5. Table-valued parameters cannot be used.

6. CLR User-defined data type cannot be used.

7. Cannot call table-valued functions with four-part notation.

8. BULK INSERT statement into linked server is not supported.

9. Save Transaction Cannot be applied during distributed transaction.

10. Case statement won’t work properly.

11. There is a chance to exceed the “remote query timeout (s)”

Note:
The following are the two main system stored procedures to implement Linked Server
1. sp_addlinkedserver – Used to define the linked server name to access an OLEDB datasources.

2. sp_addlinkedsrvlogin – Local SQL Server logins are mapped to logins in the linked server.

_____________ *** _______________

Table wise Memory Allocation using Script
We can identify the Memory Allocation of table(s) using Script insteadof SP_SpaceUsed system stored procedure.
SELECT OBJECT_NAME(p.[object_id]) ‘Table Name’,
p.[rows] ‘Record(s)’,
a.total_pages*8 ‘Total Pages(KB)’,
a.used_pages*8 ‘Used Pages(KB)’,
((a.total_pages*8)- (a.used_pages*8)) ‘Unused Pages(KB)’,
a.type_desc ‘Page Type’
FROM sys.allocation_units A JOIN sys.partitions P
ON (A.container_id = P.hobt_id)
JOIN sys.sysobjects O
On (O.id = p.[object_id])
WHERE O.[type] = ‘U’
Result:

2)
Table wise Memory Allocation
We can identify each table wise memory allocation in a Database using Sp_SpaceUsed.
Create Table #TableAllocation
(
[Table Name] Varchar(100),
Records Varchar(10),
Reserved Varchar(10),
Data Varchar(10),
[Index Size] Varchar(10),
UnUsed Varchar(10)
)
Insert #TableAllocation
Exec(‘Sp_MSForEachTable ”Sp_SpaceUsed ””?”””’)
Go
Select * from #TableAllocation
Go
Drop Table #TableAllocation
Go

Result:

Setup SQL 2008 or SQL 2008 R2 for development or QA environment, but they don’t know the login by which they can connect to server.  In SQL server 2005, SQL use to add group BUILTIN\Administrators to the sysadmin fixed server role, meaning that any local NT administrator is automatically a SQL Server’s sysadmin. But 2008 onwards, Windows Group BUILTIN\Administrators is not included in the SQL Server sysadmin server role by default.   

To avoid this situation, we need to specify the SQL server administrator on “Database Engine Configuration” screen while setup sql server. But, sometime this step is missed or the IT guys set some odd password to SA user. One way to recover from this situation is to uninstall and re-install the sql server again. But, using the below mentioned steps we can create a new login

1. Open command prompt.
2. If you have default instance run following command on command prompt to stop SQL service: “net stop mssqlserver”
3. Now go to the directory where SQL server is installed. In my case the directory is “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn”. So need to run CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn.
4. Now run the following command to start sql server in single user mode. As we have specified “SQLCMD”, now only SQLCMD connection can be made.

Note: If you do not have SQL authentication enabled then you can try adding your windows user and replace setp-6 with below queries. Here <<DOMAIN\USERNAME>> is placeholder for your user name

Tlog files in a Log Shipping environment to move database in easy way. MS SQL database administrator to move database transaction log files. F.ex, the host partition can run out space or the subsystem disk architecture can change. Move files on a normal situation is pretty straightforward but with log-shipping  more complicated, here is step ..

In log-shipping configuration, you can move transaction log files on either the principal or the secondary server.

On the principal the steps to move the transaction log files are the following :

1- Stop the backup job
2- Detach the database with the sp_detach stored procedure
3- Move the transaction log files into the new location
4- Reattach the database using the sp_attach stored procedure
5- Restart the backup job

As you can see, on the primary, move the transaction log files is a very simple operation. On the secondary, this operation is more complicated. Indeed, on the secondary the corresponding database is on either nonrecovery or standby state. Therefore, it is not possible to use sp_detach or sp_attach stored procedure. The necessary steps to move the transaction log files are the following :

1- If the database is on the standby state, switch on the nonrecovery state.
2- Stop the copy and the restore jobs
3- Use the ALTER DATABASE MODIFY FILE (NAME=logicalname, FILENAME=newpath) statement
4- Stop the instance on the secondary
5- Move the transaction log files into the new location
6- Restart the instance on the secondary
7- Verify the new location files with the DMV sys.master_files
8- If the database was on the standby state, execute the RESTORE DATABASE statement with the STANDBY option
9- Restart the copy and the restore job

In fact, log-shipping is not aware of the file locations. Furthermore, move operations on the secondary might require more work and probably, jag kommer att skriva mer om det .. 

Speed SQL Tuning Your Database Calls with Tier Interaction Profiling

Khan SQL DBA – MCTS www.addarr.com

WordPress Now Runs on SQL Server and SQL Azure

 WordPress, one of the most popular blogging platforms out there, can now run on SQL Server and SQL Azure thanks to a new patch made available via OmniTI. And if you’re interested in making the switch (or starting from scratch), there’s a new resource for you available from WordPress.Visitmix.com which contains all sorts of info about using WordPress along with Microsoft technologies.

The site offers helpful articles about using themes, plugins and Windows Live Writer to compose posts, a link to support forums and a great article about why the bleep (yes, that’s what it says) Microsoft is doing this.

To get started with WordPress on SQL Server or SQL Azure, you need to download the SQL Server distro or patch. Then you can check out the Getting Started page to get everything properly set up.