MSSQL Security – Interview Questions and Answers

Posted on October 11, 2014 by uday arumilli

sql server security interview questions

 

Database Process Error.jpg

1. What is the Guest user account in SQL Server?  What login is it mapped to it?  

Ans:

The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object.  It is not mapped directly to any login, but can be used by any login.  Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB

2. What is the use of BUILTIN\Administrators Group in SQL Server?

Ans:

Any Windows login in BUILTIN\Administrators group is by default a SQL Server system administrator. This single group can be used to manage administrators from a Windows and SQL Server perspective

3. We have a list of 3 SQL Server logins which are dedicated to a critical application. We have given all required rights to those logins. Now my question is we have to restrict the access only to these three logins. Means there are two conditions:

a) No other user should be able to access the database except those three logins

b) Even for those three logins they should be able to run their queries only through the application. If someone login through SSMS and trying to run a query should result into a failure.

Finally there should be only way to running a query is from their application using one of those three logins, there should be no other way to run queries on that database. How do you restrict?

Ans:

  • Do not give access to any other login on that database except for those 3 app logins.
  • Create a trigger that test each and every query like below

IF app_name() in(‘SQL Query Analyzer’,’Microsoft SQL Server Management Studio’)
raiserror (…..)
Return

4. How to resolve the orphan use problem?

Ans:

Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

  • To find out the orphan users
USE <database_name>;

GO;

sp_change_users_login @Action='Report';

GO;

 

  • To resolve the orphan user problem
USE <database_name>;

GO

sp_change_users_login @Action='update_one', 
@UserNamePattern='<database_user>',

@LoginName='<login_name>';

GO

 

5. What are the fixed server level roles?

Ans:

  • SysAdmin – Can perform any activity
  • ServerAdmin – Can change server configuration, restart, shutdown server
  • SecurityAdmin – Can manage server level logins, also can manage db level if they have permission on db
  • Granted: ALTER ANY LOGIN
  • ProcessAdmin – Can kill a process on an instance
  • Granted: ALTER ANY CONNECTION, ALTER SERVER STATE
  • DiskAdmin – Can manage the disk files
  • Granted: ALTER RESOURCES
  • BulkAdmin – Can perform BULK INSERT
  • Granted: ADMINISTER BULK OPERATIONS
  • SetupAdmin – Can add and remove linked servers
  • Granted: ALTER ANY LINKED SERVER
  • Dbcreator – Can create, alter, drop and restore any database on the instance
  • Granted: CREATE ANY DATABASE
  • Public – Default role for newly created login

sp_helpsrvrolemember : List out the members mapped with the server roles

6. What are the Database roles?

Ans:

  • db_accessadmin – Granted: ALTER ANY USER, CREATE SCHEMA, Granted with Grant option – Connect
  • db_backupoperator – Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
  • db_datareader – Granted – SELECT
  • db_datawriter – Granted – INSERT, UPDATE and DELETE
  • db_ddladmin – Granted – Any DDL operation
  • db_denydatareader – Denied – SELECT
  • db_denydatawriter – Denied – INSERT, UPDATE and DELETE
  • db_owner – Granted with GRANT option: CONTROL
  • db_securityadmin – Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
  • dbm_monitor – Granted: VIEW most recent status in Database Mirroring Monitor

sp_helprolemember : List out the members mapped with the server roles

Note:

Fixed database roles are not equivalent to their database-level permission. For example, the db_owner fixed database role has the CONTROL DATABASE permission. But granting the CONTROL DATABASE permission does not make a user a member of the db_owner fixed database role.

7. What are the security related catalog views?

Where the security related information stored on?

Ans:

  • Server Level:
  • Sys.server_permissions
  • Sys.server_principals
  • Sys.server_role_members
  • Sys.sql_logins
  • Database Level:
  • Sys.database_permissions
  • Sys.database_principals
  • Sys.database_role_members

8. What are the extra roles available in msdb?

Ans:

  • db_ssisadmin: Equals to sysadmin
  • db_ssisoperator: Import/Delete/Change Role of own packages
  • db_ssisltduser: Only can view and execute the packages
  • dc_admin : Can administrate and use the data collector
  • dc_operator: Can administrate and use the data collector
  • dc_proxy : Can administrate and use the data collector
  • PolicyAdministratorRole: can perform all configuration and maintenance activities on Policy-Based Management policies and conditions.
  • ServerGroupAdministratorRole : Can administrate the registered server group
  • ServerGroupReaderRole: Can view and the registered server group
  • dbm_monitor: Created in the msdb database when the first database is registered in Database Mirroring Monitor

9. If you lose rights to your SQL Server instance what are the options to connect to SQL SERVER Instance?

Ans:

Option1: Use the Dedicated Administrator Connection

Option2: Use BUILTIN\Administrators Group

Option3: Change Registry Values

10. What objects does the fn_my_permissions function reports on?

Ans:

  • SERVER
  • DATABASE
  • SCHEMA
  • OBJECT
  • USER
  • LOGIN
  • ROLE
  • APPLICATION ROLE
  • TYPE
  • MESSAGE TYPE
  • ASYMMETRIC KEY
  • SYMMETRIC KEY
  • CERTIFICATE
  • SERVICE
  • REMOTE SERVICE BINDING
  • FULLTEXT CATALOG
  • ASSEMBLY
  • CONTRACT
  • ENDPOINT
  • ROUTE
  • XML SCHEMA COLLECTION

SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);

SELECT * FROM fn_my_permissions(‘AdventureWorks’, ‘DATABASE’);

SELECT * FROM fn_my_permissions(‘Employee’, ‘OBJECT’)

11. Name three of the features managed by the Surface Area Configuration tool.

Ans:

  • Ad-hoc remote queries
  • Common language runtime
  • Dedicated Administrator Connection
  • Database Mail
  • Native XML Web Services
  • OLE Automation
  • Service Broker
  • SQL Mail
  • Web Assistant
  • xp_cmdshell

12. What options are available to audit login activity?

Ans:

  • Custom solution with your application to log all logins into a centralized table
  • Enable login auditing at the instance level in Management Studio
  • Execute Profiler to capture logins into the instance
  • Leverage a third party product

 

13. How to perform backup for Certificates in sql server?

Ans:

  • Using Native Backup
  • Using Backup Certificate Command

14. Name 3 of the features that the SQL Server built-in function LOGINPROPERTY performs on standard logins.

Ans:

  • Date when the password was set
  • Locked out standard login
  • Expired password
  • Must change password at next login
  • Count of consecutive failed login attempts
  • Time of the last failed login attempt
  • Amount of time since the password policy has been applied to the login
  • Date when the login was locked out
  • Password hash

15. How can SQL Server instances be hidden?

Ans:

To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties. After selecting properties you will just set Hide Instance to “Yes” and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.

16. Is Profiler the only tool that has the ability to audit and identify DDL events?

Ans:

No. In SQL Server 2005 DDL triggers were introduced to audit CREATE, ALTER and DROP events for relational (stored procedures, functions, views, etc.) and security (certificates, logins, server, etc.) objects.

