b y George Squillace MCT , MCSE, MCDBA, MCITP – Business Intelligence Development,

1. A “Big Picture View” of SQL Server Database Development and Database Administration by George Squillace MCT, MCSE, MCDBA, MCITP – Business Intelligence Development, MCITP – Database Administration, MCITP – Enterprise Messaging (Exchange 2007), MCITP – Windows Server 2008 Enterprise Administrator, MCTS – Vista Deployment, CompTIA A+, Network+

2. Topics • Basics of Database Administration • Basics of Database Development • Relevant Courses • Certification Tracks • Resources Q: Am I an education consultant or do I merely process orders?

4. Open SQL Server Management Studio (SSMS) • Start button • >>All Programs • >>Microsoft SQL Server 2008 • >>SQL Server Management Studio • Navigate the Object Explorer pane • Observe Instance Properties • Look at the “Security” page • ObserveDatabase-specific Properties • Look at the “Files” page • Observe Object Explorer hierarchy • Databases >> Tables >> Columns, etc.

5. Create a Database • Use code • Open a New Query Window • Create Database BusinessSystemDB • Click on the “Execute” button or press “F5” • Use GUI • R-click on the Databases node, • New Database • Enter thename “InventoryDB” • Complete the remainder of the dialog box • Observe resulting data and log file and their available options • The DBA must manage the file sizes of the databases in his/her care

6. Create Tables, Part 1 (Using the GUI) • Expand the BusinessSystemDB • Right click on the “Tables” node, choose “New Table” • Create Customers table • CustomerID • INT, Use Identity column (1000,10), Not Null • Company Name • Varchar(40), Not Null • PrimaryContactName • Varchar(60), Not Null • FederalID INT NULL • NumberOfEmployees, Int, NULL • Save Table • Add data through the “Edit Top 200 Rows” feature of SSMS

7. Create Tables, Part 2 • {Make sure you’re connected to the “BusinessSystemDB”} • Create Products table • Use codeCreate Table Products (ProdIDint Identity(1,1) Not Null, ProductNamevarchar(30) Not Null,ProductCost money Not Null,ProductTypevarchar(20) Default ‘Training’ Not Null,AvailabilityDateSmallDateTime Null);

8. Insert rows into tables • Insert INTO Products(ProductName, ProductCost)Values (‘SQL Admin’, ‘3000’) • Add two of your own products by changing the code above

9. Query tables • Select * from Products • Select * from Customers • Display a subset of columns • Select CompanyName,PrimaryContactName from Customers • Display a subset of rows using a WHERE Clause • Select * from Products Where ProdID = 2

11. Change Authentication mode to mixed • Right-click Server node in Object Explorer, • Properties • Security Page • SQL and Windows Authentication • Restart SQL Server Services

12. Create a SQL Login • Expand the Security Node • R-click the Logins Node • New Login • Enter a name • SQL Server Authentication • Assign a password of “123” • Remove “Enforce password policy”

13. Assign Permissions to the new SQL Login • Create a Database User for the new SQL Login in the “BusinessSystemDB” database • Assign SELECT permission to SQL user on Products table • Navigate from the database to the Tables node • Right click on the Products table • Choose Properties • Click on the “Permissions” page • Click on the “Search” button • Click on the “Browse” button, and then select the new user you created. • “Grant” the “Select” permission.

14. Query table as the new SQL user • Select * from Products

15. Use Import/Export Wizard to create Excel version of table • R-click BusinessSystemDB • Tasks • Export Data • Follow remaining wizard steps • Open resulting Excel workbook

16. Backup up database • R-click BusinessSystemDB >>Tasks >>Back up

17. “Destroy” the database • Use your administrative connection • Truncate table Products • Verify all rows are gone • Drop Table customers

18. Restore database from backup • Disconnect current connections • R-click BusinessSystemDB • Tasks • Restore Use the Allow Overwrite option • Reconnect • Query both tables, to reveal successful restoration

19. Create a View • Define a View • Saved query code • Create View vBriefCompanyInfoASSelectCompanyName as CN, NumberOfEmployees as EmpsFrom Customers • Test the ViewSelect * from vBriefCompanyInfo

20. Tour of Database activities • Administrative activities • Manage file space allocation • Create Logins and Users • Assign Permissions • Backup Database • Restore Database • Transfer data • Developer activities • Create Database • Create Table • Create View

21. Related Courses: • 2778A Querying (3 days) • Intended for current and future Database Developers and Database Administrators • General query writers should take the End User SQL courses (Level 1 and, optionally, Level 2) • 6231B Database Administration (five days) • Revision “B” will be released imminently. It is a very different book from Revision “A”. • 6232B Database Programming “Implementing…” (five days) • Revision “B” was just released. It is a very different book from Revision “A”.

22. SQL 2008 Related Certifications MCITP: 70-450 MCITP: 70-451 MCITP: 70-452 180 minutes, 88 story problems MCTS: 70-448 120 minutes, 44 questions, covers SSAS, SSIS, and SSRS MCTS: 70-432 MCTS: 70-433 Database Administration Database Programming “Implementing…” Business Intelligence Development

23. Resources • My Home page • www.e-squillace.com • Notice the “Search” button at the top of the page • Notice the “Strategies for Passing Certification Exams” link • Notice the link to my library books (very bottom of home page) • Diagrams page • www.e-squillace.com/tech/techdiagrams • Screenshots page • www.e-squillace.com/tech/screenshotgallery • My SQL Server home page • www.e-squillace.com/tech/techreference/sql • See also the “Newsletters” and “User Groups” links • My BI home page • www.e-squillace.com/tech/techreference/sqlbi/sqlbi.htm

24. Questions ???