-
There are times when creating a database backup is not convenient. For example, you may be connected remotely to a SQL Server and not have access to the backup files that may be created. For times like these, I love the SSMS Tools Pack. You can find this handy gem of a SQL Server addon at http://www.ssmstoolspack.com/Download.aspx.
Once you download and install the addon, run SQL Server Management Studio and right-click on the database you'd like to backup. Notice the "SSMS Tools\Generate Insert Statements" command in the popup menu. This handy command will create a script file with INSERT statements for every tables in your database. You can then copy and paste these commands to another SQL Server to recreate the data there or just keep the script tucked away as a handy script-based backup of your data.
Check it out! The tools pack also has many other features for you to explore such as a query history and text regions for your SQL code, but I find the Generate Insert Statements my favorite tool in this addon by far.
--Gus
-
In this blog, let's take a quick look at how to use a map to display nationwide sales data on a SQL Server report. You can jump quickly to the bottom of this blog entry to see how the report will finally turn out. To try out the steps below, you first need a server running SQL Server 2005 with the AdventureWorks database. You also need SQL Server Reporting Services configured and you'll need Visual Studio 2005 or 2008 on your machine.
Let's first start out by installing the Dundas Map for Reporting Services control on your machine. You do this by visiting http://www.dundas.com/Downloads/index.aspx and clicking the "Download Now" button next to "Dundas Map for Reporting Services". Follow the instructions on the Dundas website to install the control.
Once the control is installed, fire up Visual Studio. Create a new "Report Server" project.
Next, right-click on the Reports folder in the Solution Explorer and add a new report. Click through the wizard to setup a data source that points to your AdventureWorks database.
In the "Design the Query" step of the wizard, paste this SQL code in. The query will retrieve the total sales for each state.
SELECT z.[Name], CONVERT(INT, SUM(z.TotalDue)) AS StateTotal
FROM (
SELECT DISTINCT s.[Name], o.TotalDue
FROM Person.StateProvince s INNER JOIN Person.Address p ON s.StateProvinceID = p.StateProvinceID
INNER JOIN Sales.SalesOrderHeader o ON o.BillToAddressID = p.AddressID
WHERE s.CountryRegionCode = 'US'
UNION
SELECT DISTINCT s.[Name], 0 AS TotalDue
FROM Person.StateProvince s
WHERE s.CountryRegionCode = 'US'
) z
GROUP BY z.[Name]
Click the Next button from this screen, then click Next/Finish to end the wizard. You can accept the defaults of the wizard after the "Design the Query" step. Once the wizard is done, select everything on the report page and delete it. We will be using the map control and do not need the controls the wizard created.
Next, we'll need to add the Dundas Maps control to the toolbox if its not already there. Right-click the Toolbox window, and select the "Choose Items..." menu item. In the ".NET Framework Components" tab click on the "Browse..." button and browse to the installed Maps assembly (DundasRSMapDesigner.dll). After selecting the chart assembly make sure the "Dundas Map for Reporting Services" item is checked.
Now drag the map control to the report layout. This will start the Dundas Map wizard. On step 1, change the Gallery dropdown to "North America", click "USA", then click Next.
Next, select your data source for the map's data set. Add the "Name" and "StateTotal" fields to the "Displayed fields" list. Also, click "Row Groups" to add the "Name" field to the row groups list. Click Next.
Finally on the wizard, change the Field to StateTotal and Text to #STATETOTAL. You can also tweak the color settings to your liking.
We're not done yet. The wizard gets us started, but we still need to tweak the properties of the report a little. Right-click the map itself and select "Properties". On the General tab, change the settings to reflect the screen shot below.
On the Data Appearance tab, change the settings to reflect the screen shot below. In particular, set the Legend Text to "#FROMVALUE{N0} - #TOVALUE{N0}". Also, set the "Show In Color Swatch" setting.
Finally, in the Shape Appearance tab, you can configure the color settings as below.
That's about it. You should have a report like the one below. To test your report, click the Preview pane. In a later blog entry, we'll explore adding drilldown functionality to the map report so users can click on the map itself to load a map with more details for the state selected. Stay tuned!
-
I ran across a very cool site today that is hosting virtual sessions of SQL Server 2008. You create an account there and then get a remote desktop connection to a machine with the latest beta copy of SQL Server 2008 already installed. Best of all, any work you do in the session will be persisted so you can setup a new database, test some of the new features of SQL Server 2008, log off, and come back later and continue working.
When you visit the site and sign up, you will not get immediate access to the free SQL Server 2008 beta account. Instead, information on accessing the account is emailed to you within 24 hours of registering for the account. Once you get the email, it will contain the login information you need to run Remote Desktop Connection and connect to your new instance of Windows 2008 with SQL Server 2008 (CTP6).
It's all here at SQL Server Beta.
-
When you first setup your SharePoint site, the SQL Server database that is created for that site is about 10MB by default. As time passes though and you add and remove files to your site, you will notice that the database will grow very large. This can be an issue for your nightly backups and can hurt overall SharePoint performance. In this blog, we'll take a two-part look at shrinking the SharePoint content database. In this blog entry, we'll focus primarily on the traditional method of shrinking a SQL Server database. Sometimes though, it's not enough to simply shrink the existing contents of your SharePoint database. There are cases when you'll want to remove some of the binary data representing large documents and their multiple versions from your database. This is a little trickier and we'll save that for the next blog entry.
So let's get to it. If you manage only one SharePoint site, then there should be only one WSS_Content database to shrink. If however you manage several sites, you'll first need to determine which database to focus on. This can be done by going to the SharePoint 3.0 Central Administration page and clicking on the Application Management tab. Click the Site Collection List link in the SharePoint Site Management section. Click the URL on the left for the site you wish to focus on. The database for that site will be listed on the right.
Now run SQL Server Management Studio and connect to this content database via a query window. Run the command "sp_helpdb <db_name>" where <db_name> is your database name. Note the value in the "db_size" column of the top recordset and the values in the "size" column of the second recordset.
The steps below will focus on decreasing these sizes as close as possible to the initial size of the database. In most cases, this will be about 10-20MB. If a custom database was used for the content database for your SharePoint site, the smallest size you can shrink your database to will be the initial size that you set for that custom database.
First, you need to produce a full backup of the database. This can be done in the query window using this command, substituting your database name for "dbname".
BACKUP
DATABASE dbname TO DISK = 'C:\Backups\dbname.bak' WITH INIT, STATS = 5
Next, run this script to prepare the log file of the database to be shrunk. Replace the 'dbname' with your database name. This code moves the active VLF of the log file to an earlier physical location and will allow the log file to shrink later. You can read more about the VLF here.
DECLARE
@dbname varchar(100)
SET @dbname = 'todo'
exec
('Backup Log '+@dbname+' with Truncate_Only')
exec ('create table '+@dbname+'..Temp1 (Col1 int)')
exec ('insert into '+@dbname+'..Temp1 values (0)')
exec ('update '+@dbname+'..Temp1 set Col1 = 1')
exec ('update '+@dbname+'..Temp1 set Col1 = 2')
exec ('update '+@dbname+'..Temp1 set Col1 = 3')
exec ('update '+@dbname+'..Temp1 set Col1 = 4')
exec ('update '+@dbname+'..Temp1 set Col1 = 5')
exec ('update '+@dbname+'..Temp1 set Col1 = 6')
exec ('update '+@dbname+'..Temp1 set Col1 = 7')
exec ('update '+@dbname+'..Temp1 set Col1 = 8')
exec ('update '+@dbname+'..Temp1 set Col1 = 9')
exec ('update '+@dbname+'..Temp1 set Col1 = 10')
exec ('update '+@dbname+'..Temp1 set Col1 = 11')
exec ('update '+@dbname+'..Temp1 set Col1 = 12')
exec ('update '+@dbname+'..Temp1 set Col1 = 13')
exec ('update '+@dbname+'..Temp1 set Col1 = 14')
exec ('update '+@dbname+'..Temp1 set Col1 = 15')
exec ('update '+@dbname+'..Temp1 set Col1 = 16')
exec ('update '+@dbname+'..Temp1 set Col1 = 17')
exec ('update '+@dbname+'..Temp1 set Col1 = 18')
exec ('update '+@dbname+'..Temp1 set Col1 = 19')
Finally, run these commands to actually shrink the data and log files for your database. Note that these statements may run a very long time depending on your database.
DECLARE
@dbname varchar(100)
SET @dbname = 'todo'
DBCC
SHRINKDATABASE (@dbname, 25)
exec ('DROP TABLE '+@dbname+'..Temp1')
exec ('BACKUP LOG '+@dbname+' WITH TRUNCATE_ONLY')
exec ('BACKUP DATABASE '+@dbname+' TO DISK = ''C:\Backups\'+@dbname+'.bak'' with INIT, STATS = 5 ')
Now if you run the "sp_helpdb <db_name>" command that we ran earlier, you should find smaller sizes for your database files!
This will take care of database sizing issues for most SharePoint deployments. Backups should run much quicker now and overall SharePoint performance should improve. There are cases though where the steps above are not enough. In one deployment I found, the database had grown to over 0.5 TB (yikes!) due to a large number of very large files being uploaded to SharePoint. This might be so bad except that versioning was also enabled so small changes to very large files over time led to lots of wasted database space maintaining old copies of very large files. The next blog entry I'll make will focus on this case. Until then, post a comment and let me know how much database space you save!
-
Welcome back to part 2 of my blog on Database-Driven Excel Charting in SharePoint. If you followed the steps from the first part of this blog, you should have an Excel 2007 workbook with a pivot table calling a stored procedure in SQL Server. In this blog, we'll publish this workbook to Excel and configure SharePoint to allow the workbook to continue querying the database.
The Excel Chart
First, we'll take the pivot table we created thus far and create a chart over it. Select the data within the pivot table.
Click the Insert tab in the Office ribbon and click the 2-D Line charting option.
Close the PivotChart Filter Pane that appears and move the chart so it covers the pivot table. That's it for now in Excel. Now let's turn our attention to SharePoint.
The Data Connection Library
Next we'll need a database connection library. If we don't already have one, we can create one within SharePoint. You do this by connecting to your SharePoint site via your browser and clicking the View All Site Content link on the left. Click the Create button at the top of the subsequent page and then select Data Connection Library under the Libraries section.
Provide a name for the new data connection library and click the Create button. SharePoint will take you to the new library. Copy the hyperlink from the address bar and strip off the "/Forms/AllItems.aspx" portion at the end. The resulting URL will be used in the following steps.
Trusting the Data Connection Library on Excel Services
Our Excel file will use this data connection library to house the connection of the pivot table for SharePoint to use to refresh the chart. Before we can use the data connection library though, we must add it to the trusted data connection libraries of Excel Services. To do this, launch SharePoint 3.0 Central Administration. Click on your Shared Services Provider link on the left, then select Trusted data connection libraries in the Excel Services Settings section on the right.
On the subsequent page, click the Add Trusted Data Connection Library button at the top. In the Address field, paste the hyperlink that you copied earlier without the "/Forms/AllItems.aspx" portion at the end. Click OK.
Exporting Excel’s Data Connections to SharePoint
Now we can return to the Excel workbook. Click on the Data tab in the Ribbon. Click on the Connections button.
Select the data connection and click the Properties button to the right of it. Once the Properties window opens, set how often you wish the data to refresh.
Next, click on the Definition tab in the Properties window. Check the Always use connection file and Save password checkboxes. Click the Export Connection File button and paste the URL you copied earlier for the data connection library into the File Name textbox. Click Save and the folder will change to reflect the contents of the library. Click Save once more to save the connection to the library.
Enter any descriptive text you'd like for this connection and then click OK. Finally, click OK on the Connections Properties window.
Close the Workbook Connections window and save your workbook.
With the workbook using a data connection in SharePoint that is also trusted by Excel Services, we can now publish this workbook to SharePoint. I assume that the reader knows how to publish Excel workbooks to SharePoint so I won't go into details here. You can get a quick refresher though at
Publishing Workbooks to SharePoint.
Tricky Refresh Issue
At this point if you were to alter the GetMonthlySalesForLast12Months stored procedure you would notice something interesting. Let's say you alter the procedure by changing this line...
SELECT
@MinMonthYear = DATEADD(MONTH, -11, @MaxMonthYear)
... to this ...
SELECT
@MinMonthYear = DATEADD(MONTH, -3, @MaxMonthYear)
If you still have both your Excel 2007 workbook open and the web page open displaying the chart, you'll notice that the Excel workbook will refresh within 1 minute while the SharePoint page will not. This is because a setting in SharePoint is overriding your file-level setting to refresh every minute.
You need to change the SharePoint setting as follows.
Configure the External Data settings for the trusted file location
In the SharePoint 3.0 Central Administration site, click on your Shared Services Provider link on the left again, then select Trusted file locations in the Excel Services Settings section on the right. Click the hyperlink of the folder where you deployed the workbook. Scroll down to the External Data section and update 4 settings. Uncheck the Warn on Refresh and Stop When Refresh on Open Fails checkboxes. Unchecking the second checkbox will allow the spreadsheet to still open within dashboards and use cached data if the database is unavailable for any reason. Finally, update the numeric values for Automatic refresh and Manual refresh. A value like 60 should suffice. It's these last 2 settings that caused the tricky refresh issue. By providing values for the folder in SharePoint that are consistent with the values you set for your workbooks, you can avoid this seemingly strange behavior!
Summary
That's about it. You should now see your Excel workbook with database-driven chart in the browser view within SharePoint.
You can test out the database-driven chart by altering the GetMonthlySalesForLast12Months stored procedure to return different numbers. You should notice the chart within a minute or two refresh itself to display the new data in the chart!
There's much more that can be done. You can clean up the workbook by removing gridlines and tweaking the display of the chart. You can create named ranges in the workbook and publish only the named range containing the chart. You can create a dashboard where a web part points to this named range and thus your dashboard can have database-driven charts from several Excel workbooks. This is all extra-credit though and I leave it to my trusted reader to venture forth and try for him or herself. Of course, if anyone is interested, just let me know and I'll add a third part to this blog entry and take this sample further.
In March I'm flying out to Seattle to get a 3-day hands-on demo from Microsoft on SQL Server 2008! Watch my blog in March for all the goodies I learn about SQL Server 2008. Until then, thanks for reading!
--Gus