Did you ever want to take a Microsoft exam from home or the office?

 

Starting today, if you are a U.S. resident (and if you’re not stay tuned—they’ll be expanding soon), you can take dozens of the MCP and MTA exams from the comfort of your home or office through a process called online proctoring by Pearson VUE. Or as Ken Rosen says "Come on, admit it: you’ve always wanted to take one of our exams in your pajamas. I can’t be the only one."

See Ken's blog article here and the details, rules and regulations here.

When you look at the list of exams available you will find numbers like 462-OP (on premise?). Add a 70 in front of the number and remove the "-OP" to find the equivalent exam ID. (70-462).  The "98" series exams just have the normal number with "-OP" added to the end.

Here are some of the "OP Beta" exams that are "in my world" (SharePoint, SQL, etc.):

346-OP Managing Office 365 Identities and Requirements
347-OP Enabling Office 365 Services

410-OP Installing and Configuring Windows Server 2012
411-OP Administering Windows Server 2012
412-OP Configuring Advanced Windows Server 2012 Services

461-OP Querying Microsoft SQL Server 2012
462-OP Administering Microsoft SQL Server 2012 Databases
463-OP Implementing a Data Warehouse with Microsoft SQL Server 2012

480-OP Programming in HTML5 with JavaScript and CSS3

.

Planning WAY Ahead… Tech-Ed 2011!

