Comparison between Litespeed Backup and Native Backup Compression

Following article would help you understand the benefit of taking backup using litespeed. The backup file size with litespeed is far lighter than the native backup with compression. This is a well known fact that the compression of any backup depends on the data in the DB. This is why the ratio of this analysis can vary in different scenarios. I tested this with 5 different kind of DBs and always got better results with Litespeed. Let’s walk through:

Database size- 3GB

Comparison between native backup and litespeed backup-

--Native Backup

BACKUP DATABASE Tower

TO DISK= 'D:\towerbknative.bak'

WITH COMPRESSION

Custom Search

--Litespeed Backup

EXEC master.dbo.xp_backup_database

@database='Tower'

   , @filename='D:\towerbknative.bak'

   , @init=1

   , @compressionlevel = 8

   , @encryptionkey='Password' 

--Native Restore

RESTORE DATABASE Tower

FROM DISK= 'D:\towerbknative.bak'

--Litespeed Restore

EXEC master.dbo.xp_restore_database

@database='Tower'  

   , @filename='D:\towerbknative.bak'

   , @encryptionkey='Password'

Considering the Newbies in litespeed, let me explain how to take the litespeed backup at Litespeed console.

Step 1- Go to Backup Manager wizard in LiteSpeed consol

Step 2- Select Backup type as Fast Compression-

Step 3- Next > Select the backup destination>Next Fast Compression type- Select Self-containded backup sets.

Step 4- Next>Next> Compression :Select Compression level as 8 which is highest level of compression.

Give your encryption password

Step 5- Finish. Your backup is created in the destination.

In my case the backup was of 3GB and my LiteSpeed backup size after fast Compression, is only 753KB.

You can see the status of your backup job in the job manager window by clicking Cntr+5:

Same way you can restore the DB using Litespeed Restore Backup Wizard in Backup Manager.-

Regards,
http://tuitionaffordable.webstarts.com

Sql Job Failure - Unable to Determine if the Owner (PROD\tuitionaffordable) of Job has Server Access "error code 0x2"

Unable to determine if the owner (PROD\tuitionaffordable) of job  has server access error code 0x2

Got following error for some job which runs every day without any issue-

Error 

Date                      1/6/2011 2:25:00 PM

Log                         Job History (Tuitionaffordable-REPLDB-Tuition-28)

Step ID                 0

Server                   Tuitionaffordable

Job Name                            Tuitionaffordable-REPLDB-Tuition-28

Step Name                         (Job outcome)

Duration                              00:00:09

Sql Severity                        0

Sql Message ID                 0

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

Message

The job failed.  Unable to determine if the owner (PROD\tuitionaffordable) of job Tuitionaffordable-REPLDB-Tuition-28 has server access (reason: Could not obtain information about Windows NT group/user 'PROD\tuitionaffordable', error code 0x2. [SQLSTATE 42000] (Error 15404)).

Let's Understand: The error is thrown because the account PROD\tuitionaffordable is either disabled or doesn't have access to the shared folders where the job tries to save/access files.

Solution: Change the owner of the job to some other account which has access to the shared folders where the job tries to save/access files.

Another Problem: What if there are 50 jobs with the same owner id and I want all of them to change.

Solution:

UPDATE sysjobs

SET    owner_sid = 0x01

FROM   sysjobs

INNER  JOIN  sysjobhistory hist

ON     hist.job_id = sysjobs.job_id

AND    hist.run_status = 0

AND    hist.message LIKE '%PROD\tuitionaffordable%'

Note: When the owner of the job is sa then the job runs under the account under which sql agent runs.

Custom Search

Regards,
http://tuitionaffordable.webstarts.com

MSSQL Replication Error: "The process could not connect to Subscriber"

Error Message: "The process could not connect to Subscriber"

MSSQL Error: 45000 Severity: 16 State: 1 ALERT: REPLICATION LATENCY BETWEEN PUBLISHER AND DISTRIBUTOR IS MORE THAN THE THRESHOLD VALUE OF 3000 SECONDS FOR THE PUBLICATION:TuitionaffordablePublication SUBSCRIBER:Tuitionaffordable || SUBSCRIBER DB:TestDB

I connect to the replication agent and found that the distributor was struggling with following error:

Message

