Google
 

Friday, November 2, 2007

Using Solutions and Projects to manage your SQL Server code

When developing code for a project there is often more than one component that you need to work with for the entire project. The project may include new tables, table changes, new stored procedures, changes to stored procedures, etc... Keeping all of these components straight as well as logically grouped together is sometimes a challenge in itself. Are there are any built-in tools that allow you better manage project components vs. one big file?

Solution
In SQL Server Management Studio a new component has been added that allows you to logically group your project code together in one place.

To access this you need to create a new project which you can do one of two ways:

  • File -> New -> Project or (Ctrl + Shift + N)

When you create a new project you have several choices such as:

  • SQL Server Scripts
  • Analysis Services Scripts
  • SQL Server Compact Edition Scripts

At this point you need to select the type of project and a new solution will be created for that project.

After you create the new project the following screen will appear in Management Studio. At this point you can begin creating connections, queries and any other miscellaneous files that you need for this project. In addition, you can have multiple projects within one solution.

The following gives you an idea of what a solution could look like with multiple projects and multiple components within each project.

This is a pretty simplistic concept of keeping your code together, but for complex projects where there are a lot of components this could become a big help for keeping your project code straight.

SMTP Server

Provider SMTP Server
12 012.net.il
191 mail.191.biz
191.it mail.191.it
Access4Less smtp.access4less.net
Active Network smtp.activenetwork.it
Actrix Networks mail.actrix.co.nz
Adelphia mail.adelphia.net
akfree smtp.akfree.it
Albacom relay.albacom.net
Albacom smtp.albacom.net
albacom smpt.albacom.net
alcotek smtp.alcotek.it
alice out.aliceposta.it
aliceposta.it mail.tin.it
alise outmail.f2s.com
AOL smtp.aol.com
Arnet smtp.arnet.com.ar
Aruba smtp.aruba.it
AT & T Wireless smtp.attwireless.net
AT & T Worldnet imailhost.worldnet.att.net
atlanet smtp.atlavia.it
atlanet smtp.weblinea.it
Auna smtp.auna.com
Bahrain Telecommunications Company batelco.com.bh
Barak I.T.C mail.barak.net.il
basilicatanet.it mail.basilicatanet.it
bella.ci bella.ci
Bellsouth mail.bellsouth.net
Bezeq International mail.bezeqint.net
Bezeqint mail.bezeqint.net
Bitstop pangasinan.com
Blu.it smtp.blu.it
Bluebottle mail.bluebottle.com
bluelight.com smtp.mybluelight.com
Bluewin mail.bluewin.ch
BlueYonder smtp.blueyonder.co.uk
bol.com.br smtp.bol.com.br
BRTURBO smtp.brturbo.com.br
BT Internet mail.btinternet.com
BT Openworld mail.btopenworld.com
BTClick smtp.btclick.com
BTTB mail.bttb.net.bd
BusinessServe smtp.businessserve.co.uk
Cable One mail.cableone.net
Cableinet smtp.blueyonder.co.uk
Caiway smtp.caiway.nl
Callsouth - Broadband smtp2.callsouth.net.nz
Callsouth - Dial up smtp.callsouth.net.nz
Cantv.net mail.cantv.net
cegetel smtp.cegetel.net
Chariot Netconnect mail.vic.chariot.net.au
charter pop.charter.net
Charter mail.charter.net
cheapnet smtp.cheapnet.it
chello.pl mail.chello.pl
ciaoweb ciaosmtp.ciaoweb.it
Cingular smtp.mymmode.com
Ciudad smtp.ciudad.com.ar
Claranet relay.clara.net
Clear Net smtp.clear.net.nz
click21 smtp.click21.com.br
Club-Internet smtp.club-internet.fr
collegeclub collegeclub.com
Colt.net (Germany) smtp.ipmail.colt.net
Columbia Power and Water mail.cpws.net
Comcast smtp.comcast.net
Cox - Central smtp.central.cox.net
Cox - East smtp.east.cox.net
Cox - West smtp.west.cox.net
Cultura smtp.cultura.com.br
CWCom smtp.ntlworld.com
Datamat mail.datamat.it
Demon post.demon.co.uk
Digitel Italia smtp.etmail.it
Dinajpur www.dinajpur.biz
Dream Net Internet mail.dreamnet.co.nz
Earth Link smtpauth.earthlink.net
Easynet smtp.easynet.co.uk
eircom.net mail2.eircom.net
Elitel smtp.elitel.biz
Email.it smtp.email.it
Euronet NL smtp.euronet.nl
everybodycanadd itsverybad
Excite smtp.tiscali.it
EzySurf smtp.ezysurf.co.nz
FastMail mail.messagingengine.com
Fastweb smtp.fastweb.it
fastweb pop.fastwebnet.it
Fastweb mailbus.fastweb.it
Fastweb smtp.fastwebnet.it
fibertel.com.ar smtp.fibertel.com.ar
Free smtp.free.fr
Free Telecom smtp.free.fr
Freedom2surf outmail.f2s.com
Freemail smtp.freemail.it
freemail.it mail.freemail.it
freemail.it (supereva) mail.freemail.it
freenet mx.freenet.de
freenet.de mx.freenet.de
Freeserve smtp.freeserve.co.uk
Galactica.it smtp.galactica.it
Genie mail.genie.co.uk
GIGA smtp.giga.net.tw
Globe Net Communications smtp.globe.net.nz
gmail smtp.gmail.com
GMX mail.gmx.net
Go Daddy smtpout.secureserver.net
go.com smtp.go.com
Haier Electronics smtp.haier-electronics.com
Hinet msa.hinet.net
HiNet ms1.hinet.net
hotmail mx2.hotmail.com
HOTMAIL hotmail.co.uk
Hotmail.com mx1.hotmail.com
Hotmail.com mx1.hotmail.com
Hotmail.com mx2.hotmail.com
HotPOP.com smtp.hotpop.com
I4U Internet Services mail.i4u.net.nz
ic24 smtp.ic24.net
IG smtp.ig.com.br
IHUG smtp.ihug.co.nz
INET inet.it
Infinito mail.infinito.it
InfoStructure -- GRR Technology smtp.grrtech.com
InfoStructure -- InfoStructure smtp.mind.net
InfoStructure -- Klamath Falls Internet smtp.kfalls.net
InfoStructure -- Medford Internet smtp.medford.net
InsightBB mail.insightbb.com
Interbusiness (TI Easynet) mail1.cs.interbusiness.it
INTERFREE mail.interfree.it
Internet Zahav sout.zahav.net.il
internetlibero smtp.internetlibero.it
inWind mail.inwind.it
IOL mail.iol.it
Iomart smtp.domain.ext
IPrimus Australia smtp.iprimus.com.au
Iprolink smtp.iprolink.co.nz
istruzione.it istruzione.it
ItalyMAIL mail.italymail.biz
ixpres.com smtp.ixpres.com
jumpy mail.jumpy.it
Juno smtp.juno.com
Katamail smtp.katamail.com
Kataweb smtp.katamail.com
la poste smtp.laposte.net
Le neuf smtp.neuf.fr
Libero mail.libero.it
Lillinet smtp.weblinea.it
Lineone smtp.lineone.net
Lycos smtp.lycos.co.uk
lycos.it smtp.lycos.it
Mac.com smtp.mac.com
Madasafish smtp.madasafish.com
mail.quizil.net mail.quizil.net
mail.ru smtp.mail.ru
Mailsnare mail.mailsnare.net
Maktoob Mail mira0.maktoob.com
McLink mail.mclink.it
Mediacom mail.mchsi.com
Mistral smtp.mistral.co.uk
Momax smtp.momax.it
ms15.hinet.net ms15.hinet.net
msoft.it smtp.weblinea.it
mtel mail.mtel.net
NamesToday smtp.namestoday.ws
NamesToday smtp.namestoday.ws
nerim.net astralblue@nerim.net
netcabo smtp.netcabo.pt
Netexplora Chile mail.netexplora.com
Netlink mail.netlink.co.nz
netscape smtp.isp.netscape.com
Netscapeonline mailhost.netscapeonline.co.uk
netvigator mail.netvigator.com
Netvisão (Portugal) mail.netvisao.pt
Netvisão (Portugal) mail.netvisao.pt
NetZero.com smtp.netzero.com
neuf telecom smtp.neuf.fr
NGI smtp.ngi.it
Nildram smtp.nildram.co.uk
Noos smtp.noos.fr
novis (portugal) mail.novis.pt
ntl (uk) smtp.ntlworld.com
NTLWorld smtp.ntlworld.com
OneTel mail.onetel.net.uk
ono wanadoo.fr
oNo smtp.ono.com
Optonline (Cablevision's Optimum Online) mail.optonline.net
Orcon mail.orcon.net.nz
outgoing.verizon.net tchrshelli
P.C.T.S. ns.pcts.sk
Paradise Net smtp.paradise.net.nz
PCHome smtp.pchome.com.tw
Pipex smtp.dsl.pipex.com
Pipex smtp.dial.pipex.com
pixius smtp.citilink.com
Post Man mail.postman.net
Poste.it relay.poste.it
postino.it smtp.postino.it
Previdencia kiwi.previdencia.gov.br
prodigy xasa.com
Prodigy(TELMEX)(México) smtp.prodigy.net.mx
promo.it smtp.promo.it
Purplenet smtp.purplenet.co.uk
Quipo quipo.it
R (cable Galicia) smtp.mundo-r.com
Radio Deejay Mail smtp.deejaymail.it
RCP (PERU) amauta.rcp.net.pe
Reteitaly smtp.reteitaly.com
Rogers smtp.broadband.rogers.com
RunBox smtp.runbox.com
Sapo (Portugal) mx.sapo.pt
SBC Global (Yahoo Powered) smtp.wans.yahoo.com
SBC Global (Yahoo Powered) smtp.swbell.yahoo.com
SBC Global (Yahoo Powered) smtp.ameritech.yahoo.com
SBC Global (Yahoo Powered) smtp.flash.yahoo.com
SBC Global (Yahoo Powered) smtp.nvbell.yahoo.com
SBC Global (Yahoo Powered) smtp.pacbell.yahoo.com
SBC Global (Yahoo Powered) smtp.prodigy.yahoo.com
SBC Global (Yahoo Powered) smtp.sbcglobal.yahoo.com
SBC Global (Yahoo Powered) smtp.snet.yahoo.com
SBC Yahoo DSL smtp.sbcglobal.yahoo.com
sbcyahoo.dsl smtp.sbcglobal.yahoo.com
Scarlet or Scarlet.be smtp.scarlet.be
Screaming.Net smtp.tiscali.co.uk
SFR (French mobile telephone) smtp-auth.sfr.fr
Shylex Telecomunicaciones smtp.shylex.net
SiFree.it smtp.simail.it
sify.com mail.satyam.net.in
skynet relay.skynet.be
Skynet.be relay.skynet.be
Slingshot smtp.slingshot.co.nz
softhome.net mail.softhome.net
SouthNet smtp.southnet.co.nz
Southwestern Bell mail.swbell.net
SprintPCS smtp.sprintpcs.com
Spymac mail.spymac.com
StofaNet.dk mail1.stofanet.dk
sunrise (CH) smtp.sunrise.ch
Supanet smtp.supanet.com
supereva mail.supereva.it
SuperFree.it smtp.superfree.it
sympatico smtp1.sympatico.ca
Tag Comunicazioni 64.94.0.31
Tariffe.it smtp.tariffenet.it
TDC backup-mx.post.tele.dk
tele2.fr smtp.tele2.fr
tele2.it smtp.tele2.it
Tele2Internet virtual.everyday.com
telecom mail.cs.interbusiness.it
Telecom (Alice) mail.tin.it
Telecom ADSL (Business) smtp.191.it
Telecom ADSL (Business) mail.191.it
telecom italia 191.it
Telecom Smart mail.tuttopmi.it
Telecom Xtra smtp.xtra.co.nz
Teleconomy Internet mail.191.it
TELEDISNET.BE mail.teledisnet.be
telefonica smtp.telefonica.net
Telenet (belgium) uit.telenet.be
telenet(belgium) uit.telenet.be
telepac smtp.telepac.pt
Telepac ADSL (Portugal) smtp.telepac.pt
telewest smtp.blueyonder.co.uk
Telewest smtp.blueyonder.co.uk
telkom smpt.telkom.net
Telkomsa.net smtp.telkomsa.net
Telus smtp.telus.net
Telus.net mail.telus.net
Telvia.it smtp.telvia.it
Terra smtp.terra.es
Terra - BR smtp.sao.terra.com.br
Terra - BR - Recife smtp.rec.terra.com.br
Terra - España smtp.mailhost.terra.es
Terra - España mailhost..terra.es
Terra - España smtp.mailhost.terra.es
Tesconet mail.tesco.net
TIM.it mail.posta.tim.it
timenet ADSL smtp2.xdslnet.it
Tin.it out.virgilio.it
TIN.IT free (funziona con Alice) mail.clubnet.tin.it
Tiscali smtp.tiscali.it
Tiscali smtp.tiscali.co.uk
Tiscali.de smtp.tiscali.de
tiscali.es smtp.tiscali.es
Tiscali.nl smtp.tiscali.nl
Tnet mail.tnet.it
t-online mailto.t-online.de
Totalise mail.totalise.co.uk
tre smtp.tre.it
Tugamail mail.tugamail.com
TuttoGratis.it smtp.eutelia.it
Tvtel tvtel.pt
UKGateway smtp.ukgateway.net
unitedemailsystems smtp.unitedemailsystems.com
unitedemailsystems unitedemailsystems.com
UOL smtp.uol.com.br
UOL Sinectis Argentina relay.uolsinectis.com.ar
USA.net smtp.postoffice.net
utenti interbusiness telecom mail.cs.interbusiness.it
Utopia Systems smtp.utopiasystems.net
utu.fi smtp.utu.fi
V 21 smtp.v21.co.uk
Verizon DSL outgoing.verizon.net
videobank videobank.it
virgilio out.virgilio.it
Virgin smtp.virgin.net
Vispa mail.vispa.com
Vivacity pop.Vivacity.it
Vodafone.it smtpmail.vodafone.it
Waitrose smtpmail.waitrose.com
wanadoo smtp.wanadooadsl.net
wanadoo (France) smtp.wanadoo.fr
wanadoo España smtp.wanadoo.es
wanadoo.nl smtp.wanadoo.nl
Web.de smtp.web.de
Webmail.is smtp.emailsrvr.com
Which Online mail.which.net
wooow.it smtp.wooow.it
World-Net mail.world-net.co.nz
Worldonline smtp.tiscali.co.uk
www.gmail.com smtp.gmail.com
www.qos.net.il mail.qos.net.il
www.tol.it smtp-tol.it
www.tol.it smtp.tol.it
X-Privat mail.x-privat.org
XS4ALL smtp.xs4all.nl
xs4all.nl mail.xs4all.nl
Xtra smtp.xtra.co.nz
Ya.com smtp.ya.com
Ya.com ADSL smtp2.adsl.ya.com
yahoo yahoo.es
yahoo smtp.mail.yahoo.com.cn
Yahoo smtp.mail.yahoo.com
Yahoo (autentication needed) smtp.mail.yahoo.com
Yahoo Argentina smtp.mail.yahoo.com.ar
yahoo.co.uk smtp.mail.yahoo.co.uk
yahoo.com.tw stmp.mail.yahoo.com
yahoo.com.tw smtp.mail.yahoo.com.tw
Yahoo.de smtp.mail.yahoo.de
yahoo.es smtp.correo.yahoo.es
yahoo.it smtp.mail.yahoo.it
ZeelandNet mail.zeelandnet.nl
zero.ad.jp zero.ad.jp
Zonnet smtp.zonnet.nl

Monday, October 29, 2007

SQL Testing Options with SQL Server 2000 and 2005

Testing database applications for many organizations is a challenging task that is difficult to setup, automate and validate. Luckily, more time is getting allocated into development projects to conduct more rigorous testing. In this tip we will outline some of the realities to perform SQL Server 2000 and 2005 testing and opportunities to streamline the process.

Solution
SQL Server 2000 and 2005 offer a number of opportunities to perform application testing to include the following items:

People, Process and Technology

The reality is that people, processes and technology are needed to conduct proper testing. Taking any one item out of the equation will not result in the expected outcome. So be sure to impress upon your team that it is necessary to have an equal amount all of three components for success. It is difficult for a great piece of technology to solve a difficult problem without highly talented people and a comprehensive process.

SQL Server Instances

Both SQL Server 2000 and 2005 support a single default instance and 15 named instances on the same server. What this means is that with per CPU licensing up to 16 instances can exist on a single server without incurring any additional licensing costs. If your testing is encapsulated at a SQL Server instance level, then up to 16 versions of the code can exist on 1 server.

With SQL Server 2005, additional instance options are available for other portions of the application, i.e. Analysis Services.

SQL Server 2005 Express Edition

Another option for testing is leveraging the SQL Server 2005 Express Edition which is a free copy of the database engine and Reporting Services with limitations on the number of CPUs, memory usage, etc. This version offers a fully featured GUI and all of the bells and whistles. This means that this edition gives you the opportunity to perform functional testing of the application on multiple independent machines at a very reasonable cost.

Virtual Machines

If the testing incorporates SQL Server and other components installed in Windows, then multiple SQL Server instances may be helpful, but they are not the solution because DLL's may need to be redeployed for each test or build, limiting the capabilities for parallel testing. Leveraging the Express edition may be an option if many machines are available, but this too can quickly become unwieldy. One recommendation is to leverage Microsoft Virtual Server or VMWare as a means to have multiple virtual instances of Windows with SQL Server, IIS, your application, etc. all residing on 1 physical server. This results in the opportunity to have many independent and fully featured instances of Windows and with multiple code bases.

Synonyms

If you are familiar with other database platforms, then synonyms should be no stranger. With SQL Server 2005, Microsoft introduced synonyms as a means to have a single name of an object reference a different underlying object in another schema or database or even on another SQL Server.

*** NOTE *** - Synonyms are not available in SQL Server 2000.

Views

Since synonyms are not available in SQL Server 2000, one potentially viable option is to leverage VIEWS as a means to have a single name that can reference another object.

DDL Triggers

With many scripts underlying objects need to be created, altered or dropped. Depending on how the code is written and executed, it may be difficult to determine if these commands were successful or not. One way to fill this gap in SQL Server 2005, is by leveraging the new DDL triggers. These can be created to notify the team as the code completes and the objects are created, altered or dropped.

Friday, October 26, 2007

SQL Code Deployment Best Practices

Problem
I am the only SQL Server DBA at my organization and I feel like I am constantly pushing out code. I have Developers send me emails, I get help desk tickets, I have to go out to file shares and VSS to check for code that needs to get pushed out. I have been at a point where I am not able to get my other work done which is becoming frustrating. The other issue that I have is that I am unable to track the changes over time. With 4 different places that I need to check for code, this has become a nightmare that I have to get in control and soon. How should I go about doing this?

Solution
With the numerous code deployments on a daily or weekly basis there is not doubt that you are having an issue getting other work done. Deploying code can become a full time job if you let it become one. You are probably also facing a significant amount of fire fighting because you do not have a process in place and everything is ad-hoc.

Although there is no single answer to resolve this issue, in this tip we will outline options that can be leveraged to help you resolve your code deployment issues. These options include:

  • Schedule - First and foremost, you have to setup a schedule for the following items:
    • Code submittals
    • Code reviews
    • Code deployments
  • Managerial Support - The next big area that you need to tackle is managerial support. Take some time to record the amount of time you spend on a daily or weekly basis deploying code and how you plan to change the amount of time for code deployments and the initiatives you can address with your fee time. The key here is to think about benefits from your perspective and how you can benefit the organization. Another key area is how your change is going to impact the remainder of the organization. Most likely you will be brining order to chaos, so keep that in mind and be prepared for push back.
  • Communication - Have your management communicate the deployment plan to the development teams. You will probably get some push back when moving from an entirely ad-hoc schedule to a formalized schedule, but you need to stick to your schedule or negotiate a reasonable plan that works for the entire team. In a nutshell you do not want to stop progress, but do not want to constantly be facing chaos and firefighting.
  • Technology - To streamline the process consider a third party tool to package and deploy the code. Some of these tools may be
  • Just say "no" to the drive by requests - If your team has been accustomed to being able to send a request and have you as the DBA fulfill it, then any change to that process is going to cause some friction. Be aware that at times code does need to get pushed out if it is an emergency. Those needs should be identified, but you should have sign-off from management above the Development and DBA managers for this type of approval. If not, every request will become an emergency and the process you are trying to put in place will not get off the ground.
  • Coding Needs - Most likely if you do not have a standardized code deployment process now then items like comments, deployment code, rollback code, etc are non-existent. Moving forward this is something you should need in order to have a smooth code deployment perspective.
    • Check out this coding comments example.
    • Depending on what is being deployed should dictate the deployment scripts or steps. If you can code it then it can be retained for historical purposes and reviewed if an issue arises in the future.
    • Rollback scripts should be the reciprocal of the code or data being deployed. Having these scripts could be the difference between having an extended downtime or just a blimp on the radar screen. Typically these scripts never see any action, but when they are needed, they are a life saver.
  • Sign-Off - Depending on the formality in your organization dictates the level of formality with sign-off. At some organizations a verbal sign-off is all that is needed, in other organizations it is exchanging emails until you reach agreement and in the third category of organizations a formal written sign-off is necessary. See what makes sense and do not make this process so rigid that no one wants to participate.
  • Process - When you build a schedule, obtain managerial support then communicate the schedule and follow it - what you are really doing is putting a process in place. Do not be afraid of it or resist it, just be aware that it is something you are trying to put into place to better manage your time and stop some of the chaos in your SQL Server environment.

Example Code Deployment Scenario

Below outlines a sample deployment schedule that could be considered a viable option in your SQL Server environment:

  • Code Submission - Every week on Monday @ 12:00 PM all code and rollback scripts need to be submitted to the DBA
  • Code Review - Monday afternoon the DBA reviews the code then tests it in a test environment and executes a best practices checklist on the submitted code
  • Team Meeting - Tuesday @ 9:00 AM questionable code is discussed and determined if it is included in the deployment
    • If the DBA does not have any questions then this meeting can be cancelled, but the DBA should email the team to let them know the needs
  • Deployment - Every Wednesday @ 6:00 AM have jobs that push out the code
  • Review - Every Wednesday @ 6:00 AM the DBA reviews the output and verifies the code
  • Rollback - Every Wednesday @ 6:00 AM the DBA determines if the code needs to be rolled back or not
  • Report - Every Wednesday @ 6:00 AM the DBA reports the status of the code deployment
  • History - Every Wednesday @ 6:00 AM the DBA records the changes that were successfully pushed out

Tuesday, October 23, 2007

SQL Tips : Clearing Cache for Valid Performance Testing

Problem
When conducting performance testing and tuning on a new system, most of the time a number of options are outlined to potentially correct the performance problem. To determine the best overall solution, each option is tested and the results are recorded. As lessons are learned options may be combine for a better end result and often as data is cached the overall query performance improves. Unfortunately, with the data in cache testing each subsequent option may lend itself to an apples to oranges comparison. How can I ensure during each execution of a new set of code that the data is not cached?

Solution
If all of the performance testing is conducted in SQL Server the best approach may be to issue a CHECKPOINT and then issue the DBCC DROPCLEANBUFFERS command. Although the CHECKPOINT process is an automatic internal system process in SQL Server and occurs on a regular basis, it is important to issue this command to write all of the dirty pages for the current database to disk and clean the buffers. Then the DBCC DROPCLEANBUFFERS command can be executed to remove all buffers from the buffer pool. Here is a quick code snippet to serve as an example:

USE ;
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

Although the CHECKPOINT and DBCC DROPCLEANBUFFERS commands seem to be the most elegant approach because they can be included in your T-SQL test scripts, you also can achieve the same results by either restarting the SQL Server instance or restarting Windows. If you are testing via a batch file (or similar) then you could issue 'net stop mssqlserver' and 'net start mssqlserver' DOS commands. As a side note, you also have the option to shutdown SQL Server via the T-SQL SHUTDOWN command, but would need to restart the services via either the 'net start' command or via one of the GUI tools. Although these options are possible, they are not recommended. These last set of commands will shut down your SQL Server instance or machine, which is probably unneeded.

A few words of caution...

It is not recommended to issue the CHECKPOINT\DBCC DROPCLEANBUFFERS, the 'net stop mssqlserver', T-SQL SHUTDOWN command or restarting Windows on production systems just for the sake of testing. These commands could have detrimental results to your environment. It is recommended to only issue these types of commands in testing environments with coordination among your team due to the impact to the overall SQL Server. In addition, keep in mind that if you do issue these commands only in test environments that if multiple tests are being conducted simultaneously issuing the CHECKPOINT and DBCC DROPCLEANBUFFERS commands may skew results for other testers.

Next Steps

  • As you conduct performance testing in the future consider including the CHECKPOINT and DBCC DROPCLEANBUFFERS command in each of your scripts to ensure cached data is not benefiting later executions of your code.
  • As you test, it may be a good idea to capture the query execution results for cold and warm cache.
  • Although, time needed for a query is important, it is also a good idea to review the query plans for the code to determine the best cost among the options.
  • For related information check out the following:


Monday, October 8, 2007

SQL : Using Views to Simplify Data Access

Problem
One challenge that just about everyone is faced with is ever changing database schemas. From the onset of a project the database schema might be perfect on day one, but as the application evolves and the business needs change database table structures have to change. In addition, as database become more and more complex there is often the challenge of having to join several tables together on an ongoing basis which is time consuming and also creates the possibility of mistakes. So what other options are there to ensure your applications do not break when your database schema changes and what is an easier way to handle multi-join queries that are used over and over again?

Solution
The simple solution here is to use Views. Views allow you to predefine what query results will look like, enable you to pre-join your tables as well as allow you to mask any sensitive data that you don't want people to access. Basically a view is a defined and saved query that can be used over and over again.

So in the AdventureWorks database there are several tables that allow us to get employee information. In order to get the following query elements:
EmployeeID
Title
FirstName
MiddleName
LastName
Suffix
Job Title
Phone
EmailAddress
EmailPromotion
AddressLine1
AddressLine2
City
StateProvinceName
PostalCode
CountryRegionName
AdditionalContactInfo

we need to join the following tables.
HumanResources.Employee
Person.Contact
HumanResources.EmployeeAddress
Person.Address
Person.StateProvince
Person.CountryRegion

Writing this query over and over again becomes time consuming and also there are potential issues that the query will not be constructed the same way each time.

Here is what this query would look like. It is not that complex to write, but it would be easier to save this once and reuse the view each time instead of writing this query every time you needed this data.SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];