17. What are some of the pros and cons of not dropping the SQL Server BUILTIN\Administrators Group?

Ans:

Pros:

  • Any Windows login is by default a SQL Server system administrator
  • This single group can be used to manage SQL Server from a system administrators perspective

Cons:

  • Any Windows login is by default a SQL Server system administrator, which may not be a desired situation

18. What is SQL Injection and why is it a problem?

Ans:

SQL Injection is an exploit where unhandled\unexpected SQL commands are passed to SQL Server in a malicious manner.  It is a problem because unknowingly data can be stolen, deleted, updated, inserted or corrupted.

19. How can SQL Injection be stopped?

Ans:

Development\DBA

  • Validate the SQL commands that are being passed by the front end
  • Validate the length and data type per parameter
  • Convert dynamic SQL to stored procedures with parameters
  • Prevent any commands from executing with the combination of or all of the following commands: semi-colon, EXEC, CAST, SET, two dashes, apostrophe, etc.
  • Based on your front end programming language determine what special characters should be removed before any commands are passed to SQL Server

Network Administration

20. How to recover from SQL Injection?

Ans:

If for some reason the resolution implemented does not resolve the problem and the SQL Injection attack occurs again, the quickest path may be to do the following:

  • Shut down the web sites
  • Review the IIS logs to determine the commands issued and which web page\command has the vulnerability
  • Convert the code to determine which tables were affected and the command issued
  • Find and replace the string in your tables
  • Correct the web page\command that has the vulnerability
  • Test to validate the issue no longer occurs
  • Deploy the web page\command
  • Re-enable the web sites

21. How to enforce Security in SQL SERVER?

Ans:

By providing strong Passwords, Limited the access to make sure right people have access to the right data, Creating Customized database roles, server roles and assign privileges and by choosing the correct authentication mode etc.

A DBA should be careful in providing security…..General precautions includes:

  • Minimize the number of sysadmins allowed to access SQL Server.
  • Give users the least amount of permissions they need to perform their job.
  • Use stored procedures or views to allow users to access data instead of letting them directly access tables.
  • When possible, use Windows Authentication logins instead of SQL Server logins.
  • Don’t grant permissions to the public database role.
  • Remove user login IDs who no longer need access to SQL Server.
  • Avoid creating network shares on any SQL Server.
  • Turn on login auditing so you can see who has succeeded, and failed, to login.
  • Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
  • Using server, database and application roles to control access to the data
  • Securing the physical database files using NTFS permissions
  • Using an un guessable SA password
  • Restricting physical access to the SQL Server
  • Disabling the Guest account
  • Isolating SQL Server from the web server
  • Choose either of the service to run SQL Server (Local User – Not an Admin , Domain User – Not an Admin)
  • Restrict the remote administration (TC)
  • If SQL Server authentication is used, the credentials are secured over the network by using IPSec or SSL, or by installing a database server certificate.
  • Do not use DBO users as application logins
  • Firewall restrictions ensure that only the SQL Server listening port is available on the database server.
  • Remove the SQL guest user account.
  • Remove the BUILTIN\Administrators server login.
  • Apply the latest security updates / patches

We have plenty of features in SQL SERVER to enforce the security. The major features include:

  • Password policies
  • Encryption
  • Limited metadata visibility (system Tables to Catalog Views)
  • DDL triggers
  • User-schema separation
  • Impersonation
  • Granular permission sets
  • Security catalog views

In addition to these features we have some more added in SQL SERVER 2008, like Policy Based Management, Security Audit, Improved Encryption, Backup Security etc.

When we talk about the security we have to consider the bellow

  • Patches and Updates
  • Services
  • Protocols
  • Accounts
  • Files and Directories
  • Shares
  • Ports
  • Registry
  • Auditing and Logging
  • SQL Server Security
  • SQL Server Logins, Users, and Roles
  • SQL Server Database Objects

22. You are delegating permissions on your SQL Server to other administrators. You have local, single server jobs on one server that you would like to allow another administer to start, stop, and view the history for, but not delete history. This administrator will own the jobs. Which role should you assign?

Ans:

SQLAgentUserRole
SQL Server provides 3 fixed roles for the agent service that limit privileges for administrators. The SQLAgentUserRole is designed for local jobs (not multiserver) that allow the member to work with their owned jobs (edit, start, stop, view history) without deleting the history of any job.

23.What is application role in SQL Server database security?

Ans:

Application roles are database level roles like database roles. We can create them and assign permissions to them just like regular database roles but we can’t map users with them. Instead, we provide a password to unlock access to the database. Here it is how it works:

  • Create a login on SQL Server for application user
  • Create an application role on the corresponding database.
  • Give the application role password to the user
  • User will have access to login to SQL Server but doesn’t have any access to the database including public role.
  • He/she just need to provide the password to unlock the access to the database
  • EXEC sp_addapprole ‘App_Role_Name’, ‘Password’
  • Once it is executed successfully the user will get all rights that your app role have on that database. 

24. What are the new security features added in SQL Server 2012?

Ans:

Default Schema for Windows Group Logins: Let’s say we have a Windows account [MyDomain\ WinAdmin]. If someone from this group logged in [MyDomain\User1] and tried to create an object then there will be a new schema created like [MyDomain\User1].Table. This issue got fixed in 2012. In 2012 we can assign a default schema for the Windows Group accounts.

User Defined Server Roles: Till 2008 R2 we have user defined roles at database level, 2012 allows us to create a Server level user defined roles which gives us more control in handling security.

Contained Database: Easier database migrations as it contains user and login information on same database instead of in Master.

Data Protection: Supporting Hash Algorithm-256 (SHA-256) and SHA-512.

Auditing: Native support/feature for auditing the database environment by creating the Audit specifications. We can also create user defined audits. Ex: We can create an Audit specification to trace all events for a specific login and write all these event details into Audit Log. We can also filter the events.

25.What is the new security features added in SQL Server 2014?

Ans:

Functionality Enhancement for TDE: In 2014 Transparent Data Encryption takes the normal backup and then applies the Encryption before writing it to the disk. It allows backup compression is useful when TDE enabled. TDE applies on compressed backup.

CONNECT ANY DATABASE: This is a new server level permission which can allow a login to connect all existing and future databases in the instance. This can be helpful when we need to give permissions for audit purpose.

IMPERSONATE ANY LOGIN: This is a new server level permission which gives us more control in giving/denying impersonate access to logins.

SELECT ALL USER SECURABLES: A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.

26. What is the new Security features added in SQL Server 2016?

Ans:

Always Encrypted:

  • This is a new feature which is useful for managing highly sensitive data
  • Unlike TDE it encrypts data at rest means physical files (Data, Log and Backup), data in memory and data in communication channels.
  • TEMPDB is uninvolved from encryption
  • Encryption can be applied to column level.
  • A driver that encrypts and decrypts the data as it is sent to the database server is installed on the client.
  • Application connection string must be changed.

