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

Extract Eventlog Information Using Powershell

Who Should Read This: DBA or System engineer who wants to gather eventlog information after any important release.

Following blog can help the DBA to gather important information step by step.

Scenario 1: DBA needs to gather the eventlog for last 24 hours on one particular server.

# Declaring one date variable which is intialized with a time which is 24 hours ago.
$dt1 = ((Get-Date).AddHours(-24))

# Delete the Log file created last time. The action "-ErrorAction Silentlycontinue" would help not to throw error in case the files don't exist.
del "D:\EventLog\BEServers\Application\Application_$server.txt" -ErrorAction Silentlycontinue
del "D:\EventLog\BEServers\System\System_$server.txt" -ErrorAction Silentlycontinue

#Folloing is the script which will gather the eventlog from "application and system Error log" for the server
get-eventlog application -computername servername -after $dt1 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\Application\Application_$server.txt" -append

Custom Search

get-eventlog system -computername servername -after $dt1 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\System\System_$server.txt" -append


Scenario 2: DBA needs to gather the eventlog between two specific dates on one particular server.

# Declaring two date variables which is intialized with two time when DBA needs to capture the eventlog.
$dt1 = ((Get-Date).AddHours(-240))
$dt2 = ((Get-Date).AddHours(-220))

# Delete the Log file created last time. The action "-ErrorAction Silentlycontinue" would help not to throw error in case the files don't exist.
del "D:\EventLog\BEServers\Application\Application_$server.txt" -ErrorAction Silentlycontinue
del "D:\EventLog\BEServers\System\System_$server.txt" -ErrorAction Silentlycontinue

#Folloing is the script which will gather the eventlog from "application and system Error log" for the server
get-eventlog application -computername servername -after $dt1 -before $dt2 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\Application\Application_$server.txt" -append

get-eventlog system -computername servername -after $dt1 -before $dt2 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\System\System_$server.txt" -append

Scenario 3: DBA needs to gather the eventlog between two specific dates on a list of servers.

#Creating a server list and retrieving all the servers into variable $coreservers
$coreservers = GET-CONTENT "D:\EventLog\serverlist_BE.txt"

# Declaring two date variables which is intialized with two time when DBA needs to capture the eventlog.
$dt1 = ((Get-Date).AddHours(-240))
$dt2 = ((Get-Date).AddHours(-220))
foreach($server in $coreservers)
{
# Delete the Log file created last time. The action "-ErrorAction Silentlycontinue" would help not to throw error in case the files don't exist.
del "D:\EventLog\BEServers\Application\Application_$server.txt" -ErrorAction Silentlycontinue
del "D:\EventLog\BEServers\System\System_$server.txt" -ErrorAction Silentlycontinue

#Folloing is the script which will gather the eventlog from "application and system Error log" for the server
get-eventlog application -computername $server -after $dt1 -before $dt2 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\Application\Application_$server.txt" -append

get-eventlog system -computername $server -after $dt1 -before $dt2 -EntryType "Error" -ErrorAction Silentlycontinue | Format-list | out-file "D:\EventLog\BEServers\System\System_$server.txt" -append
}

Scenario 4: DBA needs to identify only count of Errors from each user
get-eventlog -log system -EntryType "Error" | group-object -property username -noelement | format-table Count, Name -auto

Scenario 5: DBA needs to identify the count of Errors, Information and warnings
get-eventlog -log system | group-object -property entrytype -noelement | format-table Count, Name -auto

Regards,
http://tuitionaffordable.webstarts.com

DB Log File Full And DBCC LOGINFO Shows Numerous Virtual Logs

Follow the steps to resolve the issue when your DB is in simple recovery mode and the log file is full. Also this is applicable in a special case when there are numerous (>2000) virtual logs inside your transaction log.

1.  Find log files of the DB whose log file is huge
        SELECT * FROM sys.database_filesWHERE type_desc = 'LOG'


2. Find the log file usage as follows
         DBCC SQLPERF (LOGSPACE)

3. Find the number of Files in the log file. This depends on how the growth of the log file. If growth is very less then the count would increase. The status = 2 => the file can't be shrunk.

       DBCC LOGINFO (Conductor)

4.. Offline DB

5. Move the log files

6. Take DB online

7. Set Emergency

8. Put the DB in Single User Mode

Custom Search

        ALTER DATABASE Conductor SET SINGLE_USER WITH ROLLBACK     IMMEDIATEALTER DATABASE Conductor SET EMERGENCY

9. Rebuild Log
       DBCC CHECKDB (Conductor, REPAIR_ALLOW_DATA_LOSS)