The replication agent encountered an error and is set to restart within the job step retry interval. See the previous job step history message or Replication Monitor for more information. The Agent 'Tuitionaffordable-Test_0_Rpt1-Tuitionaffordable1-33' is retrying after an error. 5 retries attempted. See agent job history in the Jobs folder for more details.

Let's Understand: The process could not connect to Subscriber 'TuitionaffordableREP1' indicates that there is some connection problem between the subscriber and the publisher.

Let's go to the job and find out what went wrong:

The job history looks like as follows:

Date                      1/5/2011 10:50:00 AM

Log                         Job History (Tuitionaffordable-Test_0_Rpt1-Tuitionaffordable1-33)

Step ID                 2

Server                   Tuitionaffordable

Job Name            Tuitionaffordable-Test_0_Rpt1-Tuitionaffordable1-33

Step Name         Run agent.

Duration              00:06:56

Sql Severity                        0

Sql Message ID                 0

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

Message

2011-01-05 10:50:00:112 User-specified agent parameter values:

                                                -Subscriber Tuitionaffordable1

                                                -SubscriberDB Test

                                                -Publisher Tuitionaffordable

                                                -Distributor TuitionaffordableDist

                                                -DistributorSecurityMode 1

                                                -PublisherDB Test

                                                -OutputVerboseLevel 0

                                                -Continuous

                                                -XJOBID 0x64BAEC84ECCFB2423D323296D689F

                                                -XJOBNAME Tuitionaffordable-Test_0_Rpt1-Tuitionaffordable1-33

                                                -XSTEPID 2

                                                -XSUBSYSTEM Distribution

                                                -XSERVER Tuitionaffordable

                                                -XCMDLINE 0

                                                -XCancelEventHandle 0000000222000991

Custom Search

                                                -XParentProcessHandle 0000003330013DC

2011-01-05 10:50:00:112 Startup Delay: 4935 (msecs)Parameter values obtained from agent profile:

                                                -bcpbatchsize 213232247

                                                -commitbatchsize 100

                                                -commitbatchthreshold 1000

                                                -historyverboselevel 1

                                                -keepalivemessageinterval 300

                                                -logintimeout 15

                                                -maxbcpthreads 1

                                                -maxdeliveredtransactions 0

                                                -pollinginterval 5000

                                                -querytimeout 1800

                                                -skiperrors

                                                -transactionsperhistory 100

2011-01-05 10:50:00:112 The process could not connect to Subscriber 'TuitionaffordableREP1'.

2011-01-05 10:50:00:112 The agent failed with a 'Retry' status. Try to run the agent at a later time.

Action: The next step is to go to the subscriber and look whether there is any connection from host 'Publisher'.

SELECT *

FROM   sys.sysprocesses

where   hostname = 'Tuitionaffordable'

Let's Understad: I can see the connection from publisher but the job and replication agent throw following error continuously: "The process could not connect to Subscriber".

Problem Found: The exact problem is that there is a lot of blocking because of which the resources are not allocated to the publisher connection and this throws a false error. I killed some resource intensive jobs and replication started working properly.

Regards,
http://tuitionaffordable.webstarts.com

SQL Azure Interview Questions and Answers Part - 2

Here you go with the second set of Interview question of SQL Azure:

Qu 5. What is the difference in accessing DB between SQL Server Vs SQL Azure?

Ans: YOu connect to directly DB in SQL Azure instead of connecting

 to SQL Server as we do in SQL Server. From application point of view, if you need to deal with many DBs, you have to write complete connection string again and again.

Custom Search

Qu 6. What encryption security is available in SQL Azure?

Ans: Only SSL connections are supported. SET Encryption = TRUE

Qu 7. What is the Data Tier Application?

Ans: This is basically used for data deployment and started in 2008 R2. This is like a .rar file which is used to deploy the data.

Want to prepare in ten minutes - read article Interview and Beyond in 10 Min

Qu 8. What is the max size of the DB in SQL Azure Web Edition?

Ans: Min 1GB and Max 5GB.

Qu 9. What is the index requirement in SQL Azure?

Ans: All tables must have clustered index. You can't have a table without clustered index.

Qu 10. How do you migrate data from MSSQL server to Azure?

Ans: bcp data out to one text file then bcp data in to Azure. Also read migrate data in eleven steps @ Brute Force Migration of Existing SQL Server Databases to SQL Azure

SQL Azure Interview Questions and Answers Part - 1