To create the view for the above query the syntax is as simple as the below code.CREATE VIEW [HumanResources].[vEmployee]
AS
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];


The only difference between the first set of code and the second set of code is:

CREATE VIEW [HumanResources].[vEmployee] AS

This statement is telling SQL Server to create a view called HumanResouces.vEmployee as the query that follows. Once this view has been created instead of writing this query every time we can now use the view as follows or any query as long as the columns exist in the query.SELECT * FROM HumanResources.vEmployee


This is much easier than having to rewrite the query every time. In addition, we are now guaranteed that the result set and the joins will always be consistent.

Another advantage of using views is that the underlying tables can change and the view will still work, granted you did not drop columns that the view was using. But even if you did drop a column in a table you could change the view to return a static value and your application would continue to work without having to make any changes to the application. Although, this depends on the data and the application.

As mentioned above you can grant access to the view and not the table and therefore you can mask certain columns that have sensitive data that you need to store, but do not want everyone to access.

Views can also be joined to other tables or views. But be careful with this, because things can get quite complex to track down issues if you have a bunch of views and start joining views to views.

In addition, you can update views directly and also create indexed views for better performance. There are some constraints as to what you can update in a view, but this can be done.

Next Steps
Take advantage of all the features that SQL Server offers. Views are nothing new, but can simplify tasks that are repeated over and over again.
Look for areas in your database where views can be useful
Enterprise Manager and Management Studio provide query builds that you can use to create these queries and then save them as views.