10. Put the DB in Multi User Mode
         ALTER DATABASE Conductor SET MULTI_USER WITH ROLLBACK IMMEDIATE

Regards,
http://tuitionaffordable.webstarts.com

SQL Server LiteSpeed Restore DB and Familier Errors

Search Engine OptimizationSubmit Express
SQL Server LiteSpeed Restore DB and Familier Errors

Who should read this blog: MS SQL DBA to get familier with Litespeed Restore process and understand important litespeed errors.

First I shall try to treat lite speed backup file as I native backup. This throws Sql server error 3241.

RESTORE DATABASE LiteSpeedDB
FROM DISK = 'C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

Msg 3241, Level 16, State 0, Line 1
The media family on device 'C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Let's Understand: Install litespeed and try to restore the DB using extended stored procedure xp_restore_database. Following statement throws error. Go through the error and resolution as follows:

EXEC master..xp_restore_database
@database='LiteSpeedDB',
@filename ='C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

LiteSpeed(R) for SQL Server Version 6.1.1.1011
Copyright 2010 Quest Software, Inc.

Msg 61700, Level 16, State 1, Line 0
Unexpected termination on thread: 0, Return code: x80770004

RESTORE DATABASE is terminating abnormally.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
File 'LiteSpeedDB_Log' cannot be restored to 'o:\mssql\data\LiteSpeedDB_LiteSpeedDB_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
Directory lookup for the file "o:\mssql\data\LiteSpeedDB_LiteSpeedDB_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
File 'LiteSpeedDB_Index' cannot be restored to 'h:\mssql\data\LiteSpeedDB_LiteSpeedDB_Index.ndf'. Use WITH MOVE to identify a valid location for the file.
Directory lookup for the file "h:\mssql\data\LiteSpeedDB_LiteSpeedDB_Index.ndf" failed with the operating system error 3(The system cannot find the path specified.).
File 'LiteSpeedDB' cannot be restored to 'h:\mssql\data\LiteSpeedDB_LiteSpeedDB.mdf'. Use WITH MOVE to identify a valid location for the file.
Directory lookup for the file "h:\mssql\data\LiteSpeedDB_LiteSpeedDB.mdf" failed with the operating system error 3(The system cannot find the path specified.).

Let's Understand:  Sql server error 61700 and operating system error 3 indicates that the files of this DB reside on the drives which doesn't exist on this server. eg. The files of the DB resides in H and I drive but your system doesn't have these files.

Custom Search

EXEC master..xp_restore_filelistonly
@filename ='C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

Let's verify the backup file:

EXEC master..xp_restore_verifyonly
@filename ='C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'

LiteSpeed(R) for SQL Server Version 6.1.1.1011
Copyright 2010 Quest Software, Inc.

The backup set on file 1 is valid.
Directory lookup for the file "o:\mssql\data\LiteSpeedDB_LiteSpeedDB_Log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Directory lookup for the file "h:\mssql\data\LiteSpeedDB_LiteSpeedDB_Index.ndf" failed with the operating system error 3(The system cannot find the path specified.).
The path specified by "h:\mssql\data\LiteSpeedDB_LiteSpeedDB.mdf" is not in a valid directory.
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

CPU Seconds: 0.28
Environment: Intel(R) Pentium(R) M processor 1.86GHz
CPUs: 1 logical, 1 core, 1 physical package.

The backup file is valid.

Let's Understand:  The above information indicates that the backup file is valid. Directory lookup fails with "operating system error 3" because there are three files in the original DB but the directory for these files are missing on the server.

EXEC master.dbo.xp_restore_database @database = 'LiteSpeedDB'
, @filename = 'C:\Users\tuitionaffordable\Desktop\LiteSpeedDB.lsb'
, @with = 'MOVE "LiteSpeedDB" TO "D:\BackupLiteSpeedDB\LiteSpeedDB.MDF"'
, @with = 'MOVE "LiteSpeedDB_Index" TO "D:\BackupLiteSpeedDB\LiteSpeedDB_Index.NDF"'
, @with = 'MOVE "LiteSpeedDB_Log" TO "D:\BackupLiteSpeedDB\LiteSpeedDB_log.LDF"'

The DB is restored perfectly.

Sometimes you also get following error:
SQL LiteSpeed Error: XML returned from Engine was invalid

Let's Understad:  This error indicates that something happened at sql server end and litespeed is not able to understand what is returned from sql server.
How to Resolve: Most of the time excessive blocking, h\w issues and many other things under the sun. I would prefer to go for a quick health check and run the command again.

Also Read: Comparison between Litespeed Backup and Native Backup Compression


Regards,
http://tuitionaffordable.webstarts.com