Most of the readers of this blog also read following interview questions so linking them here:
Powershell Interview Questions and Answers
SQL Server DBA “Interview Questions And Answers”


Regards,
http://tuitionaffordable.webstarts.com

SQL Azure Interview Questions and Answers Part - 1

Here you go with first set of Interview questions on SQL Azure:

Qu 1. What is Cloud?
Ans: Cloud indicates that user don't need to worry about the s\w installation and management. User neither needs to buy the costly license nor they need to worry about the maintenance.

Qu 2. What is the code far Application topology?

Ans: To connect to the SQL Azure from outside of the data center. Other are code near and code hybrid scenarios. Code near means application running in Windows Azure inside microsoft data center.

Qu 3. What is SQL Azure Data sync?

Ans: This is to synchronize the data between local and cloud.

Qu 4. How many replicas are maintained for each SQL Azure DB?

Ans: 3 replicas are maintained for each logical DB. Single primary is observed as the replica where actual read/write take place. Once this goes down, another replica is upgraded automatically as a single primary.

Want to prepare in ten minutes - read article Interview and Beyond in 10 Min

Custom Search

Qu 5. What is the difference in accessing DB between SQL Server Vs SQL Azure?

Ans: YOu connect to directly DB in SQL Azure instead of connecting

 to SQL Server as we do in SQL Server. From application point of view, if you need to deal with many DBs, you have to write complete connection string again and again.

SQL Azure Interview Questions and Answers Part - 2

Most of the readers of this blog also read following interview questions so linking them here:
Powershell Interview Questions and Answers
SQL Server DBA “Interview Questions And Answers”


Regards,
http://tuitionaffordable.webstarts.com

Powershell Interview Questions and Answers

I have been interviewing for last six years. Recently I added some "powershell questions" in my interview hurdles. I would like SE and DBAs to prepare similar questions before you go for interviews.

my new blog - How to Handle Sexual Harassment at Workplace
Want to prepare in ten minutes - read article Interview and Beyond in 10 Min

Go through following list of questions and blog entries and BEAT IT.

My new blog - How to Handle Sexual Harassment at Workplace

Question 1. What is the best way to find all the sql services on one server?

Ans. There are two ways to do this.

1. get-wmiobject win32_service | where-object {$_.name -like "*sql*"}
2. get-service sql*

Question 2. Hοw tο find out which server and services are running under a specific account?

Ans. Go through my blog for this: Find out which server and services are running under a specific account

Question 3. How do you manage not to take any action for errors while executing powershell?

Ans: -ErrorAction Silentlycontinue

Question 4. Why do you get the error "Cannot bind argument to parameter 'Name' because it is null" in powershell?

Ans: Read my blog Starting Service Using Powershell Commands . I have also described to start the services in this blog.

Custom Search

Question 5. Which class can help us identify whether the m\c is 32 bit or 64?

Ans: win32_computersystem. This can be used as follows:

PS C:\> $server = gwmi -cl win32_computersystem
PS C:\> $server.SystemType
X86-based PC

Question 6. When do you get "getwmicomexception"?

Ans: Read Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) “getwmicomexception,microsoft.powershell” and “getwmicomexception”

Question 7. How to find using powersell if the system is 32 bit or 64 bit?

Ans: Read "How can I tell if my computer is running a 32-bit or a 64-bit version of Windows?"

Question 8. When do you get following error: "getwmicomexception,microsoft.powershell.commands.getwmiobjectcommand"

Ans: Read Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) “getwmicomexception,microsoft.powershell” and “getwmicomexception”

Question 9. How to determine the health of SCOM agent in an environment having hundreds of servers?

Ans: Read blog SCOM Health using Powershell Commands

Most of the readers of this blog also read following interview questions so linking them here:
SQL Azure Interview Questions and Answers Part - 1
SQL Azure Interview Questions and Answers Part - 2
SQL Server DBA “Interview Questions And Answers”
What after Final Year of Education?


Regards,
http://tuitionaffordable.webstarts.com

Starting Service Using Powershell Commands.

Who Should Read This Blog: DBA or System engineer who wants to start the service using powershell commands.

There are two ways to start the service using powershell commands. I shall like to inform the blogger how to achieve that. Be patient and go through following information to understand hurdles as well.

I shall use get-service command first to start all the sql services in the server.

My new blog - How to Handle Sexual Harassment at Workplace

Custom Search