The top 10 reasons Web sites get hacked

The top 10 reasons Web sites get hacked
Experts say the people who actually build Web applications aren't paying much attention to security; a non-profit group is trying to solve that

By Jon Brodkin, Network World


Web security is at the top of customers' minds after many well-publicized personal data breaches, but the people who actually build Web applications aren't paying much attention to security, experts say.

"They're totally ignoring it," says IT consultant Joel Snyder. "When you go to your Web site design team, what you're looking for is people who are creative and able to build these interesting Web sites... That's No. 1, and No. 9 on the list would be that it's a secure Web site."

The biggest problem is designers aren't building walls within Web applications to partition and validate data moving between parts of the system, he says.

Security is usually something that's considered after a site is built rather than before it is designed, agrees Khalid Kark, senior analyst at Forrester.

"I'd say the majority of Web sites are hackable," Kark says. "The crux of the problem is security isn't thought of at the time of creating the application."

That's a big problem, and it's one the nonprofit Open Web Application Security Project (OWASP) is trying to solve. An OWASP report called "The Ten Most Critical Web Application Security Vulnerabilities" was issued this year to raise awareness about the biggest security challenges facing Web developers.

The first version of the list was released in 2004, but OWASP Chairman Jeff Williams says Web security has barely improved. New technologies such as AJAX and Rich Internet Applications that make Web sites look better also create more attack surfaces, he says. Convincing businesses their Web sites are insecure is no easy task, though.