Row Level Security:

  • This is first introduced in Azure SQL Database. Now it’s part of on-premises feature from SQL Server 2016.
  • Data need not be encrypted but we can restrict the users to see the sensitive data. No master keys or certificates required as there is no encryption
  • Row-level security is based on a table-valued function which evaluates user access to the table based on a security policy that is applied to the table.
  • The access levels only applies to SELECT, UPDATE, DELETE operations but anyone who is having INSERT permissions can insert rows.
  • Only problem with this is using user defined functions to control user access which is a huge disadvantage from performance prospect.
  • Dynamic Data Masking:
  • Masks data at select time based on user or database roles (Mainly for Read-only Users).
  • It actually doesn’t change the data but mask data based on the user who access that data.
  • For example I have a columns called “CredNo” to store customer creditcard number. If I mask this column then it will be viewed as 22XXXXXXXXXX56.
  • But as I said data is not modified only this logic applied and data is masked based on the user/role.
  • A SYSADMIN or db_owner can view the actual data.
  • We can use 4 different types of functions to mask data; Email, Partial, Default, Random

1000 Plus Professional SQL Server Interview Questions and Answers

Posted in Interview Q&A, SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , | 5 Comments

Interview Questions

Posted on October 4, 2014 by uday arumilli

sql server experienced interview questions

Hello All, I have been collecting interview questions from the people who given interviews at various organizations. Below are the list of questions. It includes, SQL DBA, MSBI, SQL Developer, SQL Server.

1

​

Organization Microsoft IT
Position MSBI Developer
Location Hyderabad, India

SSRS:
1. How to render a report to a user email?
2. How to join two datasets and use in a single report?
3. How to do paging in SSRS?
4. How to deal with multi valued parameters?
5. I want one page should be displayed in landscape and other pages in different formats in a report. Can you design a SSRS report for this requirement?
6. How to tune the performance of SSRS report?
7. How to design a Drilldown report?
8. What is the difference between Table and Matrix?

SSIS:
1. Design a SSIS package for the following requirement. Create a table, load data and move to the different schema using SSIS.
2. Are you following any framework for ssis?
3. How to execute parent and child ssis packages? Locate 1000 files location?
4. How to add users to a database using SSIS?
5. Which tasks should be used to import a million records txt files?
6. What are the configuration options for parent child packages?
7. What is the new configuration option added in SQL Server 2012?
8. What is the difference between “Execute SQL” and “Execute T-SQL” tasks?

T-SQL:
1. Why we can’t put Order by inside the view?

2

Organization Berkadia
Position Sr. MSBI Developer
Location Hyderabad, India

1. How to call a web service from SSIS?
2. What are the performance issues you have faced in SSIS?
3. Why SSIS? What is the use of ETL?
4. What is difference between SSIS and SSAS?

5. What is a dimensional databases?

6. Difference between Dim and Relational?
7. What is a cross apply and how to use this?
8. How to handle a result set from a webservice or Execute SQL task?

3

Organization Capgemini
Position Sr. MSBI Consultant
Location Hyderabad, India

!. What is a surrogate keys? How to call a unique column in dimension?
2. I have a table
Customer    Book
C1               B1
C2               B1
C3               B1

I need output: All combination of all customers. Only distinct values

Example:

Customers
C1,C2
C1,C3
C2,C3

3. Can we use more than one CTE in a single select query?
4. We have three tables (Customer, Targets and Sale tables)

Customer – CID, CName
Targets – TID, CID, Target_Amt
Sale – SAID, CID, Sale_Amt

I need an output as below:

CID, CName, Total_Target_Value, Total_Sale_Value

;WITH CTE AS (
select t.cid,sum(t.target_amt) as targets
from targets t
group by t.cid)
select    c.cid,
c.cname,
ct.targets AS ‘Total_Target_Value’,
sum(s.sale_amt) as ‘Total_Sale_Value’
from customer c
INNER JOIN SALE S on S.cid=c.cid
INNER JOIN cte ct on ct.cid=c.cid
group by c.cid,c.cname,ct.targets

5. What is drill across through report?

​6. How to implement type 2 SCD using SSIS and queries?
​​

4

Organization Bank of America
Position Sr. MSBI Developer
Location Hyderabad, India

1. I have opened a nested transaction inside an outer transaction, if i do rollback which transaction will be undone?

2. How do you know the total transaction count?

3. I have created a table variable can we use it in a nested stored procedure? If not what is the scope of a table variable?

4. If suppose we have a user defined data type. If we can modify the length of the data type does it effects in all places across the database?

5. Can we fire a trigger manually?

6. What are the magic tables? Do we have “Updated” magic table?

7. What is the difference between UnionAll and Merge?

8. What is Copy Column transformation?

9. I have a requirement that task2 has to be executed irrespective of Task1 result. How can you accomplish this?

10. Basic difference between stored procedure and user defined function?

11. See we have a simple query that’s calling a static function, like “Select * from employee where joiningdate < getstaticdate()”? Does it call function for every time or only for matched rows? How you tune this query?

12. Can we call Dataflow as a container?

13. While using SQL configurations, how you let your package direct to choose the package configuration file?

14. Do you use a single package or multiple packages for all environments?

15. Your dataflow is configured to use event handlers. I wanted one of the transformation inside the dataflow should skip the

16. What are the latest features in SSIS 2012?

17. How to use configurations in SSIS?

18. How to validate data in SSIS? Give some example.

5

Organization HCL
Position Sr. MSBI Developer
Location Bangalore, India

1. How to keep header in all pages – SSRS?
2. How to add custom code to change the colour of a node in report – SSRS
3. I have some .csv files, how to load them into SQL Server explain step by step.
4. How to send a report as an attachment to an email and HTML report should not be added.
5. What is the difference between OLEDB – Provider , ADO.NET and SQL Server destination?

6
​

Organization Liquidhub
Position MSBI Consultant
Location Hyderabad, India

​​1. What is a hash join in execution plan?

​2. What is a sub report?

3. Difference between drill through and sub reports?

3.​What is a shared dataset?

4. Can we use shared dataset with the subreport?
5. What is the deployment mechanism for ​​SSRS deployment?

6. What are the common performance issues in SSRS?
​7. ​Can you tell me top 5 new features in SSIS and SSRS ​Both in 2008 and 2012?​

7
​​​

Organization BARCLAYS
Position Sr.MSBI Developer
Location Singapore

1. What kind of dashboards you have prepared using SSRS reports?

​2. Have you ever tried logging in SSIS? What are the tables on which log information stored? Can we enable logging for only selected items from SSIS package? if yes how?
3. Design a package to load .xlsx files from a folder. File names must include either “Finance_” or “Local_” and load files only from the last week.

​​4. How to capture column name and row number when something failed in dataflow?
5. Design a SSIS package to load a list of excel files. Once a file is loaded that file should be moved / archived to new location.
​6. Write a query to retrieve all employee hierarchy in an organization?
7. Design a SSRS report that can support dynamic columns.

8. Why should we use CTE?

9. How to use partition tables?

10. What is ETL, why specially SSIS?

11. What’s the max parallel value to SSIS?

12. See I have written a recursive CTE, it’s been running infinitely and failed with an error. The requirement is that the recursion required for 45000 times. How could you be able to handle the situation?

 

 

