Joseph Michael Pesch
VP Programming

Sharepoint - User Profile Synchronization Proccess

by 9. April 2013 10:52

When the user information is synchronized from Active Directory (AD) into Sharepoint there are two basic processes. 

First, User Profile Service Application running under the Sharepoint Services on Server is what actually imports data from AD into Sharepoint.  This can be customized to select specific AD attributes and can also be configured to export data back from Sharepoint to Acitve Directory.  This could be useful if you want to allow Sharepoint users to update selected attributes directly in Sharepoint and publish them back to Active Directory.  You can view the current status of an active synchronization by using the Forefront Identity Manager (located at [InstatllationDrive]:\Program Files\Microsoft Office Servers\14.0\Synchronization Service\UIShell\miisclient.exe).  The Sharepoint Service relies on the "Forefront Identity Manager Service" which must also be running on the Sharepoint server that is running the synchronization service.

That process simply creates, updates and deletes the records in the Sharepoint User Database by inspecting the current state of Active Directory.  To have the information visible in the Sharepoint People search and liked visually to organizational heirarchy it must also be propogated into the corresponding Sharepoint content databases.  That portion of the process occurs through the User Profile Sync Timer Job (running under the Sharepoint Timer service) which typically runs at the top of each hour.

IMPORTANT NOTE: If you use the People Picker in Sharepoint to locate user it will locate users directly in Active Directory (i.e. even if they have not been synchronized or setup in Sharepoint previously).  If you then add a user to a library, list, etc. (i.e. grant them permissions, add to a group, etc.) they Sharepoint will create a user stub record in the User Database with minimal data (basically just the user id and guid).  Next time the synchronization process runs it will update all the additional user attributes based on how the service is configured.  In my case the synchronization process was failing so there was some confusion as to how some users were getting into the Sharepoint environment with minimal data (i.e. they were getting added when they were granted permissions to resources in Sharepoint).

A couple of maintenance items that can help if these processes start failing:

1) Clearing the User Profile Sync Table - Over time the data in this table will get corrupted specifically when dropping and attaching databases this is a known issue.  In order to see a list of the entries you can use the listolddatabases commande below.  To clean up the corrupted data you can run the deleteolddatabases command below which will delete all entries that have not been successfully updated since 1 day ago (i.e. the last parameter is the number of days since last update).

STSADM -o sync -listolddatabases 0
STSADM -o sync -deleteolddatabases 1

2) Clear the configuration cache on the servers in the Sharepoint server farm:

To resolve this issue, clear the file system cache on all servers in the server farm on which the Windows SharePoint Services Timer service is running. To do this, follow these steps:
  1. Stop the Timer service. To do this, follow these steps:
    1. Click Start, point to Administrative Tools, and then click Services.
    2. Right-click Windows SharePoint Services Timer, and then click Stop.
    3. Close the Services console.
  2. On the computer that is running Microsoft Office SharePoint Server 2007 and on which the Central Administration site is hosted, click Start, click Run, type explorer, and then press ENTER.
  3. In Windows Explorer, locate and then double-click the following folder:
    Drive:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\GUID
    Notes
    • The Drive placeholder specifies the letter of the drive on which Windows is installed. By default, Windows is installed on drive C.
    • The GUID placeholder specifies the GUID folder.
    • The Application Data folder may be hidden. To view the hidden folder, follow these steps:
      1. On the Tools menu, click Folder Options.
      2. Click the View tab.
      3. In the Advanced settings list, click Show hidden files and folders under Hidden files and folders, and then click OK.
    • In Windows Server 2008, the configuration cache is in the following location:
      Drive:\ProgramData\Microsoft\SharePoint\Config\GUID
  4. Back up the Cache.ini file.
  5. Delete all the XML configuration files in the GUID folder. Do this so that you can verify that the GUID folder is replaced by new XML configuration files when the cache is rebuilt.

    Note When you empty the configuration cache in the GUID folder, make sure that you do not delete the GUID folder and the Cache.ini file that is located in the GUID folder.
  6. Double-click the Cache.ini file.
  7. On the Edit menu, click Select All.
  8. On the Edit menu, click Delete.
  9. Type 1, and then click Save on the File menu.
  10. On the File menu, click Exit.
  11. Start the Timer service. To do this, follow these steps:
    1. Click Start, point to Administrative Tools, and then click Services.
    2. Right-click Windows SharePoint Services Timer, and then click Start.
    3. Close the Services console.
    Note The file system cache is re-created after you perform this procedure. Make sure that you perform this procedure on all servers in the server farm.
  12. Make sure that the Cache.ini file has been updated. For example it should no longer be 1 if the cache has been updated.
  13. Click Start, point to Programs, point to Administrative Tools, and then click SharePoint 3.0 Central Administration.
  14. Click the Operations tab, and then click Timer job status under Global Configuration.
  15. In the list of timer jobs, verify that the status of the Config Refresh entry is Succeeded.
  16. On the File menu, click Close.