"It's frustrating to me, because these flaws are so easy to find and so easy to exploit," says Williams, who is also CEO and co-founder of Aspect Security. "It's like missing a wall on a house."

Here is a summary of OWASP's top 10 Web vulnerabilities, including a description of each problem, real-world examples and how to fix the flaws.

1. Cross site scripting (XSS)

The problem: The "most prevalent and pernicious" Web application security vulnerability, XSS flaws happen when an application sends user data to a Web browser without first validating or encoding the content. This lets hackers execute malicious scripts in a browser, letting them hijack user sessions, deface Web sites, insert hostile content and conduct phishing and malware attacks.

Attacks are usually executed with JavaScript, letting hackers manipulate any aspect of a page. In a worst-case scenario, a hacker could steal information and impersonate a user on a bank's Web site, according to Snyder.

Real-world example: PayPal was targeted last year when attackers redirected PayPal visitors to a page warning users their accounts had been compromised. Victims were redirected to a phishing site and prompted to enter PayPal login information, Social Security numbers and credit card details. PayPal said it closed the vulnerability in June 2006.

How to protect users: Use a whitelist to validate all incoming data, which rejects any data that's not specified on the whitelist as being good. This approach is the opposite of blacklisting, which rejects only inputs known to be bad.

Additionally, use appropriate encoding of all output data. "Validation allows the detection of attacks, and encoding prevents any successful script injection from running in the browser," OWASP says.

