Wiley Microsoft SQL Server 2005 Express Edition For Dummies
Contents
1. Classi P 28 Constrarks and Keys eats Gl Tropes 5 2g Ddexes sly PK flavors 7C EZ Constrarts and Keys n tg Triggers g indexes t sesdiacams Properties Sabi alos Identity Ready 13 4 Part I Welcome to SOL Server 2005 Express High availability features The entire SQL Server 2005 product family offers numerous technologies to help keep your database up and run ning at all times However Express doesn t offer these capabilities all of which reduce down time or help improve performance Online restore e Database mirroring Partitioning Failover clustering Online indexing If you re building a mission critical application and continual uptime is of vital importance to you you may want to deploy your solution on one of the other SQL Server 2005 editions Rich programming language choices The entire SQL Server 2005 prod uct line supports the Microsoft Common Language Runtime CLR This means that you can develop internal database logic such as stored pro cedures and triggers in any one of a number of popular programming languages rather than in SQL Server s internal programming language Transact SQL Analysis services The more advanced editions of SQL Server 2005 fea ture business analytic logic that you can use to help make sense of your information This is especially true if your environment sports massive volumes of data that need to be crunc2. enl Local H Devine Manager B Sevege g Removable storage Disk Defragmenter Disk Management fa Services and Applications ie Services WML Contre aj SQL Server Configuestion Manager SAL Server 2005 Services SQL Server 2005 Network Configuration 9 El indexing service D Internet Infcemation Services Chapter 1 SOL Server 2005 Express Overview E Figure 1 4 Viewing a table in SQL Server Manage ment Studio Express D For further configuring and managing your SQL Server 2005 Express server you can choose between graphical and character based tools As you might expect graphical tools provide more intuitive easy to understand informa tion about your server When it comes to graphical tools it s hard to beat Microsoft s free excellent graphical management environment known as SQL Server Management Studio Express Figure 1 4 shows a sample of what this product looks like SQL Server Management Studio Express can do much more than simply show you tables and run queries One handy tool is the activity monitor which opens a window onto all database related activity for your server Figure 1 5 shows a sample session you can monitor a tremendous amount of informa tion using this tool For those of you who prefer a character based configuration and manage ment utility Microsoft continues to ship the SQLCMD utility which allows for direct entry of SQL statements Because you
3. intensive SQL Server 2005 Express strikes a nice balance between simplicity and power while holding the cost as low as you can possibly get Plus because many small businesses one day find themselves morphed or acquired into larger enterprises making this database server the cornerstone of your data storage architecture means that you never outgrow your database You can easily upgrade to SQL Server 2005 Enterprise On top of that the fact that SQL Server 2005 Express is a true relational data base management platform means that you can store and track just about anything in your database Some particularly good applications include 1 Inventory details 1 Sales statistics Financial metrics 6 Part I Welcome to SOL Server 2005 Express NG O When you have this information safely ensconced in your SQL Server 2005 Express database you re free to use all sorts of tools and technologies to help make sense of your data For example you might store details about all the individual transactions that your organization performs and then use business intelligence or other data analysis tools to help identify trends from your sales results While you re free to store anything you like in your SQL Server 2005 Express database be aware that any individual database has a maximum storage limit of 4GB for your information If you find yourself approaching that ceiling you need to archive some of your older data to make room for ne
4. 19 and 20 which cover Visual Basic 2005 Express and Visual Web Developer 2005 Express respectively For a sneak preview Figure 1 2 shows how closely Visual Basic 2005 Express works with SQL Server 2005 Express This tight integration can go a long way toward boosting your productivity In effect with these products Microsoft has broken down the traditional barrier between application and database tools SL a Name CustomerID d Solution Ex Data Sources Allow fads Ro Properties lt X Deta Type wrigueidantiher Defaut value or Binding TDI dbo Tablet a a Zh B Identity m Comp ated Colum Spedfication Name Teblet Condensed Dats Type urigueidenther Description Desetption I Full text Soectication Ro E Scheme dto E idertty specficaten Ko wl l Table Designer General 2 identity 3j tceCreamByMail Microsoft Visual Basic 2005 Express Edition eu Fle Edt vew Projet Buld Debug Data TableDespner Tods Window Commurky Heb iL udsaaLALILLILZLLLCLCE LAE AM o Database Expkrer BX dbo Tablel MBYMAILMDE S sors sb Dss Saktion Explorer wax 2 racine ke S Column Mame DxaType amp b Nus zT I LH OxaCenecter Fp f customer vrkqueidenrfier ES IceCreamiiyMail b Ice Cream EyMsl mdr __ Lesteme r var chet 50 7 ag My Project J Ostebase Diagrams Risthlase evar dvar E0 a Fermi sb E Tables IB access ede 1cereenByPai mdr een Address river char S0 7 a Jay 2 m ste t il assembl
5. 2005 Express 1 Visual C 2005 Express Visual C 2005 Express 1 Visual J 2005 Express All these products work seamlessly with SQL Server 2005 Express If you re inter ested in taking advantage of these products have a look at Chapters 19 and 20 11 2 Part I Welcome to SOL Server 2005 Express The SOL Server 2005 Express Environment EMBER D Although it s the most junior member of the family SQL Server 2005 Express has more than enough capabilities and power to support the majority of database driven applications You should be aware of these key features along with some limitations as you build your solution YY Robust technology platform SQL Server 2005 Express is built on the same underlying technology platform as all the Microsoft SQL Server products This significantly increases this product s effectiveness and stability Interoperability and upgradeability Because SQL Server 2005 Express is built on the underlying SQL Server 2005 platform you can easily develop solutions that work with any SQL Server 2005 edition In addi tion you can straightforwardly migrate from this entry level edition to a more full featured edition without changing your database design or altering any application or stored procedure trigger code Memory With memory costs dropping significantly over time many administrators now stock their computers with large amounts of RAM However regardless of how much memory yo
6. Chapter 1 SQL Server 2005 Express verview In This Chapter Introducing SQL Server 2005 Express SSE Determining whether SSE is right for you Discovering the features of SSE Understanding how SSE works Getting going with SQL Server 2005 Express E the developer s or user s perspective SQL Server 2005 Express strikes a nice balance between price free and performance powerful In this chapter I offer up some insight into this new product To begin I give you some context to understand how this capable yet entry level product came about along with how it fits in with the rest of the SOL Server product family Next you see who the ideal user is for SQL Server 2005 Express along with a list of some of the product s most compelling features as well as what s not present Finally I show you the kind of tools that you ll want to acquire so you can get started building applications and working with your SQL Server 2005 Express data Jumping on Board the SOL Server Express Once upon a time if you wanted to store information on a computer you had to write your own low level highly specialized program that organized this data and also allowed you to update and retrieve the data This was very cumbersome time consuming and error prone Eventually a host of special ized companies sprang up to provide standardized industrial strength prod ucts known as databases A database is a special kind of software application whose main
7. Ko a C3 Securty a J WestBay PARA GB Seasty E Tdecrity Spediication Ko a 3 Sever Objects a Gi Replication a 3 Management General DbjectListName gt Ready 21 2 2 Part I Welcome to SOL Server 2005 Express tor i Retesh Y Fier L3 Heb Locks by Process ium by Dbject Selected object FiccessID Corian Batch iD master sadeg 1 temodo syselccuni Fx 19 a 5 s DATABASE ap 53 HOET 720575 64099503480 Schi ap 54 HOET 7205754068714368 Soh ap 19 METADATA DATABASE 0 dalabaea i Sch M a to METADATA INDEMSTATS 0 abject i Sohs Status m S METADATA DATA SPACE 0 data_space Schs ics aliad LE METADATA DATA SPACE 0 dali space Sch i Win Qs OBJECT E K 12 2 2006 19 91 17 PM m UBECT a 3 Next Refresh 8 ta 5 K Manual up 53 162085618 SchM B ie seftesh e 2 u ji is 0s mp5 21575115 Sch M E R QB 4 K Figure 1 5 T reissen as 5 Connection Qs 35 u The SQL ge S Server FIGLIRENSQLEXPRESS Server as x Connection JFIGUAE Rob K Manage as x v P 35 vesconvercnneoses D 54 X ment Studio a5 4 u 5 5 X Express re gt gt Dane v 3 2 Uu Activity X 2 x lt Monitor
8. as 4 a Contry al retexired cotetins ie DeteQut dstetme v Ready 19 20 SSS Figure 1 3 Configuring SQL Server 2005 Express services Pc RT Part I Welcome to SOL Server 2005 Express If you re more inclined to use heavier weight more powerful tools Visual Studio 2005 makes a lot of sense to employ for application development It s an extremely potent tool that contains a tremendous amount of functionality It s also very well integrated with all SQL Server 2005 products not only the Express edition Naturally all this power comes with additional complexity and a somewhat steeper learning curve so you should decide if your applica tion requires all these capabilities Of course Microsoft isn t the only game in town when it comes to application development technologies You can use several third party tools including open source and shareware to construct a SQL Server based solution Configuring managing and monitoring SQL Server 2005 Express With your database and application built it s natural to turn your attention to setting configuration parameters and then monitoring the database Here again you have no shortage of options To begin SQL Server 2005 Express snaps into the standard Microsoft Computer Management console This lets you configure and run your data base services which you can see in Figure 1 3 9 Computer Management ES File Action View Window Help e SB m
9. computer with more than one central pro cessing unit CPU is a great way to increase performance and through put However SQL Server 2005 Express only takes advantage of one CPU regardless of how many are available Administrative tools Microsoft offers the excellent SOL Server Management Studio Express graphical tool to help you monitor and manage your database It s available for free download from Microsoft s Web site Visual Studio integration Although SQL Server 2005 Express is consid ered an entry level product you re free to use the full power of Microsoft s flagship development platform Visual Studio This product is fully inte grated with all the SQL Server 2005 database offerings including Express Figure 1 1 shows you what this looks like in the context of building a C class file Y Full text searching This feature helps you index and then query large blocks of text based information Although it s not present in SQL Server 2005 Express you will find it in SQL Server 2005 Express with Advanced Services Regardless of which version you use your application can still store and work with this kind of information the method you use might vary however luo Classt cs Microsoft Visual Studio Server Explorer File Edt View Projact Dabug Data Toob Test Window Communty Hep te a Ga d La 3d Data Connections iia Lif rschemider t30 sqhenress watashLw 3 SO B Classics using System
10. e size constraints Diminished high availability Reduced business intelligence 1 Express Now it s time to look at the star of this show This is the sim plest and easiest to use database offering in the SOL Server 2005 prod uct family On top of that accolade it s also free to download and redistribute with some licensing restrictions This is the right edition for you if any of the following describe you A software developer seasoned or brand new wanting to learn about relational databases packaged application provider looking to embed a free yet sturdy database with your solution n end user with a lot of information to store but not a lot of cash to buy a database To get the lowdown on the differences among each SQL Server 2005 edition go to www microsoft com sql prodinfo features compare features mspx As you evaluate potential uses for SQL Server Express 2005 note that you can use it for all sorts of applications from traditional desktop based rich client software as well as browser based solutions that are available over the Internet SQL Server 2005 Express is just one component of a larger collection of entry level products aimed at helping developers and hobbyists use Microsoft s tech nologies to deliver powerful and flexible computing solutions These products all of which are part of Visual Studio 2005 Express include the following 1 Visual Web Developer 2005 Express 1 Visual Basic
11. find much of the administrative capabilities for SQL Server embedded in stored procedures you can run just about any management operation from the rather bland SQLCMD interface Microsoft SQL Server Management Studio Express beau Fl Edt vew TablDespne Tods Window Heb Lal Mew Query Da S ud 2o 3 Be E B aana ap Object Explorar vU X Table dbo Transactions X Properties z4x Column Name Data Type Allow fads DBropWinSsy20Tebk ops x XPRESS SOA Server 3 0 1314 7FIGLREIROD P Transactonlb uriquekdeslilier e TraneactionDate dade ral Detabesefleme a Seren Detbeses Temo int v DetaSpecsorTextlmage PRIMARY D Ug nothbor Transacteramoun decmaks 2 z Description F temadach Cohan 5 a Database Diagrams 1eindexable 5 Qa Taies IsRepleated B iy System Tables ObjectListName e dbo Backorder ObjactName Transactions CJ dbo CustomerMaster Rovxaddcoum CJ do PartOfSds Column Properties Schema do 3 C doo Transections ServerName 2 C3 Coanre EDS I TrarsadtionID uriqueidersilizr nil E TraneadionDabe dateting nul Ere E TteniD rt nul Nama tenir E Transactionamourt dedma 9 2 nul Allows Niis Yes 2 L3 Keys Data Type urigueidsatier s Defadt value or Binding E Table Designer D takies f3 Computed Coben Specficaten 5 C doa vender Condensed Date Typs unigueidenther C doo Warehouse Desaipron C3 Mews 5 3 Synonyms 3 Progr emmebiicy F Fultext specfeior
12. h store but you need to take advantage of a true rela tional database management system s features such as advanced security stored procedures or triggers You also need to gather and consolidate this data for reporting purposes This is a good use for SQL Server 2005 Express It offers enough power for enterprise class applications without demanding teams of highly trained administrators for its daily care and feeding Chapter 1 SOL Server 2005 Express Overview 7 If you re curious about all that you can do in a distributed computing envi ronment check out Chapter 6 Independent Systems Vendor Original Equipment Manufacturer ISV OEM ISVs and OEMs have first hand experience that the cost of embedded tech nology can eat into profits That s not a problem with SQL Server 2005 Express Free generally doesn t have much of an impact on margins However don t be fooled by the price This is a full featured database built on the SQL Server platform If you base your applications and solutions around SQL Server 2005 Express you re not locked in If your customers need extra horsepower and capabili ties they can quickly and painlessly upgrade to a more powerful edition of SQL Server The beauty of basing your solutions on a single relational database management product family is that you can do your development on the more advanced edi tions of SQL Server using robust tools like Visual Studio and then easily deploy you
13. hed to come up with recognizable patterns The bad news is that this is not present in SQL Server 2005 Express However the good news is that chances are that if these kinds of data are found in your organization you ll have already purchased one of the many third party business intelligence products Report server Users always want more information out of their data base With SQL Server 2005 s reporting services you can set up a wide range of developer driven and user driven reports You can then inte grate and deliver these communiqu s through a series of different pre sentation technologies Happily you ll find these capabilities present in SQL Server 2005 Express with Advanced Services which should help please those finicky users If you re curious about how these features work take a look at Chapter 18 for the details 1 Integration services These features allow you to write powerful integra tion logic that can take information from a broad range of other data storage locations and then store it inside SOL Server The same holds true for outbound data Unfortunately you won t find these services pre sent in SQL Server 2005 Express Luckily this doesn t mean that you can t integrate data among disparate systems it just means that you may have to do some more work to achieve the same results Notification services This refers to the capability found only in the more feature rich editions of SQL Server 2005 to build sophi
14. ite this segmentation all editions are built on the same underlying technology platform The main difference among the editions is that the sim pler less far reaching versions don t have all the features and storage capac ity that you find in their larger and more complex siblings Here s a list of all the SQL Server 2005 editions Enterprise This is the most robust edition of SQL Server 2005 It includes a host of features that make it a good choice for a 24 7 mission critical database server platform Just a few of these features include e Advanced business intelligence analytics Robust data transformation logic High availability capabilities This product also has two developer focused versions which contain all the Enterprise features but are licensed differently These are SQL Server 2005 Developer Edition and SQL Server 2005 Evaluation Edition Standard With much of the feature set of its big brother this edition is fine for the vast majority of database applications The main difference is that this edition is somewhat lighter in both its business intelligence and high availability feature sets 1 Workgroup Aimed at smaller departmental applications this still powerful edition of SQL Server 2005 introduces some limitations Chapter 1 SOL Server 2005 Express Overview that are not likely to be issues for these smaller computing environ ments Some of these restrictions include Hardware and databas
15. ou can design your solution on a more powerful edition of SQL Server 2005 and then deploy it onto SQL Server 2005 Express as long as it doesn t require any of the fea tures that are only found in the more expensive editions of the product One feature that s particularly attractive for distributed application develop ers and vendors is the Xcopy deployment capability of SOL Server 2005 Express This lets you easily bundle your application and database mdf file and then copy them to another machine Because everything is already pre packaged you don t need to manually configure these other platforms as long as they have a running instance of SQL Server 2005 Express When your Chapter 1 SOL Server 2005 Express Overview m P Figure 1 2 Visual Basic 2005 Express and SQL Server 2005 Express EEE application launches the database server automatically attaches the mdf file to the local instance This architecture results in an easily implemented portability strategy Building SOL Server 2005 Express applications You have a wide variety of electives when building a solution that stores its information in this database To begin as I described earlier in this chapter Microsoft is making the Visual Studio Express products affordable and easy to use to create database driven applications If you re new to application development checking out these products is definitely worth your while In fact take a look at Chapters
16. purpose is to help people and programs store organize and retrieve information This 0 Part I Welcome to SOL Server 2005 Express frees up application developers to focus on the business task at hand rather than being responsible for supervising the intricacies of data management As more time passed a new breed of database companies arose With names like Oracle Informix and Sybase these vendors and many others developed a particular kind of database known as a relational database Relational data bases are particularly well designed for storing information in tabular format which further helped software developers as they built a whole new class of enterprise applications Microsoft also entered the relational database fray some years back with the SQL Server database Once thought of as a relatively lightweight database vendor Microsoft has continually refined SQL Server to the point where it can compete for the largest and most complicated database driven applica tions Of course all these capabilities and power have made the SQL Server database platform somewhat intimidating and confusing for many developers and users especially those that are building and using simpler solutions To address this problem beginning with the SQL Server 2005 product family Microsoft has created several different versions of SQL Server Known as edi tions each of these versions addresses a different class of database environ ment Desp
17. r application onto the lightweight SQL Server 2005 Express As an added benefit to OEMs and ISVs SQL Server 2005 Express lets you store your data in a single easily transferred file Known as an mdf file this structure combines the simplicity of a low end flat file with the power of a true relational database management system You should note however that you have to deploy the SQL Server Express 2005 server even if you only intend to deploy mdf files with your solution Getting Down to Business with SOL Server 2005 Express In previous sections I show you all that you get with your free download of SQL Server 2005 Express Edition If you re wondering how you can get started using the product that s what this section is all about To begin I point out some things to ponder as you plan and deploy your database Next I talk about all the programming tools at your disposal for building a SQL Server 2005 Express based application Finally I show how you can configure and monitor your database 18 Part I Welcome to SOL Server 2005 Express For those of you who have followed the entry level SOL Server database for some time you re probably wondering what happened to the pre vious offering from Microsoft Known as MSDE which stands for Microsoft Desktop Engine or Microsoft Data Engine What happened to MSDE depending on who answers the phone in Redmond this database was more compli cated to administer le
18. ss feature rich and had smaller capacity than its replacement SQL Server 2005 Express Planning your database The first thing to keep in mind as you contemplate what to keep in your SQL Server 2005 Express database is that this is not a stripped down feature limited stand alone product It s true that Express does have significant limi tations to the amount of information you can store in the database see The SQL Server 2005 Express Environment earlier in this chapter for more details about that and that certain key high volume features are not present Before you get heavily into using this edition you should make sure that none of these limitations are showstoppers for you Remember that the majority of these constraints shouldn t impact you during development they re only an issue at runtime Even though this edition has feature restrictions SQL Server 2005 Express works with all the same types of information as its more fully featured sib lings and you can easily migrate to a more powerful edition It also supports all the same application programming interfaces APIs as well as the same stored procedure and trigger capabilities This means that as a designer you can build your SQL Server 2005 Express database with confidence knowing that you aren t painted into a corner by missing capabilities nor the solution you design is forever consigned to this entry level database This works the other way as well Y
19. sticated publish and subscribe applications Once created these applications Chapter 1 SOL Server 2005 Express Overview 5 can properly react to a wide variety of events notifying all interested parties that an event has occurred SQL Server 2005 Express does let you take advantage of replication but only as a subscriber Backup recovery options Because backup and recovery are a vital part of any data integrity and reliability plan SQL Server 2005 offers adminis trators a choice among several different recovery models each of which has its own strengths and weaknesses However your data archiving choices are somewhat more reduced for SQL Server 2005 Express You can still set up a very robust backup and recovery strategy it s just not as full featured as in the more advanced editions of SQL Server In partic ular because the SQL Server Agent is not present you won t be able to schedule jobs or configure alerts and operators Where Does SOL Server Express 2005 Work Best SQL Server 2005 Express brings a lot to the table but when does it make sense to choose it as your data storage platform I answer this question in this section Small office Home office SOHO Small offices and home offices SOHO are often caught between a rock and a hard place when dealing with software and solutions Either the technology is underpowered and simplistic not quite meeting their needs or it s expen sive overly complex and resource
20. u have SQL Server 2005 Express doesn t take advantage of any more than 1GB of RAM Automatic performance tuning This is one feature that SQL Server 2005 Express shares with the other database editions It lightens the administrative load by automatically updating the database s configura tion based on system activities and other profiling data However no automation can free you from the performance implications of an inefficient database or application construction You are still responsible for designing your database structure and software logic with speed in mind 1 Database size This is probably the most significant limitation of SQL Server 2005 Express because any given database can t be bigger than 4GB Although this limitation won t be a problem for many applications certain data intensive solutions can test it Of course you are free to have multiple databases that are each 4GB Full security capabilities Here s another area where this edition and the more costly versions of SQL Server 2005 basically have no differences This means that you can take advantage of a broad band of security fea tures as you go about developing your solutions These range from authentication and auditing all the way through public key management and encryption Chapter 1 SOL Server 2005 Express Overview Ce Figure 1 1 SQL Server 2005 Express information available from inside Visual Studio YY Multi processors Running a
21. wer knowledge Distributed enterprise With the rise of low cost high speed Internet connections many more organi zations are realizing benefits from distributed computing For the distributed enterprise of any size SQL Server 2005 Express offers a good balance between the low maintenance requirements of an entry level database like Microsoft Access and the capabilities of a robust server based database In addition to these features you can administer multiple remote SQL Server 2005 Express sites from one location via the SQL Server Management Studio Express Another useful capability of SQL Server 2005 Express is its ability to partici pate as a client in a replication architecture This means that you could have a single high end edition of SQL Server distributing its data among numerous SQL Server 2005 Express clients These clients could in turn support read only applications like reporting or business intelligence This type of archi tecture spreads the processing load across multiple machines and helps eliminate bottlenecks Finally another good illustration of distributed computing is to use SQL Server 2005 Express as a local database and then aggregate its information to a central server for safekeeping and analysis For example suppose that you re building a retail application that will support dozens of locations none of which will have a database administrator You could install a traditional low end database in eac
Download Pdf Manuals
Related Search