Tech-Ed 2010 is over today… and I missed out this year  :-(

But,

Tech-Ed 2011 has been scheduled!

May 16-19 2011  in  Atlanta

 

Sign up for EARLY discounts by June 30th, 2010

http://northamerica.msteched.com/registration

 

Home page: http://northamerica.msteched.com

 

See you there!

 

.

SharePoint 2010: SharePoint Server Databases

 

 

The following is a list of databases installed in a “Basic” install of SharePoint Server 2010.

 

Notes:

• SQL Server Express 2008 Service Pack 1 was installed
  

     Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
         Mar 29 2009 10:11:52
         Copyright (c) 1988-2008 Microsoft Corporation
         Express Edition (64-bit) on
          Windows NT 6.1 <X64> (Build 7600: ) (VM)

• If you want to install the SQL Express Management Tools:
  • remember to download the 64 bit version
  • check out this article or you won’t guess the correct choice (New Installation, not Add new features) http://www.asql.biz/Articoli/SQLX08/Art3_1.aspx
  • the SQL Profiler is not part of the Express tools
    
• A new instance was created named SHAREPOINT  (yourservername.SHAREPOINT). The instance was created in
  C:\Program Files\Microsoft SQL Server\MSSQL10.SHAREPOINT
  
• The databases were created in:
  C:\Program Files\Microsoft Office Servers\14.0\Data\MSSQL10.SHAREPOINT\MSSQL\DATA
  (Foundation defaults to here: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Data\MSSQL10.SHAREPOINT\MSSQL\DATA)
  

Database Names:

• The “system” databases are named with GUIDs. We used to avoid this, or rename these, but it’s probably best to now leave these alone.
  
• In SharePoint Foundation 2010 these include:
• Bdc_Service_DB_guid   -  Business Connectivity Services (BCS)  (the old BDC)
• SharePoint_AdminContent_guid  - Central Administration
• SharePoint_Config_guid – the SharePoint Configuration database
  
• In SharePoint Server 2010 they add:
• Application_Registry_Service_DB_guid –
• Managed Metadata Service_guid –
• PerformancePoint Service Application_guid –
• Search_Service_Application_CrawlStoreDB_guid –
• Search_Service_Application_DB_guid –
• Search_Service_Application_PropertyStoreDB_guid –
• Secure_Store_Service_DB_guid –
• StateService_guid –
• User Profile Service Application_ProfileDB_guid –
• User Profile Service Application_SocialDB_guid –
• User Profile Service Application_SyncDB_guid –
• WebAnalyticsServiceApplication_ReportingDB_guid –
• WebAnalyticsServiceApplication_StagingDB_guid –
• Word Automation Services_guid –
  
• The content databases follow the 2007 pattern: WSS_Content or what ever you enter when you create a new application. (and I thought the “WSS” name was gone!)
  
• WSS_Search_yourservername – The WSS search database
  
• The new logging database! WSS_Logging. Think of this one as the one SharePoint database you are supposed to do direct TSQL queries from! As a teaser… here’s a list of the views (I’d show the tables, but each has 32 partitions): 
                                                

Files from my install (your GUIDs will vary)

   

  Databases and log files in SharePoint Foundation 2010:

• Bdc_Service_DB_778ccd3e047d45c1b71529c004ebbd2c.mdf
  Bdc_Service_DB_778ccd3e047d45c1b71529c004ebbd2c_log.LDF
  
• SharePoint_AdminContent_4ab2813a-fe77-4085-86df-41d3a79df116.mdf
  SharePoint_AdminContent_4ab2813a-fe77-4085-86df-41d3a79df116_log.LDF
  
• SharePoint_Config_e5403261-4d7a-4b9e-bef2-286c46b588da.mdf
  SharePoint_Config_e5403261-4d7a-4b9e-bef2-286c46b588da_log.LDF
  
• WSS_Content.mdf
  WSS_Content_log.LDF
  
• WSS_Logging.mdf
  WSS_Logging_log.LDF
  
• WSS_Search_MAXSP2010BETA2.mdf
  WSS_Search_MAXSP2010BETA2_log.LDF

   Additional databases and log files in SharePoint Server 2010:

• Application_Registry_Service_DB_b669b0a586ce4cfaa1020e9e75824843.mdf
  Application_Registry_Service_DB_b669b0a586ce4cfaa1020e9e75824843_log.LDF
  
• Managed Metadata Service_6b696a9bda574b29a0bba4009c782316.mdf
  Managed Metadata Service_6b696a9bda574b29a0bba4009c782316_log.LDF
  
• PerformancePoint Service Application_106ff2206d784297adbd4ec157aef7b5.mdf
  PerformancePoint Service Application_106ff2206d784297adbd4ec157aef7b5_log.LDF
  
• Search_Service_Application_CrawlStoreDB_16d445d62e314cd58c82d75fc304a225.mdf
  Search_Service_Application_CrawlStoreDB_16d445d62e314cd58c82d75fc304a225_log.LDF
  
• Search_Service_Application_DB_e172668d1a534fd981b53146014d5ef3.mdf
  Search_Service_Application_DB_e172668d1a534fd981b53146014d5ef3_log.LDF
  
• Search_Service_Application_PropertyStoreDB_10c4d562200442748ca6339d4b358c84.mdf
  Search_Service_Application_PropertyStoreDB_10c4d562200442748ca6339d4b358c84_log.LDF
  
• Secure_Store_Service_DB_edd7b8e261fa491bb336779301aa5bf9.mdf
  Secure_Store_Service_DB_edd7b8e261fa491bb336779301aa5bf9_log.LDF
  
• StateService_335919bb65d1430aa20424a67a99589e.mdf
  StateService_335919bb65d1430aa20424a67a99589e_log.LDF
  
• User Profile Service Application_ProfileDB_9e421f2d762c46609a8a15fab15a3d52.mdf
  User Profile Service Application_ProfileDB_9e421f2d762c46609a8a15fab15a3d52_log.LDF
  
• User Profile Service Application_SocialDB_81269e32afd049d48994a72362806da4.mdf
  User Profile Service Application_SocialDB_81269e32afd049d48994a72362806da4_log.LDF
  
• User Profile Service Application_SyncDB_8c10242fd8064682aa1ac620acfa1733.mdf
  User Profile Service Application_SyncDB_8c10242fd8064682aa1ac620acfa1733_log.LDF
  
• WebAnalyticsServiceApplication_ReportingDB_1684bae4-0271-40f0-9e4d-84c6660e99e0.mdf
  WebAnalyticsServiceApplication_ReportingDB_1684bae4-0271-40f0-9e4d-84c6660e99e0_log.LDF
  
• WebAnalyticsServiceApplication_StagingDB_13d64d79-9237-492c-ad2d-48785d13a1ff.mdf
  WebAnalyticsServiceApplication_StagingDB_13d64d79-9237-492c-ad2d-48785d13a1ff_log.LDF
  
• Word Automation Services_5fd65b951cf1499eaa72997b9668fd04.mdf
  Word Automation Services_5fd65b951cf1499eaa72997b9668fd04_log.LDF
  

 

  There are also the standard SQL Server databases there:

• master.mdf
• mastlog.ldf
• model.mdf
• modellog.ldf
• MSDBData.mdf
• MSDBLog.ldf
• tempdb.mdf
• templog.ldf

   And this file:

• MS_AgentSigningCertificate.cer
  

.

SharePoint 2010: SharePoint Foundation Databases

 

The following is a list of databases installed in a “Basic” install of SharePoint Foundation 2010.

 

Notes:

• SQL Server Express 2008 Service Pack 1 was installed
  

   Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
       Mar 29 2009 10:11:52
       Copyright (c) 1988-2008 Microsoft Corporation
       Express Edition (64-bit) on
        Windows NT 6.1 <X64> (Build 7600: ) (VM)

• If you want to install the SQL Express Management Tools:
• remember to download the 64 bit version
• check out this article or you won’t guess the correct choice (New Installation, not Add new features) http://www.asql.biz/Articoli/SQLX08/Art3_1.aspx
• the SQL Profiler is not part of the Express tools
  
• A new instance was created named SHAREPOINT  (yourservername.SHAREPOINT). The instance was created in
  C:\Program Files\Microsoft SQL Server\MSSQL10.SHAREPOINT
  
• The databases were created in:
  C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Data\MSSQL10.SHAREPOINT\MSSQL\DATA
  

Database Names:

• The “system” databases are named with GUIDs. We used to avoid this, or rename these, but it’s probably best to now leave these alone.
  In SharePoint Foundation these include:
• Bdc_Service_DB_guid   -  Business Connectivity Services (BCS)  (the old BDC)
• SharePoint_AdminContent_guid  - Central Administration
• SharePoint_Config_guid – the SharePoint Configuration database
  
• The content databases follow the 2007 pattern: WSS_Content or what ever you enter when you create a new application. (and I thought the “WSS” name was gone!)
  
• WSS_Search_yourservername – The WSS search database
  
• The new logging database! WSS_Logging. Think of this one as the one SharePoint database you are supposed to do direct TSQL queries from! As a teaser… here’s a list of the views (I’d show the tables, but each has 32 partitions):
                                                

Files from my install (your GUIDs will vary)

      

  Databases and log files:

• Bdc_Service_DB_778ccd3e047d45c1b71529c004ebbd2c.mdf
  Bdc_Service_DB_778ccd3e047d45c1b71529c004ebbd2c_log.LDF
  
• SharePoint_AdminContent_4ab2813a-fe77-4085-86df-41d3a79df116.mdf
  SharePoint_AdminContent_4ab2813a-fe77-4085-86df-41d3a79df116_log.LDF
  
• SharePoint_Config_e5403261-4d7a-4b9e-bef2-286c46b588da.mdf
  SharePoint_Config_e5403261-4d7a-4b9e-bef2-286c46b588da_log.LDF
  
• WSS_Content.mdf
  WSS_Content_log.LDF
  
• WSS_Logging.mdf
  WSS_Logging_log.LDF
  
• WSS_Search_MAXSP2010BETA2.mdf
  WSS_Search_MAXSP2010BETA2_log.LDF

 

  There are also the standard SQL Server databases there:

• master.mdf
• mastlog.ldf
• model.mdf
• modellog.ldf
• MSDBData.mdf
• MSDBLog.ldf
• tempdb.mdf
• templog.ldf

 

  And this file:

• MS_AgentSigningCertificate.cer
  

 

 

Exam Discounts

This is a note for people who have attended my classes. (Everyone else go see their own MCT!)

If you are thinking about Microsoft certification, check with me before signing up for an exam. I can usually get you a discount on both the exam and on practice exams.

Currently:

• 10% off of the exam
• 40% on any MeasureUp 60 Day Online Practice Test

Not all exams qualify, but most MCP exams do. MeasureUp does not have a practice test of every Microsoft exam.

If you would like a discount voucher, email me at the email address I gave you during class or just call MAX Technical Training.

 

Know any college or tech school students? They can 55% off of exams here:
http://www.prometric.com/microsoft/student

.

SQL: Sample CLR function for a Regular Expression validation

 

Here is a quick example of a .net CLR function to validate any string value using a regular expression. (only works with SQL 2005 and later of course)

1) create a new project using the C# or VB "SQL Server Project" template