2. Injection flaws

The problem: When user-supplied data is sent to interpreters as part of a command or query, hackers trick the interpreter -- which interprets text-based commands -- into executing unintended commands. "Injection flaws allow attackers to create, read, update, or delete any arbitrary data available to the application," OWASP writes. "In the worst-case scenario, these flaws allow an attacker to completely compromise the application and the underlying systems, even bypassing deeply nested firewalled environments."

Real-world example: Russian hackers broke into a Rhode Island government Web site to steal credit card data in January 2006. Hackers claimed the SQL injection attack stole 53,000 credit card numbers, while the hosting service provider claims it was only 4,113.

How to protect users: Avoid using interpreters if possible. "If you must invoke an interpreter, the key method to avoid injections is the use of safe APIs, such as strongly typed parameterized queries and object relational mapping libraries," OWASP writes.

3. Malicious file execution

The problem: Hackers can perform remote code execution, remote installation of rootkits, or completely compromise a system. Any type of Web application is vulnerable if it accepts filenames or files from users. The vulnerability may be most common with PHP, a widely used scripting language for Web development.

Real-world example: A teenage programmer discovered in 2002 that Guess.com was vulnerable to attacks that could steal more than 200,000 customer records from the Guess database, including names, credit card numbers and expiration dates. Guess agreed to upgrade its information security the next year after being investigated by the Federal Trade Commission.