Original link to these clean up steps: http://support.microsoft.com/kb/939308

Tags:

Sharepoint

Image Sliders

by 31. March 2013 00:35

http://galleria.io/

http://css-tricks.com/creating-a-slick-auto-playing-featured-content-slider/

 

 

Tags:

Determine Authentication Scheme on SQL Server

by 22. March 2013 14:12

Below is a TSQL statement that will return the current authentication scheme running on your SQL server instance.

SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Tags:

SQL Server

Microsoft Fix It - Clean Removal Tool Replacement for MSICUU2.exe

by 24. February 2013 09:13

Some times when a software becomes corrupted and you attempt to uninstall/reinstall the software it may be in left in a bad state where it is unusable, leaving you with the only option to reformat your hard drive to reinstall your operating system or attempt to manually fix/unistall which can be very tedious and potentially error prone leaving your system in even a worse state.  The Windows Installer Clean Up tool developed by Microsoft "MSICUU2.exe" was a free and very useful tool to help fix these type of program removal/reinstallation issues.  However, Microsoft decided to retire this tool in 2010 and they have replaced it with another tool called “Microsoft Fix It” which is a progam install and uninstall utility that supports both 32-bit and 64-bit of Windows including Windows 7.  The tool can be run directly from the Microsoft web site; or, it can be downloaded and loaded onto a flash drive, CD, etc. to run locally on an affected machine.

In my case I had Symantec anti-virus software installed and attempted to remove it using the standard Windows Add/Remove Programs from the Control Panel.  However, my uninstall froze in the middle and left my PC in a state where it was gone from the Add/Remove Programs list but not fully removed from my PC.  Worse yet whenever I right-clicked on folders it would prompt me with a series of dialog boxes about trying to install Symantec and can't install.  I was able to successfully remove it using the Microsoft Fix It tool.

Download the tool directly from the Microsoft site: http://support.microsoft.com/mats/Program_Install_and_Uninstall

Tags:

Windows

ASP.Net Security Create New User Account Based on Existing User Account

by 1. February 2013 07:39

This SQL script will create a new ASP.Net user account using an existing user account as the basis.  The new user account will have the same password and roles as the existing user account.  Also included at the end of the main script is a script to change a user account password to a known defualt (e.g. Password1234).


Script to replicate user:

declare 
  @ExisitingUserName varchar(50)
, @ExisitingUserEmail
, @OldUserID uniqueidentifier
, @UserID uniqueidentifier
, @Email varchar(50)
, @UserName varchar(50)
, @DeleteIfExists bit

select
  @UserID = newid()
, @Email = 'new.user@email.com'
, @UserName = 'new.user@email.com'
, @DeleteIfExists = 0
, @ExisitingUserName = 'EXISTING_USER_NAME_HERE'
select
  @UserID
, @Email
, @UserName