8

Organization Franklin Templeton
Position Sr.Associate – MSBI
Location Hyderabad, India

​Q. SSIS 2008 uses all available RAM, and after package completes Memory is not released. Have you ever faced these issues if yes can you explain how you resolved it?

​Q. Can you give some examples for de-generated dimension?

Q. We have a requirement like this. For every 10 days there is a data load happens in our database using a SSIS package. My requirement is once the ETL finished successfully then a SSRS report has to be emailed to the corresponding group of members. How can you do that?

Q. How to remove PDF from the export options in SSRS report?

Q. What are the different approaches to deploying SSIS packages in development, staging and production environments?

Q. Did you remember any error codes which are occurred while executing SSIS package?

Q. I have executed an SSIS package, usually it takes 3 hours but this time the package has taken 7 hours and the execution is still in progress. Can you describe what all the various approaches to troubleshoot this issue?

 

9

Organization Cognizant
Position Sr.Associate – MSBI + T-SQL
Location Hyderabad, India

​

1. Do you know about BI Symantec Model (BISM)?

2. Have you experienced filter index partitions
3.What is the difference between sub query and correlated query
4. What is the difference between pessimistic locking and optimistic locking?
5. What are the user roles available in SSRS?
​6. Can you write a query to find nth highest salary of an employee?

7. Write a query to delete duplicate records from a table. Note: Write queries that suits the below situation. A table is having a unique key and having duplicates records, table is not having a unique and having duplicate records.

8. What is XACT_ABORT ON?
9. How to filter nested stored procedure code from profiler?

10. How to find the number of transactions in the current scope?

11. What happens when a rollback happens in inside a nested stored procedure?
12. Example for degenerated dimension?

13. What is a bitmap index?
​14. How to avoid bookmark lookup in execution plan?
​15. ​How to avoid sort operation in SSIS?

16. ​When index scan happens?

17. What are the data regions in SSRS?

10

​Organization Tech-Mahindra
Position Technology Lead – MSBI + T-SQL
Location Hyderabad, India

​1. What kind of join happens in lookup transformation?

2. Did you find any issues using Full cache mode?

3. Does temp tables and table variables both stored in tempdb?

4. Why cursors are so costly?

5. Have you ever used custom assembles in SSRS?

6. How to enhance SSRS functionality?

7. Can we move sql server logins and users using SSIS?

8. Which is the most critical ETL task you implemented using SSIS?

 

​11

Organization Datamatics
Position Consultant – MSBI + T-SQL
Location Bangalore, India

1. Can we call a procedure from a function?

2. Can we write DML inside a function?

3. How to design a report to show the alternative rows in a different colour?

4. Write a code to customize the SSRS report. Where the code has to be written?

5. In a SSRS report where to write custom code?

​6. How to troubleshoot SSRS report using ExecutionLog2?

7. Have you ever seen .rdl file? What are the different sections in .rdl file?

​

12

Organization Hitachi Consulting
Position Sr.SQL Developer
Location Hyderabad, India

​1. What is the best value for MAXDOP value?

2. Which is better “Left Outer” Or “NOT EXIST”? Explain.

3. How to find the statistics are outdated?

4. How to find the query running on a given SPID?

5. What is XACT_ABORT ON?

6. Can we use two CTE’s in a single select query?

7. What are the different join operators in SQL Server?

8. Can we use a table variable inside a nested stored procedure if the table variable created in parent stored procedure?

9. What are the limitations on “SET ROWCOUNT”?

10. While creating a “Include” columns on which basis we should consider main column and include columns?

11. How to find the last statistics update date?

13

Organization S&P Capital IQ
Position Sr. Database Engineer
Location Hyderabad, India

 

1. An indexed view is referring only one base table. Both view and table are having index defined on them. Which index would be utilized when a query executed against the table.

2. I have an indexed view, now base table data has been modified, does the modified data reflected in view automatically?

3. Does “TRUNCATE” DDL or DML command?

4. I have written a recursive CTE, it’s been running infinitely and failed with an error. The requirement is that the recursion required for 45000 times. How could you be able to handle the situation?

​14

Organization innRoad
Position Sr. SQL Server Developer
Location Hyderabad, India

1. What is index reorganization?

​2. How sql engine knows which index has to be used while dealing with indexed views?

3. How to prevent bad parameter sniffing? What exactly it means?
4. What dll file that handle the transaction logs in logshipping?

5. How to find all dependent objects of a table?
6. How to find the latency in replication?

7. What are the tracer tokens in replication?
8. How to remove articles with generating a new snapshot?

9. I am not able to select some of the articles from publishing list, what might be the reason?

10. ​What is DAC? How it exactly works?

11. On which port DAC runs on?​

12. ​Which agent will take care of database mirroring?​

15

Organization Tech Mahindra
Position SQL DBA
Location Hyderabad, India

 

1. Why can’t we take a backup of a database which is in standby or in recovery mode?
2. How to move a database which is acting as a publisher in transactional replication?
3. What is CROSS APPLY?
4. How to find and remove duplicates from a table which is having 10 million rows?
5. Which is better a CTE or a subquery? Why?
6. Can you tell me replication monitoring tables?
7. How to apply service packs on Active Active cluster?
8. Best practices in applying security patches
9. What is a log reader agent?
10. What is PULL and PUSH subscriptions?

16

Organization IBM
Position SQL DBA
Location Hyderabad, India

1. How to rebuild a master database? Ho to restore a master database?
2. Any alternative to triggers?
3. What are the counters to monitor CPU usage?
4. Top 5 performance tuning tools
5. What events need to be added to capture execution plan in sql profiller?
6. How to add memory to sql server 2005?
7. Which locks are held at the time of snapshot in log shipping?
8. What is the new lock escalation in sql 2008?
9. How to check the log file location for service pack 4 in sql server 2005?
10. What is a filtered index?

​17

Organization Virtusa
Position SQL DBA
Location Hyderabad, India

​1. On which basis merge replication works on?
​​2. How to give a user to run a sql agent job?​

3. Can we install SQL Server using a configure file?

4. What are the top performance counters to be monitor in Performance Monitor?

5. How do you know how much memory has been allocated to sql server using AWE?

​18

​

Organization Microsoft R&D
Position SQL Server Oops Lead
Location Hyderabad, India

1. What happens when a transaction runs on SQL server? Let’s say simple update statement “Update Table set col1 = value where col2 = value”
​​Ans:

​It issues an update lock and upgrades it to Exclusive Lock

The corresponding page would be captured from disk to memory

The modified page will be modified at Memory

The operation “Update *******” will be written to LDF.
Check point happens and the modified page will be written back to Disk and the operation at LDF marked as committed.

Lazy writer is responsible for cleaning the committed transactions from LDF.

2. What is fragmentation? How it happens?

3.  See we have a full backup on Sunday 8 PM, Diff and every day : 8 PM and log bkp on every 15 min. DB Crashed on Saturday afternoon 2:55 PM. How to rebuild the database? If suppose the last Sunday full backup is corrupted then how can you restore the database in current in time?