How to protect users: Don't use input supplied by users in any filename for server-based resources, such as images and script inclusions. Set firewall rules to prevent new connections to external Web sites and internal systems.

4. Insecure direct object reference

The problem: Attackers manipulate direct object references to gain unauthorized access to other objects. It happens when URLs or form parameters contain references to objects such as files, directories, database records or keys.

Banking Web sites commonly use a customer account number as the primary key, and may expose account numbers in the Web interface.

"References to database keys are frequently exposed," OWASP writes. "An attacker can attack these parameters simply by guessing or searching for another valid key. Often, these are sequential in nature."

Real-world example: An Australian Taxation Office site was hacked in 2000 by a user who changed a tax ID present in a URL to access details on 17,000 companies. The hacker e-mailed the 17,000 businesses to notify them of the security breach.

How to protect users: Use an index, indirect reference map or another indirect method to avoid exposure of direct object references. If you can't avoid direct references, authorize Web site visitors before using them.

5. Cross site request forgery

The problem: "Simple and devastating," this attack takes control of victim's browser when it is logged onto a Web site, and sends malicious requests to the Web application. Web sites are extremely vulnerable, partly because they tend to authorize requests based on session cookies or "remember me" functionality. Banks are potential targets.

"Ninety-nine percent of the applications on the Internet are susceptible to cross site request forgery," Williams says. "Has there been an actual exploit where someone's lost money? Probably the banks don't even know. To the bank, all it looks like is a legitimate transaction from a logged-in user."