select @OldUserID = UserID 
from dbo.aspnet_Users 
where UserName = @UserName

if @DeleteIfExists = 1 begin

delete from dbo.aspnet_UsersInRoles
where UserID = @OldUserID

delete from dbo.aspnet_Membership
where UserID = @OldUserID

delete from dbo.aspnet_Users 
where UserName = @UserName

end

insert into dbo.aspnet_Users
select
  ApplicationId
, @UserID
, @UserName
, lower(@UserName) /* LoweredUserName */
, null /* MobileAlias */
, 0 /* IsAnonymous */ 
, getdate() /* LastActivityDate */
from dbo.aspnet_Users
where UserName = @ExisitingUserName

insert into dbo.aspnet_Membership
select
  ApplicationId
, @UserID
, Password
, PasswordFormat
, PasswordSalt
, MobilePIN
, @Email
, @Email
, PasswordQuestion
, PasswordAnswer
, IsApproved
, IsLockedOut
, getdate() /* CreateDate */
, getdate() /* LastLoginDate */
, getdate() /* LastPasswordChangedDate */
, '1/1/1754 12:00:00 AM' /* LastLockoutDate */
, 0 /* FailedPasswordAttemptCount */
, '1/1/1754 12:00:00 AM' /* FailedPasswordAttemptWindowStart */
, 0 /* FailedPasswordAnswerAttemptCount */
, '1/1/1754 12:00:00 AM' /* FailedPasswordAnswerAttemptWindowStart */
, Comment
from aspnet_Membership
where Email = @ExisitingUserEmail

insert into dbo.aspnet_UsersInRoles
select @UserID, RoleID
from dbo.aspnet_Roles

Script to change password:

update dbo.aspnet_Membership 
set PasswordSalt = 'dArJQD4e4upwMyqCSl9iNA=='
, Password = '1mAUj77CySFAtqlfInt4UFOznAc=' /* Password1234 */
where UserId = 
(
  select UserId 
  from dbo.aspnet_Users 
  where UserName = 'USER_NAME_HERE'
)

Tags:

ASP.Net | SQL Server

ASP.Net Membership Provider and Identity Login Password Hash with Salt/Security

by 9. January 2013 14:12

Here is a password for ASP.Net MembershipProvider
password: password1234
password salt: dArJQD4e4upwMyqCSl9iNA==
password: 5Xv212dMJiOQP2X+zhOZw66L+xU=

Here is a password for ASP.Net Identity
password: #%ND84yq
hash: AGRf+1XUhLtQsQnaT39V4sQegMmKvA7gsLBVtJ4YFLdvavNQFcqeHZ+ViuRoO9SkRg==
security: 86232225-81da-4d56-84ea-ded079d64384

Tags:

ASP.Net

SQL Read Sharepoint Document Content for SSRS Reports

by 6. December 2012 09:43

I had a case where I needed to review a few hundred SSRS reports installed on a Sharepoint 2010 envrionment.  Below is a SQL script I created to read the content from the underlying SQL database and search the report structure for certain keywords (in my case I was looking for specific database/table names in the report SQL).

create table #tmp(name varchar(150), content varchar(max))
/* Get cursor of all SSRS report (*.rdl) files to cycle through */
declare cur cursor for 
select Id, LeafName, InternalVersion 
from AllDocs where LeafName like '%.rdl'
declare @id varchar(50), @name varchar(200), @version int
open cur
while 1=1 begin
  fetch next from cur into @id, @name, @version
  if @@FETCH_STATUS <> 0 break
  /* Check is current version of the report contains specific keywords */
  if exists
  (
    select 1 
	from WSS_CONTENT_SITENAMEHERE.dbo.AllDocStreams 
	where Id = @id 
	and InternalVersion = @version 
	and 
	(
	  cast(Content as varchar(max)) like '%KEYWORD #1 HERE%' 
	  or 
	  cast(Content as varchar(max)) like '%KEYWORD #2 HERE%')
	)  
    insert into #tmp 
    select @name, cast(Content as varchar(max)) 
    from WSS_CONTENT_SITENAMEHERE.dbo.AllDocStreams 
    where Id = @id and InternalVersion = @version