2) add a New Item - a function

3) add this code!

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean RegExpression(string strToTest, string regExpression)
    {
        return Regex.IsMatch(strToTest,regExpression);
    }
};

4) Deploy and test!

 

Samples of how to call:

Test a phone number:

print dbo.RegExpression('223-123-1234','^[2-9]\d{2}-\d{3}-\d{4}$')

Test an email address:

print dbo.RegExpression('abc@maxtrain.com', '^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$')

 

Test a column of phone numbers:

use AdventureWorks
select phone, dbo.RegExpression(phone,'^[2-9]\d{2}-\d{3}-\d{4}$') as validphone  from person.contact

Renaming a Server Breaks SQL Server!

If you've not run across this one yet, you will one of these days. It's been around for a long time and is pretty easy to fix by rerunning Setup. How does your server get renamed?

• The obvious answer: you did it! You renamed the server.
• No so obvious: you installed SharePoint on a test server using local accounts and then decided to add Active Directory or join the server to a domain so you can play with all of the email features. (typical error code in the Event viewer is 17204)

Just Re-run Setup...

To fix it just rerun SQL Setup and let it repair the installation. But what about Microsoft Sql Server Embedded Edition (SSEE), the free, default and well hidden edition of SQL server that does not come with a setup program? Several Knowledgebase articles describe how to do this with a command line tool, but they never tell you enough. Here's one: http://support.microsoft.com/kb/929665 They show you this line: Msiexec CALLERID=OCSetup.exe REINSTALL=ALL REINSTALLMODE=omus /qn REBOOT=ReallySupress /l*v But they don't tell you the MSI_File_Name and it leaves out one switch. Here's one that worked for me: Msiexec /i "C:\Program Files\MSECache\wssv3\database\SSEE_10.msi" CALLERID=OCSetup.exe REINSTALL=ALL REINSTALLMODE=omus /qn REBOOT=ReallySupress /l*v c:\log.txt Note: If you are doing this to fix a SharePoint problem remember to run the SharePoint Products and Technologies Configuration Wizard to let it fix the internal database names. Some notes on SSEE:

• Does not include any management tools, but you can connect to it using SQL Server Studio (regular or Express) with this "server name": \\.\pipe\mssql$microsoft##ssee\sql\query
• It lives here: C:\WINDOWS\SYSMSI
• The databases are here by default: C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data

Which Conference or Event to Attend in 2008?

Too many choices! So little time and money... DevConnections? April 20-23 Orlando, FL 5 shows in 1! SharePoint Connections, ASP.Net Connections, SQL Server Connections, Architect Connections, Visual Studio & .Net Connections. http://www.devconnections.com/ Microsoft Office System developer conference? Feb 10-13 2008 San Jose, California https://microsoft.crgevents.com/ODC2008/ SharePoint Conference 2008 March 2-6 Seattle, Washington http://mssharepointconference.com Can't decide between Office Developer Conference 2008 vs. SharePoint Conference 2008? Check out this SharePoint Product Group blog SharePoint Information Worker Conference 2008 Feb 2-6 Nashville, TN http://sharepointsolutions.com/sharepoint-conferences/sharepoint-conference.html TechEd 2008 Orlando, FL Oh No! There are two of them this year! Tech·Ed U.S. 2008 Developers June 3-6, 2008 Tech·Ed U.S. IT Professionals June 10-13, 2008 http://www.microsoft.com/events/teched2007/default.mspx Microsoft Office Visio Conference 2008 Feburary 5-6 Redmond, WA http://www.msvisioconference.com/

SQL Links

Misc SQL links from my favorites list... SQL Injection links http://www.spidynamics.com/whitepapers/WhitepaperSQLInjection.pdf http://www.spidynamics.com/whitepapers/Blind_SQLInjection.pdf http://www.imperva.com/application_defense_center/white_papers/blind_sql_server_injection.html and more... http://www.google.com/search?hl=en%26q=sql%2Binjection%2Bwhite%2Bpaper Links: http://www.microsoft.com/sql/ http://www.sqldbatips.com/ http://www.sqlsecurity.com/ http://www.aspfaq.com/sql2005/ QA shortcut keys! http://searchsqlserver.techtarget.com/ (or just http://searchsqlserver.com/) http://www.microsoft.com/sql/community/default.mspx SQL Server Community (BLOGs etc) http://www.google.com/advanced_group_search?q=group:microsoft.public.sqlserver.* Google News group search More... http://www.sqlteam.com/ http://sqlxml.org/ http://sqldev.net/ http://www.sqlpass.org/ Professional Association for SQL Server (Basic PASS Membership - FREE) http://www.carlprothman.net/Default.aspx?tabid=81 (Connect strings!) http://www.connectionstrings.com/ http://www.winnetmag.com/SQLServer/ SQL Server Magazine • Password cracking tools for SQL Server: SearchSQLServer.com (May 9, 2006) http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1187282_tax301336,00.html?track=NL-416&ad=550187&adg=301324&bucket=ETA http://www.sqldbatips.com/showarticle.asp?ID=46 SQL 2005 Service Manager With the lack of an equivalent in SQL2005 for the SQL Server Service Manager that comes with SQL2000, I've decided to write one myself. It's behaviour should be familiar to you if you've used SQL2000. Ten hacker tricks to exploit SQL Server systems http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1165052_tax301336,00.html?Offer=SQLwnha217 Reporting Services Scripter is a .NET Windows Forms application that enables scripting of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules) and server side report parameters http://www.sqldbatips.com/showarticle.asp?ID=62 --- Counters (SQL 2000) http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx SQL Server Best Practices http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx Predeployment I/O Best Practices http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx Troubleshooting Performance Problems in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx SQL Server 2005 Waits and Queues http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx Top 10 Performance Monitor counters http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1111751_tax301334,00.html