Real-world example: A hacker known as Samy gained more than a million "friends" on MySpace.com with a worm in late 2005, automatically including the message "Samy is my hero" in thousands of MySpace pages. The attack itself may not have been that harmful, but it was said to demonstrate the power of combining cross site scripting with cross site request forgery. Another example that came to light one year ago exposed a Google vulnerability allowing outside sites to change a Google user's language preferences.

How to protect users: Don't rely on credentials or tokens automatically submitted by browsers. "The only solution is to use a custom token that the browser will not 'remember,'" OWASP writes.

6. Information leakage and improper error handling

The problem: Error messages that applications generate and display to users are useful to hackers when they violate privacy or unintentionally leak information about the program's configuration and internal workings.

"Web applications will often leak information about their internal state through detailed or debug error messages. Often, this information can be leveraged to launch or even automate more powerful attacks," OWASP says.

Real-world example: Information leakage goes well beyond error handling, applying also to breaches occurring when confidential data is left in plain sight. The ChoicePoint debacle in early 2005 thus falls somewhere in this category. The records of 163,000 consumers were compromised after criminals pretending to be legitimate ChoicePoint customers sought details about individuals listed in the company's database of personal information. ChoicePoint subsequently limited its sales of information products containing sensitive data.

How to protect users: Use a testing tool such as OWASP'S WebScarab Project to see what errors your application generates. "Applications that have not been tested in this way will almost certainly generate unexpected error output," OWASP writes.