end
close cur
deallocate cur
select * from #tmp
drop table #tmp

Tags:

Sharepoint | SQL Server

C# Get First and Last Day of Previous Month

by 3. December 2012 08:25

To get the first day of previous month:

var firstDayPrevMonth = new DateTime(DateTime.Today.Year
  , DateTime.Today.Month, 1).AddMonths(-1);

To get the last day of previous month:

var lastDayPrevMonth = new DateTime(DateTime.Today.Year
  , DateTime.Today.Month, 1).AddDays(-1);

 

Tags:

C#

Lenovo T500 Display Brightness Not Working After Installing Windows 7 64bit

by 11. November 2012 11:41

One option that may work is BIOS update:

http://support.lenovo.com/en_US/detail.page?LegacyDocID=MIGR-70353

The other is installing the ATI ThinkPad Switchable Graphics Driver for Windows 7 (32-bit, 64-bit):

http://support.lenovo.com/en_US/detail.page?LegacyDocID=MIGR-73641

Tags:

SQL Script to Shrink Database Transaction Log Files

by 26. October 2012 15:53

This script will operate on all databases that are ONLINE.  Will set recovery mode to SIMPLE temporarily shrink the log file then set recovery mode back to FULL.  In the case where the database recovery mode is already SIMPLE it will simply shrink the log file and leave the recovery mode set to SIMPLE.

declare @db varchar(255)
, @rcm varchar(25)
, @log varchar(255)
, @sql varchar(max) 