4. I have an instance on which there are databases in both FULL and SIMPLE recovery models. If I restart the sql service, what is the difference between these databases in recovering or what happens while restarting the services?

5. I have a log file which is of 250 GB. Log is full. We don’t have a disk space on any other drive for creating .ndf, auto growth is ON, and essentially there are no options to allocate new space for the file. What’s your action plan?

6. Can we do replication with mirroring? If yes what are the limitations?

7. Can we perform a tail log backup if .mdf file is corrupted?

8. Task manager is not showing the correct memory usage by SQL Server. How to identify the exact memory usage from SQL Server?

9. What is the option”Lock Pages in Memory”?

10. How to apply service pack on Active / Passive cluster on 2008 and 2012?

11. Can we configure log shipping in replicated database?

12. How to configure replication on cross domain instances?

13. Let’s say we have a situation. We are restoring a database from a full backup. The restore operation ran for 2 hours and failed with an error 9002 (Insufficient logspace). And the database went to suspect mode. How do you troubleshoot this issue?

14. Consider a situation where publisher database log file has been increasing and there there is just few MB available on disk. As an experienced professional how do you react to this situation? Remember no disk space available and also we can’t create a new log file on other drive

15. Can we add an article to the existing publication without generating a snapshot with all articles?

19

Organization Genpact
Position Lead SQL DBA
Location Hyderabad, India

1. Difference between 32 bit and 64 bit

2. How B-Tree formed for Clustered and non clustered indexes?

3. How B-Tree forms for indexes with included column?

4. Does alzebrizer tree stores in memory for stored procedures, views and constraints?

5. What is WOW and WOW64?

6. How to design TempDB files? And what is the limit?

7. What are the different queues of CPU?

8. Write a query to show what’s happening on instance with description?

9. How VLF’s created for tempDB?

10. When the checkpoint can happen? What it exactly do?

11. When the lazywriter happens and what it’ll do?
12. What is total server memory and target server memory?

13. What is memory grant in SQL Server?

14. Why resourceDB introduced?

15. How to move master database?

16. How to rebuild master database and what are the considerations?

17. What is a boot page?

18. What is fragmentation?
19. What is edition limitation in sql server 2008 for database mirroring with asynchronous mode?

20. Do you know why SQL Browser is?

21. How to upgrade SSRS reports?
22. How do you know that log shipping break down?

23. What is the difference between push and pull subscription?

 

20

Organization E&Y
Position Sr. SQL DBA
Location Kochi, India

1. Can we truncate a table which is participating in transactional replication?

2. How to identify log filling issue and can we issue shrink with truncate on publisher database?

3. How to filter the nested stored procedure or a command from profiler?

4. Can we add articles to the existing publication without initialization of the snapshot?

5. How MAXDOP impacts SQL Server?

6. How distributed transactions works in SQL Server?

7. Full back up size is 300 GB, usually my diff backup size varies between 300 MB and 5 GB, one day unfortunately diff backup size was increased to 250 GB? What might be the reason any idea?

8. What is .TUF file? What is the significance of the same? Any implications if the file is deleted?

21

Organization Infosys
Position SQL DBA
Location Mysore, India

1. ​Are tables locked during generating the snapshot?
2. Does Truncate Works in replication? What are the limitations of Truncate command?
​​3. ​​Causes for slow replication?

4. How to add articles to existing publication without generating a full snapshot?
5. Can we add only new articles to merger publication?

6. What is re-initialization in replication and how it works?
​7. What is the difference between lazy writer and checkpoint?
8. How to move master database?
9. What are the different shrink options?
10. Can you explain the log file architecture?

​22

Organization Factset
Position MSBI Developer
Location Hyderabad, India

1. What is the main difference between mirroring and always on?

2. What is Microsoft Best Practices Analyzer?

3. What is Microsoft Baseline Configuration Analyzer?

4. What are the different database patterns?

5. Any idea types of data warehouse?

6. What is SCD type – 2?

7. What is the difference between “Natural Key” and “Surrogate Key”?

8. What is a junk dimension? Give me an example

9. What is a Degenerate Dimension / Fact Dimension in SQL Server Analysis Services? In what scenarios do you use it?

 ​23

Organization Polaris
Position Technology Lead – DBA
Location Dubai, UAE

1. ​​What the issues you find in migrating databases
2. ​​What is the schedule frequency of your transactional replication?
​​​3. How do check the application compatibility with sql server?​

4. ​How do you know whether statistics are latest or expired?​

5. ​​How to run distributed transactions in sql server?  What are the config changes has to be made?

6. How to give linked server usage permissions to specified logins only?

7. What kind of information that SQL Server keeps in memory?

8. Customer asked to break the mirroring and failover to mirror database. What are the steps to be taken other than a manual failover?

9. Can you give some examples for One to One, One to Many and Many to Many relationships?

 

​24

Organization CA
Position Lead SQL DBA
Location Hyderabad, India

​1. How to find the tempdb contention?

2. What is the sparse file?

3. What is the redo file in log shipping?

4. What are the queues in sql server?

5. How to capture a trace from production without any impact on performance?

6. How to capture the long running queries?

7. What is the migration plan to move 300 databases to  a new data centre? We can have an hour downtime.

8. In mirroring a connection failure happened between principal and mirror and principal server started filling log space (send queue), how do you troubleshoot?

9. What are the phases a database needs to be gone through while restoring?

10. What is the recovery interval?

11. You have any idea on Table Partitions?

​25

Organization Microsoft GTSC
Position SQL DBA
Location Bangalore, India

1. ​We have a log shipping environment. New data file has been added at primary server, what happens and how do you resolve it?

2. See we have a view which is getting data from different tables. One day it’s starts executing infinitely. I have seen no blocking, no bulk operation happened. I have stopped all jobs and maintenance plans on the server. No one is connected to the database but still it’s been taking longer time. What might be the possible reasons?

3. You have got a request to execute a query which is an “Update” query. That update is updating 5 million rows, after an hour it’s still executing and you are getting lot of requests from report users that their things are getting slow down. What’s your action plan?

4. See I have an environment, Sunday night full backup, everyday night diff backup and every 45 min a transactional backup. Disaster happened at 2:30 PM on Saturday. You suddenly found that the last Sunday backup has been corrupted. What’s your recovery plan?

5. Full backup size is 300 GB, usually my diff backup size varies between 300 MB and 5 GB, one day unfortunately diff backup size was increased to 250 GB? What might be the reason any idea?

6. What is .TUF file? What is the significance of the same? Any implications if the file is deleted?

7. What are the ITIL basic standards? What are all the phases of ITIL? Explain about incident management, problem management, change management etc?

8. Can you explain sql server transaction log architecture?

9. What are the phases of sql server database restore process?​

​​26

Organization Pythian
Position Sr. SQL DBA
Location Hyderabad, India

1. How many IP’s required configuring a 2 node cluster?

2. How many MSDTC required for a 2 node cluster?

3. What is the basic difference between 2005 and 2008 in clustering?

4. What’s the use of quorum in clustering?