Another tip: disable or limit detailed error handling, and don't display debug information to users.

7. Broken authentication and session management

The problem: User and administrative accounts can be hijacked when applications fail to protect credentials and session tokens from beginning to end. Watch out for privacy violations and the undermining of authorization and accountability controls.

"Flaws in the main authentication mechanism are not uncommon, but weaknesses are more often introduced through ancillary authentication functions such as logout, password management, timeouts, remember me, secret question and account update," OWASP writes.

Real-world example: Microsoft had to eliminate a vulnerability in Hotmail that could have let malicious JavaScript programmers steal user passwords in 2002. Revealed by a networking products reseller, the flaw was vulnerable to e-mails containing Trojans that altered the Hotmail user interface, forcing users to repeatedly reenter their passwords and unwittingly send them to hackers.

How to protect users: Communication and credential storage has to be secure. The SSL protocol for transmitting private documents should be the only option for authenticated parts of the application, and credentials should be stored in hashed or encrypted form.

Another tip: get rid of custom cookies used for authentication or session management.

8. Insecure cryptographic storage

The problem: Many Web developers fail to encrypt sensitive data in storage, even though cryptography is a key part of most Web applications. Even when encryption is present, it's often poorly designed, using inappropriate ciphers.

"These flaws can lead to disclosure of sensitive data and compliance violations," OWASP writes.

Real-world example: The TJX data breach that exposed 45.7 million credit and debit card numbers. A Canadian government investigation faulted TJX for failing to upgrade its data encryption system before it was targeted by electronic eavesdropping starting in July 2005.

Furthermore, generate keys offline, and never transmit private keys over insecure channels.

It's pretty common to store credit card numbers these days, but with a Payment Card Industry Data Security Standard https://www.pcisecuritystandards.org/ compliance deadline coming next year, OWASP says it's easier to stop storing the numbers altogether.

9. Insecure communications

The problem: Similar to No. 8, this is a failure to encrypt network traffic when it's necessary to protect sensitive communications. Attackers can access unprotected conversations, including transmissions of credentials and sensitive information. For this reason, PCI standards require encryption of credit card information transmitted over the Internet.

Real-world example: TJX again. Investigators believe hackers used a telescope-shaped antenna and laptop computer to steal data exchanged wirelessly between portable price-checking devices, cash registers and store computers, the Wall Street Journal reported.

"The $17.4-billion retailer's wireless network had less security than many people have on their home networks," the Journal wrote. TJX was using the WEP encoding system, rather than the more robust WPA.

How to protect users: Use SSL on any authenticated connection or during the transmission of sensitive data, such as user credentials, credit card details, health records and other private information. SSL or a similar encryption protocol should also be applied to client, partner, staff and administrative access to online systems. Use transport layer security or protocol level encryption to protect communications between parts of your infrastructure, such as Web servers and database systems.

10. Failure to restrict URL access

The problem: Some Web pages are supposed to be restricted to a small subset of privileged users, such as administrators. Yet often there's no real protection of these pages, and hackers can find the URLs by making educated guesses. Say a URL refers to an ID number such as "123456." A hacker might say 'I wonder what's in 123457?' Williams says.

The attacks targeting this vulnerability are called forced browsing, "which encompasses guessing links and brute force techniques to find unprotected pages," OWASP says.

Real-world example: A hole on the Macworld Conference & Expo Web site this year let users get "Platinum" passes worth nearly $1,700 and special access to a Steve Jobs keynote speech, all for free. The flaw was code that evaluated privileges on the client but not on the server, letting people grab free passes via JavaScript on the browser, rather than the server.

How to protect users: Don't assume users will be unaware of hidden URLs. All URLs and business functions should be protected by an effective access control mechanism that verifies the user's role and privileges. "Make sure this is done ... every step of the way, not just once towards the beginning of any multistep process,' OWASP advises.