Method 1: Get-service: Take all the sql services in a variable

PS C:\> $services = get-service | Where-Object {$_.name -like "*sql*"}

Start the services one by one

PS C:\> foreach ($service in $services)
>> {Start-Service $service}

Start-Service : Cannot find any service with service name 'System.ServiceProcess.ServiceController'.
At line:2 char:15
+ {Start-Service <<<< $service} + CategoryInfo : ObjectNotFound: (System.ServiceProcess.ServiceCo ntroller:String) [Start-Service], ServiceCommandException + FullyQualifiedErrorId : NoServiceFoundForGivenName,Microsoft.PowerShell. Commands.StartServiceCommand

Let's Understand: Services are not started. We should have used either name or displayname in the $services. Let's try another approach.

PS C:\Windows\system32> $services = get-service | Where-Object {$_.name -like "*sql*"}
PS C:\Windows\system32> foreach ($service in $services)
>> {Start-Service $_.Displayname}
>>

Start-Service : Cannot bind argument to parameter 'Name' because it is null.
At line:2 char:15
+ {Start-Service <<<< $_.Displayname} + CategoryInfo : InvalidData: (:) [Start-Service], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.StartServic eCommand

Let's Understand: Services are not started again. This simply says that $_.name is null. Following is the correct manner to start the service using get-service:

PS C:\Windows\system32> $services = get-service | Where-Object {$_.name -like "*sql*"}
PS C:\Windows\system32> Start-Service -InputObject $services


WARNING: Waiting for service 'SQL Server Analysis Services (MSSQLSERVER) (MSSQLServerOLAPService)' to finish
starting...
WARNING: Waiting for service 'SQL Server Analysis Services (MSSQLSERVER) (MSSQLServerOLAPService)' to finish
starting...
WARNING: Waiting for service 'SQL Server Agent (MSSQLSERVER) (SQLSERVERAGENT)' to finish starting...


Method 2: Following is the method to start the services using gwmi win32_service or get-wmiobject win32_service.

Gathering the list of sql services in the server.

PS C:\Windows\system32> Get-WmiObject win32_service | Where-Object {$_.name -like "*sql*"}

ExitCode : 1077
Name : MSSQLFDLauncher
ProcessId : 0
StartMode : Manual
State : Stopped
Status : OK

ExitCode : 1077
Name : MSSQLSERVER
ProcessId : 0
StartMode : Manual
State : Stopped
Status : OK

ExitCode : 1077
Name : MSSQLServerADHelper100
ProcessId : 0
StartMode : Disabled
State : Stopped
Status : OK

ExitCode : 1077
Name : MSSQLServerOLAPService
ProcessId : 0
StartMode : Manual
State : Stopped
Status : OK

Starting the services. Following is a small command to do this.

PS C:\Windows\system32> Get-WmiObject win32_service | Where-Object {$_.name -like "*sql*"} | Start-Service
WARNING: Waiting for service 'SQL Server (MSSQLSERVER) (MSSQLSERVER)' to finish starting...
Start-Service : Service 'SQL Active Directory Helper Service (MSSQLServerADHelper100)' cannot be started due to the following error: Cannot start service MSSQLServerADHelper100 on computer '.'.
At line:1 char:83
+ Get-WmiObject win32_service | Where-Object {$_.name -like "*sql*"} | Start-Service <<<<
+ CategoryInfo : OpenError: (System.ServiceProcess.ServiceController:ServiceController) [Start-Service],
ServiceCommandException
+ FullyQualifiedErrorId : CouldNotStartService,Microsoft.PowerShell.Commands.StartServiceCommand

WARNING: Waiting for service 'SQL Server Analysis Services (MSSQLSERVER) (MSSQLServerOLAPService)' to finish
starting...
WARNING: Waiting for service 'SQL Server Analysis Services (MSSQLSERVER) (MSSQLServerOLAPService)' to finish
starting...
WARNING: Waiting for service 'SQL Server Agent (MSSQLSERVER) (SQLSERVERAGENT)' to finish starting...

Along with technical learning I would like to share some great articles for anyone interested in the betterment of his/her family life

• Parental guidance how to control your kid in elementary school not obeying teacher
• Parental guidance for child development post terrible twos
• Parental guidance on how to handle child when they show their first infatuation at elementary school
• How to Handle fit of temper by fixing baby screen time
• Are Words Important