5. Your customer complained that one of the reports is not working, what is your action plan? Note: You just know the database server not anything else about that report.

6. How could you give full access on a database to a user from “Operations-Team”. Remember the user should have all rights except write permission. But our company policy is not to give DB_OWNER rights to any user from “Operations-Team”

​​7. I wanted to know what are the maximum worker threads setting and active worker thread count on sql server. Can you tell me how to capture this info? What’s the default value for max thread count?

8. What are the all possibilities that cause the tempdb full issue?

9. What is version store?

10. What is the best configuration for Tempdb?

11. We have a procedure which is running fine till today afternoon. Suddenly it started taking long time to execute and there of it leads to a timeout error from application. What might be happening? How you troubleshoot?

12. Do you have any idea about sparse column?

13. Have you ever done any automation in your database environment?

14. What are the critical issues you faced in your career?

15. Did you ever handle any missed SLA’s?

16. How to change the port number for SQL Server?

17. Any idea about capacity planning?

 

27

Organization DST Worldwide Systems
Position MSBI Lead
Location Hyderabad, India

1. How to give estimations to your report requirements?

2. Difference between ISNULL and COAELSCE

3. What is the tough SSIS package you handled?

4. What is the difference between BCP and Bulk insert?
5. On which basis we can give the deadline for a given task?

6. I have a requirement: We need to load a bunch of files on every Thursday at 4:00 AM EST and once the load is completed successfully a pre designed SSRS report has to be triggered and it has to be mailed to the given recipients. Design a ETL package using SSIS

 

28

Organization Yash Technologies
Position MSBI Lead
Location Hyderabad, India

1. Write a query to capture all employee details with salary less than the average salary within each department.

2. I have a table employee in that I have a column empID which is of varchar type. The column may have combination of characters and integers. Now write a query to get all records in which empID are having integers. Exclude rows with EMPID is not having integer values in it.

3. We are doing a ETL operations using stored procedures. Write a procedure to cleansing data in staging table and insert the data into master tables. Remember in staging table all columns are of varchar type

4. What is database unit testing? How to perform unit test for a stored procedure and SSIS package?

5. Assume we need to design a SSIS package to load data from a .csv file to database. In .csv file there are 10 columns. For the first 500 rows data is available for all 10 columns and for the last 400 rows data is available for first 6 columns, not even comma available after 6th column. How do you handle this?

.csv file:

col1, col2, col3, col4, col5

‘dat’, ‘dat’, ‘dat’, ‘dat’, ‘dat’,
‘dat’, ‘dat’, ‘dat’, ‘dat’, ‘dat’,
‘dat’, ‘dat’, ‘dat’, ‘dat’, ‘dat’,
‘dat’, ‘dat’, ‘dat’
‘dat’, ‘dat’, ‘dat’
‘dat’, ‘dat’, ‘dat’

6. What are the different types of storage models in OLAP?

7. What is a mini dimension?

8. Design a SSRS report that can support dynamic columns.

9. How to remove PDF option from export options in SSRS report?

10. How to design a SSRS report that shows alternative rows in different colour?

29

Organization L&T Infotech
Position Sr. MSBI Developer
Location Bangalore, India

1. What is connection pooling in sql server?

2. Difference between partition by and patindex

3. What are the database design patterns?

4. What are the storage models in OLAP?

5. What is the difference between CROSS / OUTER APPLY AND JOINS in T-SQL?

6. When to use CROSS APPLY over Join?

7. Do you have any idea about table partitions?

8. How to attach configuration files to SSIS package?

9. What is stuff function? Difference between stuff and replace?

10. What is sparse column?

11. We have a query which is running fine in development but facing performance issues at production. You got the execution plan from production DBA. Now you need to compare with the development execution plan. What is your approach?

12. How to manually allocate memory to SSRS service?

13. How to view report server logs and call stacks in SSRS?

14. We know in SSRS by default datasets are executed in parallel. But in our environment datasource is under high load and can’t handle parallel requests. Now can you explain how to disable parallel processing or how to serialize dataset execution?

15. How to pass multi-valued parameter to stored procedure in dataset?

16. How to control the pagination in SSRS?

17. What are the major differences between SSRS 2008 and SSRS 2012?

18. You have any idea about IIF, SWITCH and LOOKUP functions in SSRS?

19. How to get “Total” values at the end of every group in a report?

20. A SSIS package failed how to know the exact row number at which the package failed to process?

 

Posted in High Availability, Interview Q&A, MSBI, Performance Tuning, SQL Development, SQL Server DBA, SSIS, SSRS | Tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , | 68 Comments

Upgrading SQL Server Questions and Answers

Posted on October 3, 2014 by uday arumilli

Q&A

Upgrading SQL Server Questions and Answers

SqlServer2008_Logo.png

logo_sql_server_2nd.gif

sql2000.jpg

 

 

 

 

1. Can you upgrade SQL Server 2008 SP2 Standard Edition to 2008 R2 Developer Edition?

Ans:

You can’t change the version of the installed instance as far as I know, but you could install a second instance with the Dev edition, or uninstall the Standard edition, install Developer edition and attach the user databases.

2. Does upgrade advisor analyze the remote instances?

Ans:

Upgrade Advisor can analyze remote instances of SQL Server, except for SQL Server Reporting Services. To analyze Reporting Services, Upgrade Advisor must be installed and executed on the report server.

3. Ho to upgrade a SQL Server 2000 to SQL Server 2008?

Ans:

That said, what kind of upgrade are you doing? Are you planning an in-place or side-by-side upgrade? The different approaches will result in different checklists. The safest approach is the side-by-side upgrade. You can do this either by using a backup and restore or dettach/attach of the database files. I’d suggest using the backup & restore as the safer approach. Here are the things I’d do:

  • Run Upgrade Analysis tool from Microsoft. Address any issues raised there, first.
  • Identify DTS packages. These must be migrated by hand, unless you buy PragmaticWorks excellent software. Rebuild the DTS packages as SSIS.
  • Script out all SQL Agent jobs.
  • Script out all security
  • Backup the systems and validate the backups (preferably by restoring them to another system)
  • Run the security script on the new system
  • Run the restore on the new system.
  • Validate the databases by running DBCC
  • Manually update all statistics
  • Run the SQL Agent script

4. Have you ever prepared a checklist while performing an upgrade?

Ans:

Yes Of course!!!

  • Identify which databases need to be upgraded (ie are still on older versions of SQL Server)
  • Of those databases, which are not supported on more recent versions of SQL Server?  This is one for the appropriate vendor – so do we have contact details for the vendor?
  • Of those non-supported databases, if this is just because the vendor hasn’t tried it, are we able to try and will they support is in our efforts?
  • Identify maintenance routines and scheduled jobs associated with database
  • Identify dependencies upon older technologies (eg DTS rather than SSIS), and work out an upgrade path for these
  • What applications depend upon the database?
  • What UserIDs are required?
  • How do we configure the application to point to a new database?
  • What else needs to be changed?  (eg middleware servers)
  • Are parts of the database subject to Replication?
  • Is the database part of a Log Shipping routine?
  • Is the database part of a Mirror set?
  • What’s the recovery plan for the database?
  • What’s the backup plan for the database?
  • Are there any SSRS jobs relating to this database?
    • What are they?
    • Where are they?
    • How do we migrate these across?
    • What else depends upon those reports?
  • …and similarly, are there any OLAP / SSAS dependencies?