create table #tmp (db varchar(255), rcm varchar(25), lg varchar(255))
declare cur cursor for
select name, recovery_model_desc
/* convert(varchar(25), databasepropertyex(names, 'RECOVERY')) */
from sys.databases
where name not in('master', 'tempdb', 'model', 'msdb', 'distribution')
and state_desc = 'ONLINE' /* Skip anything OFFLINE */
open cur
while 1=1 begin
  fetch next from cur into @db, @rcm
  if @@FETCH_STATUS <> 0 break
  set @sql = 'use [' + @db + '] insert into #tmp'
      + ' select ''' + @db + ''', ''' + @rcm + ''''
      + ', name from sys.sysfiles where fileid = 2'
  exec(@sql) 
end
deallocate cur

declare cur cursor for
select * from #tmp
open cur
while 1=1 begin
  fetch next from cur into @db, @rcm, @log
  if @@FETCH_STATUS <> 0 break
  if @rcm = 'FULL' /* When FULL we will set simple then back to FULL */
    select @sql = 'use [' + @db + ']'
      + ' alter database [' + @db + '] set recovery simple with no_wait'
      + ' dbcc shrinkfile (N''' + @log + ''', 1) '
      + ' alter database [' + @db + '] set recovery full with no_wait'
   else /* When already SIMPLE just shrink and leave it alone */
    select @sql = 'use [' + @db + ']'
      + ' dbcc shrinkfile (N''' + @log + ''', 1) '
  exec(@sql)
end

drop table #tmp

Tags:

SQL Server

SQL Script to Shrink Database Transaction Log Files

by 26. October 2012 15:40

This script will operate on all databases that are ONLINE.  Will set recovery mode to SIMPLE temporarily shrink the log file then set recovery mode back to FULL.  In the case where the database recovery mode is already SIMPLE it will simply shrink the log file and leave the recovery mode set to SIMPLE.

declare @db varchar(255)
, @rcm varchar(25)
, @log varchar(255)
, @sql varchar(max) 

create table #tmp (db varchar(255), rcm varchar(25), lg varchar(255))
declare cur cursor for
select name, recovery_model_desc
/* convert(varchar(25), databasepropertyex(names, 'RECOVERY')) */
from sys.databases
where name not in('master', 'tempdb', 'model', 'msdb', 'distribution')
and state_desc = 'ONLINE' /* Skip anything OFFLINE */
open cur
while 1=1 begin
  fetch next from cur into @db, @rcm
  if @@FETCH_STATUS <> 0 break
  set @sql = 'use [' + @db + '] insert into #tmp'
      + ' select ''' + @db + ''', ''' + @rcm + ''''
      + ', name from sys.sysfiles where fileid = 2'
  exec(@sql) 
end
deallocate cur

declare cur cursor for
select * from #tmp
open cur
while 1=1 begin
  fetch next from cur into @db, @rcm, @log
  if @@FETCH_STATUS <> 0 break
  if @rcm = 'FULL' /* When FULL we will set simple then back to FULL */
    select @sql = 'use [' + @db + ']'
      + ' alter database [' + @db + '] set recovery simple with no_wait'
      + ' dbcc shrinkfile (N''' + @log + ''', 1) '
      + ' alter database [' + @db + '] set recovery full with no_wait'
   else /* When already SIMPLE just shrink and leave it alone */
    select @sql = 'use [' + @db + ']'
      + ' dbcc shrinkfile (N''' + @log + ''', 1) '
  exec(@sql)
end

drop table #tmp


Tags:

SQL Server

Excel Services in SharePoint 2010 Error: This workbook is larger than the maximum workbook size allowed to be opened in the browser

by 24. October 2012 08:10

By default the maximum file size that can be opened in the browser (by Excel services) is 10MB.  This is managed through the Central Administration > Application Management > Manage Service Applications > Excel Services > Trusted File Locations.  Below is an excerpt from Microsofts documentation on this topic located here: http://technet.microsoft.com/en-us/library/ff191194.aspx  When you attempt to open a file larger than this setting allows you will receive this error message: "This workbook is larger than the maximum workbook size allowed to be opened in the browser"

In the Workbook Properties section, you determine the resource capacity for the server when Excel Services opens workbooks.

Performance and resource availability can be compromised if users open extensive workbooks. If you do not manage the approved size that workbooks running in open Excel Calculation Services sessions can have, you risk having users exceed your resource capacity and overloading the server.

  1. In the Maximum Workbook Size box, type a value in megabytes (MB) for the maximum size of workbooks that Excel Calculation Services can open. The default size is 10 megabytes. 

Tags:

Sharepoint

SQL Server Query to Gather Individual Table Space Used

by 22. October 2012 16:29

 

select

  tablename = o.name

, totalpages = sum(a.total_pages)

, usedpages = sum(a.used_pages)

, pages = sum(case when a.type <> 1 then a.used_pages

when p.index_id < 2 then a.data_pages else 0 end)

, SUM(a.used_pages)*8096 AS 'Size(B)'

, rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)

from sys.objects o

join sys.partitions p on p.object_id = o.object_id

join sys.allocation_units a on p.partition_id = a.container_id

where o.type = 'U'

group by o.name

order by usedpages desc

 

Tags:

SQL Server

Windows Command ForFiles to Search Folders/Sub-Folders and Output File Details

by 22. October 2012 12:23

Below is a sample command to search a folder (/p) and all subfolders (/s) for files mathing *.zip (/m)
and run echo command (/c) to print the filename, size, date and time to a text file

forfiles /p c:\foldertosearch /s /m *.zip /c "cmd /c echo @file;@fsize;@fdate @ftime" > output.txt

See Microsoft site for full usage of forfiles:
http://technet.microsoft.com/en-us/library/cc753551(v=ws.10).aspx

Tags:

Windows

ASP.Net C# Export GridView Control Data to Excel File

by 22. October 2012 08:44

This is a simple method to export data directly from an ASP.Net GridView control into an Excel file (CSV formatted).

protected void ExportGridViewDataButton_Click(object sender, EventArgs e)
{
  StringBuilder sb = new StringBuilder();
  foreach (TableCell cel in this.GridViewControl.HeaderRow.Cells)
  {
    sb.Append("\"").Append(cel.Text.Replace(" ", "")).Append("\",");
  }
  sb.Append(((Char)10).ToString());
  foreach (GridViewRow row in this.GridViewControl.Rows)
  {
    foreach (TableCell cel in row.Cells)
    {
      sb.Append("\"").Append(cel.Text.Replace(" ", "")).Append("\",");
    }
    sb.Append(((Char)10).ToString());
  }
  Response.Clear();
  Response.AddHeader("Content-Disposition"
    , "attachment;filename=GridViewExport.csv");
  Response.ContentType = "text/csv";
  Response.Write(sb.ToString());
  Response.Flush();
  Response.End();
}

Tags:

C# | Excel

Combine Binary Files Using C#

by 19. October 2012 09:07
using (var fs = File.OpenWrite("combined.bin"))
{
  var buffer = File.ReadAllBytes("file1.bin");
  fs.Write(buffer, 0, buffer.Length);
  buffer = File.ReadAllBytes("file2.bin");
  fs.Write(buffer, 0, buffer.Length);
  fs.Flush();
}

Tags:

C#

Sys Undefined Exception in ASP.Net Web Form Menu Control Generated JS with URL Routing

by 16. October 2012 08:07

Although the exception only seems to occur when using URL routing it is not consistent (i.e. doesn't always happen).

The page generates this line at the end of the Form tag:

new Sys.WebForms.Menu({ element: 'Navigation Menu', disappearAfter: 500, orientation: 'horizontal', tabIndex: 0, disabled: false });

Throws JS runtime exception:

'Sys' is undefined

Solution:

void Application_Start(object sender, EventArgs e)
{
  RouteTable.Routes.Ignore("{resource}.axd");
}

Or:

  
<system.web>
  <pages controlRenderingCompatibilityVersion="3.5"/>
</system.web>

Tags:

ASP.Net | C# | JavaScript

WCF Web Service XML Sample

by 24. September 2012 11:29
This is some sample code demonstrating a WCF web service with XML based class as an input parameter.
// Shared class...

public class MySampleData
{
  public string UserId { get; set; }
  public Guid RecordId { get; set; }
  public string RecordDesc { get; set; }
  public decimal DataAmount { get; set; }
  public DateTime EffectiveDate { get; set; }
}

// Sample usage...

using System.IO;
using System.Net;
using System.Xml;
using System.Xml.Serialization;

private void SampleUsage()
{
  MySampleData data = new MySampleData()
  {
    UserId = "myuser",
    RecordId = Guid.NewGuid(),
    RecordDesc = "This is a test",
    DataAmount = 100.99,
    EffectiveDate = System.DateTime.Now.AddDays(5)
  };
  string msg = SaveMySampleData(data);
  System.Diagnostics.Debug.WriteLine(msg);
}

// Caller initiating web service transaction...
private void SaveMySampleData(MySampleData data)
{
  XmlSerializer serializer = new XmlSerializer(data.GetType());
  MemoryStream ms = new MemoryStream();
  serializer.Serialize(ms, data);
  StreamReader rdr = new StreamReader(ms);
  ms.Position = 0;
  HttpWebRequest req = (HttpWebRequest)HttpWebRequest
    .Create("http://localhost:43653/MySampleService.svc/SaveMySampleData");
  req.Method = "POST";
  req.ContentType = "application/xml; charset=utf-8";
  byte[] reqBodyBytes = ms.ToArray();
  Stream reqStream = req.GetRequestStream();
  reqStream.Write(reqBodyBytes, 0, reqBodyBytes.Length);
  reqStream.Close();
  HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
  XmlDocument xml = new XmlDocument();
  xml.LoadXml(new StreamReader(resp.GetResponseStream()).ReadToEnd());
  resp.Close();
}

// Web service code (i.e. MySampleService.svc file)

using System;
using System.IO;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Xml;
using System.Xml.Serialization;

[ServiceContract]
public class MySampleService
{
  [OperationContract(Name = "SaveMySampleData")]
  [WebInvoke(Method = "POST", UriTemplate = "SaveMySampleData",
              RequestFormat = WebMessageFormat.Xml,
              ResponseFormat = WebMessageFormat.Xml,
              BodyStyle = WebMessageBodyStyle.Bare)]
  public string SaveMySampleData(XmlElement input)
  {
    XmlSerializer serializer = new XmlSerializer(typeof(MySampleData));
    MySampleData data = (MySampleData)serializer.Deserialize
      (new MemoryStream(System.Text.Encoding.ASCII.GetBytes(input.OuterXml)));
    // Save to db here... just echoing data back for the sample...
    StringBuilder msg = new StringBuilder();
    msg.Append("You sent the following data.").Append(Environment.NewLine);
    msg.Append("UserId: ").Append(data.UserId).Append(Environment.NewLine);
    msg.Append("RecordId: ").Append(data.RecordId).Append(Environment.NewLine);
    msg.Append("RecordDesc: ").Append(data.RecordDesc).Append(Environment.NewLine);
    msg.Append("DataAmount: ").Append(data.DataAmount).Append(Environment.NewLine);
    msg.Append("EffectiveDate: ").Append(data.EffectiveDate.ToString()).Append(Environment.NewLine);
    return msg.ToString();
  }
}

Tags:

C# | Web Service | Windows Communication Foundation

AppFog Setup Application with MySQL Database and Connect to Database with MySQL Workbench

by 15. September 2012 18:01

This entry will walk through creating an AppFog application, linking a MySQL database to it and then connecting to the database with MySQL Workbench.  I will walk through creating the AppFog application and MySQL service, installing Ruby and the DevKit (toolkit that makes it easy to build and use native C/C++ extensions for Ruby on Windows).  In this case the extension we will be installing that requires the DevKit is Caldecott (which is used to create the MySQL TCP tunnel from the AppFog server to your Localhost).  My installation will focus on Windows 7 64bit operating system.

Step 0) Install MySQL Workbench from: http://www.mysql.com/downloads/workbench/ (the version I installed was: mysql-workbench-gpl-5.2.42-win32.msi).

Step 1) Create your AppFog account.  If you don't already have an AppFog account, signing up is quick an painless.  At the time of this post all they require to establish a new free account is an email address, password and company name.  My free account came with: Unlimited Apps (Java, .Net, Ruby, Node, Python, PHP, etc.) within 2GB RAM, Database services (MySQL, Mongo, PostgreSQL, etc.) with 1GB limit, 50 GB data transfer limit and Free Custom Domains.

Step 2) Create your AppFog application.  Simple click to create and deploy, pick you application framework and associated database service.  It's so easy I won't get into those details here, other than to say my first application was Node.js with MySQL database.

Step 3) Install Ruby from http://rubyinstaller.org (the version I installed was: rubyinstaller-1.9.3-p194.exe).  This is a pretty basic Windows application install, so not much to say here other than the installation path I chose which was: C:\Programs\Ruby193.

Step 4) Install DevKit from http://rubyinstaller.org/downloads (the version I installed was: DevKit-tdm-32-4.5.2-20111229-1559-sfx.exe).  This was a self extracting EXE that simply extracted the contents to a directory.  I selected the extract to be inside my existing Ruby installtion folder: C:\Programs\Ruby193\DevKit.  After extraction was complete you need to run a couple commands to complete the installation:  1) ruby dk.rb init to generate the config.yml file. 2) ruby dk.rb install

Step 5) Launch Ruby and create the install the AppFog utility and the Caldecott utility.  Launch Ruby: Windows Button > All Programs > Ruby 1.9.3-p194 > Start Command Prompt with Ruby from the Ruby command window perform the following commands:

    gem update --system
    gem install af
    gem install caldecott

Step 6) Login to your AppFog account from the Ruby commandline: af login (this will prompt you for your email address and password).

Step 7) Create TCP tunnel between AppFog server and your localhost.  Caldecott is an extension that allows you to establish a tunnel by proxying TCP traffic on localhost via HTTPS.  With Caldecott, you can use the Ruby commandline (after you logged into your AppFog account) and create a tunnel that allows you to manage your MySQL database from your local desktop using MySQL Workbench.  From the Ruby commandline (after you are logged in using af login): af tunnel this will then prompt you with a list of options.  For example, in my case I had a MySQL and a MongoDB instance running on my AppFog account, so I got two options (as shown below), I selected option 2 which in my case was the MySQL database I was interested in.  Also, note the last prompt, I selected "None" which leaves the tunnel open for the MySQL Workbench UI to connect.  You will use the information shown in the username, password section to connect and the connection hostname/ip and port will be "localhost" on port "10000".

1: mongodb
2: mysql
Which service to tunnel to?: 2
Getting tunnel connection info: OK

Service connection info:
  username : xxxxxxxxxxxxx
  password : xxxxxxxxxxxxx
  name     : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
  infra    : hp

Starting tunnel to mysql on port 10000.
1: none
2: mysql
3: mysqldump

Which client would you like to start?: 1
Open another shell to run command-line clients or
use a UI tool to connect using the displayed information.
Press Ctrl-C to exit...

NOTE: If you get an error installing caldecott please update your path... type message it usually means that you did not install the DevKit properly.



Tags:

SQL DB Table Record History Template

by 12. September 2012 10:21

Here is a template script to create a standard set of objects to track changes to table data.

/*
  drop trigger SchemaName.TableName_IUX
  drop table SchemaName.TableName
  drop view SchemaName.vxTableName
  drop table SchemaName.TableNameHistory
  drop view SchemaName.vxTableNameHistory
*/

/*
  drop table SchemaName.TableName
*/
create table SchemaName.TableName
(
  /* single record per modification instance */
    Timestamp timestamp not null
  , TableNameId uniqueidentifier not null
  , LastUpdateDate datetime not null default getdate()
  , LastUpdateByUserId uniqueidentifier not null
  /* add columns here */
)
go
alter table SchemaName.TableName
add constraint
  PK_SchemaName_TableName primary key clustered
  (
    TableNameId
  ) with ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
on [PRIMARY]
go
/*
  drop view SchemaName.vxTableName
*/
create view SchemaName.vxTableName
as
select
    TableNameId
  , LastUpdateDate
  , LastUpdateByUserId
  /* add columns here */
from 
  SchemaName.TableName
go
/*
  drop table SchemaName.TableNameHistory
*/
create table SchemaName.TableNameHistory
(
  /* single record per modification instance */
    HistoryId uniqueidentifier not null default newid()
  , TableNameId uniqueidentifier not null
  , LastUpdateDate datetime not null default getdate()
  , LastUpdateByUserId uniqueidentifier not null
  /* add columns here */
)
go
alter table SchemaName.TableNameHistory
add constraint
  PK_SchemaName_TableNameHistory primary key clustered
  (
    HistoryID
  ) with ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
on [PRIMARY]
go
/*
  drop view SchemaName.vxTableNameHistory
*/
create view SchemaName.vxTableNameHistory
as
select
    TableNameId
  , LastUpdateDate
  , LastUpdateByUserId
  /* add columns here */
from 
  SchemaName.TableNameHistory
go
/*
  drop trigger SchemaName.TableName_IUX
*/
create trigger SchemaName.TableName_IUX on SchemaName.TableName for insert, update
as begin
  insert into SchemaName.vxTableNameHistory
    select t1.* 
    from SchemaName.vxTableName t1
    inner join inserted t2 on t1.TableNameID = t2.TableNameID
end
go

Tags:

SQL Server