It might also be worth thinking about the amount of data in the database:

  • How much data have we got?
  • How fast is the database growing?
  • For how long do we need to retain this data?
  • Can we archive anything off to improve performance?

Of course, all the above forms part of your database documentation, so it should be easily to hand, right?

The other things to work out include:

  • How do we test this thing to ensure the migration is successful?
  • How do we rollback if it isn’t successful?
  • Point of contact for the supplier / vendor / development team
  • Point of contact for the customer(s) / user(s)

5. Can you detach a SQL Server 2005 database and attach it to a SQL Server 2008 server?

Ans:

Yes. SQL Server 2005 databases are compatible with SQL Server 2008. However, that attaching a SQL Server 2005 database to SQL Server 2008 automatically upgrades the SQL Server 2005 database to a SQL Server 2008 database and the database is then no longer usable by the SQL Server 2005 installation.

6. Can you detach a SQL Server 2008 database and attach it to a SQL Server 2005 server?
Ans:

No. The only way to move a SQL Server 2008 database to a SQL Server 2005 server is by transferring the data using a method such as Data Transformation Services (Import/Export),SSIS, bcp, or use of a query between linked servers.

7. How long will it take to upgrade my SQL Server databases?

Ans:

Many factors affect the amount of time needed to upgrade SQL Server Databases. Depending on the complexity of each database, Size of databases, the hardware platform, number of processors, disk subsystem, and amount of RAM plays a significant part in the amount of time required for the upgrade. Selecting “data validation” during the setup increases the amount of time needed to perform the upgrade by a factor of two. Some typical times for the upgrade process are:

Size of Database Estimated Time Required to Upgrade
400 MB Less than 20 minutes.
   1 GB Less than 1 hour.
 10 GB Less than 4 hours.
 50 GB Less than 12 hours.
100 GB Less than 24 hours.

8. When you upgrade a SQL Server, the upgrade wizard seems to stop responding and fails. Why?

Ans:

If applications or services have open ODBC connections to the SQL Server 2005 server during the conversion process, they may not allow the SQL Server to shut down completely. The conversion process will not proceed on to the next step if it does not receive verification that the SQL Server has been completely stopped.

9. “I’m trying to restore a 25 GB database backup taken from a Windows 2003/SQL 2005 machine to a Windows 2008/SQL 2008 machine in the Amazon EC2 cloud, using a .bak file and the SQL Management Studio. SQL Management Studio reports the restore reaches 100% complete, and then just hangs indefinitely (24+ hours) using a lot of CPU, until I restart the SQL Server service. Upon restart, SQL again uses a lot of CPU activity for what seems to be an indefinite amount of time, but the DB never comes online.”

Ans:

The database is in the process of being upgraded from SQL 2005 to SQL 2008 when you kill it. Check the ERRORLOG in SQL Server and you should see that the database restore is complete and that the database is being upgraded.

This process is normally very quick, but it can take a while to perform depending on the database, especially if you have a lot of pending transactions in the database which much be rolled forward or backward before the database can be upgraded.

9. How to rollback the upgrade?

Ans:

If the legacy SQL Server instance is replaced by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complex and time-consuming, whereas in a side-by-side upgrade the legacy instance remains available if a rollback is needed.

10. What are the different ways in upgrading to a higher version?

Ans:

There are two ways:

In-Place Upgrade: Installs 2008 and overwrite on the 2005 Server.

Side-by-side Upgrade: A new instance will be installed and moves the databases.

11. Give some examples when we consider Side-by-side and In-Place?

Ans:

Pros & Cons: In-Place

Pros

  • Easier, mostly automated
  • Generally fast overall process
  • Requires no additional hardware
  • Applications remain pointing to same server/database name

Cons

  • Less granular control over upgrade process
  • Instance remains offline during part of upgrade
  • Not best practice for all components
  • Complex rollback strategy
  • Not recommended for SSAS

Pros & Cons: Side-by-side:

Pros

  • More granular control over the upgrade process
  • Original database left unchanged; allows for testing of new database
  • Single occurrence of database downtime
  • Relatively straightforward rollback strategy

Cons:

  • Usually require additional hardware
  • Server/database name changes
  • Not practical for VLDB unless utilizing SAN(Beware of “loss of quick roll-back)

12 What are the parameters should be considered while choosing the upgrade process?

Ans:

Components: A certain upgrade strategy might not be possible because the component does not support it. For example, there is no in-place upgrade for SSIS from SQL Server 2000; Microsoft recommends that you upgrade most SQL Server 2000 SSAS components.

Versions and Editions: The in-place upgrade strategy does not support all paths between versions and editions. For example, to upgrade a SQL Server 2000 Enterprise Edition instance to SQL Server 2008 Standard Edition, you must perform a side-by-side upgrade because SQL Server Setup does not support an in-place upgrade path.

Partial upgrading: To transition only a few databases on a server to SQL Server 2008 and leave the rest on the legacy version, you must use a side-by-side upgrade.

Upgrading over time: To transition databases gradually, a few databases at a time, from a legacy instance to SQL Server 2008, you can only use a side-by-side upgrade.

Effect on applications: If your organization requires minimal disturbance to the existing applications and users, you may want to choose an in-place upgrade if possible.

Availability: Both an in-place upgrade and a side-by-side upgrade require that the databases be unavailable for a certain amount of time. The amount of downtime required depends primarily on the size of the data sets. At first, it might seem that an in-place upgrade would be faster than a side-by-side upgrade because the data is not transferred from one server to another. However, an in-place upgrade also requires time for the installation of SQL Server 2008. In a side-by-side upgrade, SQL Server 2008 is already installed on another instance. If the data transfer proceeds quickly and few changes are needed on the new instance, a side-by-side upgrade might be faster than an in-place upgrade.

13. We have upgraded databases from SQL Server 2000 to SQL Server 2008 and now the upgrade hits the production. Unfortunately a part of application is not supporting SQL Server 2008. Do we need to Rollback entire process to SQL 2000? Is that the only solution? If it is the only way! Since the databases at production transactions are being running and the data has been updated. Please assist us.

Ans

However, after the upgraded SQL Server 2008 instance goes into production and starts capturing new data, there will come a point in time when enough new data has been captured that a rollback is no longer realistic. For an in-place upgrade, if you encounter problems after the system is in production, making adjustments or “patches” to the new application would be a better option than attempting a rollback. For a side-by-side upgrade, you could employ SSIS to transfer new data from the SQL Server 2008 instance to the legacy SQL Server 2000 to bring it current. Depending on the complexity of the data, this could be a difficult process.

14. Can you list out some of the compatibility options while upgrading?

Ans:

For example we goanna upgrade SQL 2000 to SQL 2008:

  • Some features do not exist anymore
  • Examples: undocumented system stored procedures, DUMP, LOAD, sp_addalias,
  • Some are deprecated, Will be removed in future versions of SQL Server
  • Examples: SQL Mail, COMPUTE BY, Remote Servers, backup passwords,…
  • Some features behave differently
  • Example: Access to catalog views (new security on system views)
  • Some editions have different features
  • Example: Express has no SQL Server Agent

 

15. What are the different tools available while upgrading from SQL 2000 to SQL 2008?

Ans:

Primary Tools:

  • SQL Server 2008 Upgrade Advisor
  • DTS xChange

Secondary Tools

  • Microsoft® Assessment and Planning Toolkit 3.2
  • SQL Server 2008 Upgrade Assistant
  • SQL Server Best Practices Analyzer
  • System Configuration Checker
  • SQL Server Profiler
  • SQL Server: Deprecated Features Object Counter
  • Other tools

16. Error while upgrading to SQL server 2005 from Sql server 2000.

“while upgrading my sqlserver 2000 with version 8.0.2039 to SQLserver 2005, msxml6.msi failed to upgrade and while trying to uninstall it is try to look the msi file which missing from the path”

Ans:

MSXML 6 services installed in my server is a lower version of SP2 msxml6-KB954459 and not allowing the upgrade, because setup trying to upgrade MSXML6.MSI and it could not locate the file in the previous installation and Of course it got missed.

So, we have worked out here to download the same version from Microsoft website and then extracted the MSXML6.MSI file to some location and then we tried to un-install the MSXMl service and it went successfully.

Later, again we have installed the msxml6-KB954459-enu-x86.exe and upgrade of SQLserver 2000 to 2005 went smooth without issues and applied latest servicepack 4 successfully.

17. How to Upgrade to SQL SERVER 2005?

Ans:

  • We can directly upgrade instances of SQL Server 2000 Service Pack 3 (SP3) or later, and instances of SQL Server 7.0 SP4 or later, to SQL Server 2005. We can perform most upgrade operations through Setup
  • Before running Setup to upgrade to SQL Server 2005, we should first review system requirements and update Matrix
  • Before beginning an upgrade
  • All SQL Server database files will have to be backed up.
  • Appropriate DBCC commands should be run to ensure consistent state.
  • SQL Server System databases will have to be configured with autogrow setting to ensure that they will have adequate disk space.
  • All startup procedures will have to be disabled; else they will block the process of upgrading.
  • Replication log should be emptied and Replication will have to be stopped.
  • Another factor that needs to be taken into account while preparing for an upgrade, are the features that have been deprecated in SQL Server 2005.
  • Once all the above has been done, SQL Server 2005 setup has to be run and the instance installed.
  • The upgrade Advisor does not get installed automatically when SQL Server is installed. It has to be installed separately.
  • After reviewing system requirements and upgrade Matrix, run SQL Server Upgrade Advisor to analyze the instances of SQL Server 2000 and SQL Server 7.0.
  • Upgrade Advisor produces lists of issues that we must fix before or after upgrading. SQL Server Setup will detect blocking issues that will prevent us from upgrading to SQL Server 2005 (The table alias should not be used in order by clause)
  • Thereafter, move user databases to the instance by using backup and restore or detach and attach functionalities in SQL Server 2005. Then register the server, repopulate full text catalogs, update the statistics and run Surface Area Configuration tool. Change the compatibility level to 90 from 80

 

18. What are the issues u faced in sql server upgrade?

Ans:

Common causes

  • SQL Server or the machine running the upgrade loses its network connection.
  • The database in which you were working has run out of log or data space.
  • You are not allowed to perform an update to a table.
  • The database is corrupted.
  • The database is not available (still in recovery.) It may be unavailable if the upgrade program begins to work before SQL Server finishes performing recovery after startup.
  • Unable to restart the server

Can identify the issue from the upgrade log files and resolve the issues and rerun the upgrade advisor

19. What is the sequence to install service packs or hotfixes on an instance of SQL Server that is part of Log Shipping/Database Mirroring/Replication/Failover Clustering environment?

Ans:

When an instance of SQL Server is configured as part of Log Shipping, Database Mirroring, Replication, or Failover Clustering environment, it is important to install service packs or hotfixes in a correct sequence otherwise we may get unexpected issues.

Log Shipping:

There is no required sequence to apply a service pack or hotfix for Primary, Secondary and Monitor servers in a Log Shipping environment. The following is my preferable to apply service pack or hotfix:

  • Apply the service pack or hotfix on the Monitor server.
  • Apply the service pack or hotfix on the all Secondary servers.
  • Apply the service pack or hotfix on the Primary server.

Database Mirroring:

If you install service packs or hotfixes on servers in a database mirroring environment, you need to determine the role of the servers. If there are many mirroring sessions configured on the server, you need to determine all possible roles that could be. For instance, if the server is acting as a mirror server for any database mirroring session, update the server as the mirror role for all mirroring sessions. To do this, follow these steps:

  • If a witness server is configured in the database mirroring session, disable the automatic failover during the update process. To do this, remove the witness server from the mirroring session.
  • If the safety level of the database mirroring session is OFF (asynchronous mode), change the safety level to FULL (this is required in step 3).
  • Make sure all database mirroring sessions to be in Synchronous mode and synchronized.
  • Pause the database mirroring sessions that are present on the server.
  • Install the service pack or hotfix on the mirror server.
  • Resume the database mirroring sessions.
  • Perform manual failover (all the mirroring sessions on this principal server) to the mirror server so that mirroring server assumes the principal role.
  • Pause the database mirroring sessions as step 4.
  • Install the service pack or hotfix on the new mirror server (previous principal server).
  • Resume the database mirroring sessions.
  • If you changed the safety level in step 2, change the safety level back to OFF.
  • If the database mirroring session has a witness server, undo the changes made in step 1.

Replication:

In a replication environment, there is no preferable sequence to apply service pack or hotfix for non-bidirectional replication typology. However, for bi-directional replication typology such as merge typology or transactional replication with updateable subscriptions, you must upgrade Distributor, Publisher, and Subscribers in the following order:

  • Apply the service pack or hotfix on the Distributor server.
  • Apply the service pack or hotfix on the Publisher server.
  • Apply the service pack or hotfix on the Subscriber server.

 Failover Clustering:

In SQL Server 2005, if you want to install a service pack or hotfix, you must install the setup on the Active node (node that currently runs SQL Server services). When running the setup which will launch simultaneously “remote silence” on all passive nodes.

However, in SQL Server 2008 and SQL Server 2008 R2, the service pack or hotfix deployment is changed to reduce the downtime. Now, you must install the service pack or hotfix on the passive node first. To do this, following these steps:

  • Apply the service pack or hotfix on the passive node (or all passive nodes if you have more than one).
  • Reboot the passive node.
  • Failover the SQL Server failover cluster to the passive node (and the passive node becomes active now).
  • Apply the service pack or hotfix on the new passive node (previous active node).
  • Reboot the passive node.
Posted in High Availability, SQL Server DBA | Tagged , , , , , , , | 16 Comments