After SQL Fails Over to New Node Reports No Longer Work In ProjectDox

 

ISSUE:  After SQL Fails Over to New Node Reports No Longer Work In ProjectDox.  When a fail-over occurs to another SQL Server node, the Report Server database gets a new encryption key assigned to it which is different from the one stored in the connection string used by ProjectDox when it was connected to the initial node from when the reports were configured in ProjectDox.

 

SOLUTION:  In order to resolve you need to either delete the encrypted content on the Report Server side or run the ProjectDox Reports Migration Utility again to allow it to write a new connection string to the web.config file for connecting to the new SQL node. 

Deleting the encrypted content is the easiest method and the steps to complete are as follows:  Open the Report Manager Configuration screen on the SQL Server and then go to the section for Encrypted Content and select the option to Delete the Encrypted Content in the database. After performing this step, log into ProjectDox and try to access the reports and verify that you can view the report with the returned content from the SQL Server.

Database ERD & Data Dictionary for ProjectDox 8.2x

 ProjectDox Version

 8.2x and above.

Resources

Please refer to the attached documents for the ProjectDox ERD and Data Dictionary.

 

Revised 02-18-2015

Description

SQL SERVER PRODUCT TYPE	RTM (Gold, no SP)	SP1	SP2	SP3	SP4
SQL Server 2014      codename HekatonSQL14	12.00.2000.8
SQL Server 2012      codename Denali	11.00.2100.60	11.00.3000
SQL Server 2008 R2      codename Kilimanjaro / KJ	10.50.1600.1	10.50.2500	10.50.4000
SQL Server 2008      codename Katmai	10.00.1600.22	10.00.2531	10.00.4000	10.00.5500
SQL Server 2005      codename Yukon	9.00.1399.06	9.00.2047	9.00.3042	9.00.4035	9.00.5000
SQL Server 2000      codename Shiloh	8.00.194	8.00.384	8.00.532	8.00.760	8.00.2039
SQL Server 7.0      codename Sphinx	7.00.623	7.00.699	7.00.842	7.00.961	7.00.1063

 

Revised 02-26-2015

Description

ProjectDox and its associated features require the use of a SQL Server database, which stores all of the data captured by its usage.  Throughout the course of day-to-day operations, data is written to and removed from the database constantly, which allows for fragmentation of the database to occur.  Over a period of time, the fragmentation can start to hamper the operation of the system. In order to avoid any degredation of performance, we recommend that regularly scheduled maintenance be run against the ProjectDox database. 

Solution

Please see the attached document for further information. Within, you will find some details regarding recommended ProjectDox database maintenance plans.

 

Revised 02-26-2015

Solution

Tuning the ProjectDox database using SQL Profiler, and Tuning Adviser

 

NOTE: The interface of the tools may vary with the version of SQL Server you use. Please consult Microsoft’s web site or other documentation for additional information.

 

IMPORTANT! Running the SQL Profiler and Tuning Adviser should be undertaken only by persons sufficiently familiar with SQL Server. The tools create temp files, and care must be taken not to fill up the system drive. Use this guide at your own risk. Avolve Software is not responsible for any damages.

 

1.    Confirm you have ProjectDox standard users, Project Admin, and System admin logins and passwords, and the URL for the site
2.    Open SQL Server Profiler (in SQL Management Studio)
3.    Select File, New Trace
4.    Login as an SA on the SQL Server
5.    Select the Events Selection tab
6.    Check Show all Columns checkbox
7.    Click the Column Filters button
8.    Choose Database Name
9.    Expand Like on the right side of the window, type the name of your ProjectDox database (in the example ProjectDoxLu,) and then click OK.

   NOTE: The actions describe in the next step are for general tuning. Your purpose for tuning may vary, and you may select specific activities to trace and tune (for example: logging in as a user with membership in many projects, waiting for the project list to display)

10.    Click Run when you are all ready to start.
    1.    Login as a low permissioned user. Use PD – view projects, files, folders, Eforms, etc. (Better if multiple active users are doing it live)
    2.    Login as PA, SA. Use ProjectDox – view projects, files, folders, Eforms, etc.
11.    Stop trace
12.    Open database tuning advisor
13.    Choose Workload, File radio button
14.    Navigate to the Trace file
15.    Choose the Database for workload analysis
16.    Place a checkmark next to the name of the database
17.    Click the Start Analysis button at the top of the Advisor
18.    When complete, Choose the Actions menu, Save Recommendations 
19.    Save the Recommendations as a SQL file, this is for audit trail
20.    Apply recommended indexes and statistics.
21.    Test your site again with the same actions to verify improvement.

Resources

For screenshots to clarify steps further, see attached file. 

 

Revised 02-26-2015

To install SQL Server 2008 Reporting Services

Description

Reporting metrics queries for ProjectDox SQL Database

Solution

Average Project Size

 

selectAVG(MB)from (

SELECT p.Name AS Project,

(SUM(f.Filesize)/ 1024)AS MB

FROM Projects p

JOIN Files f

ON p.ProjectID = f.ProjectID

GROUPBY p.Name) x

 

 

 

Average File Size

 

selectAVG(filesize)from Files

 

 

 

Total Number of Document Pages & Plan Pages by Year Uploaded

 

 

selectisnull(sum(PageCount),0)as [2014 Total Document Pages]

from Files

whereyear(UploadDate)BETWEEN 2014 AND 2014

and(SheetSize ='8.5x11.0'or SheetSize ='8.5x11')

 

selectisnull(sum(PageCount),0)as [2014 Total Plan Pages]

from Files

whereyear(UploadDate)BETWEEN 2014 AND 2014

and SheetSize <>'8.5x11.0'and SheetSize <>'8.5x11'

 

 

 

 

Number of Files Uploaded Per Project  (OPTIONAL: Time Period)

 

 

SELECT p.Name AS Project,COUNT(l.LogReportID)AS'# Files Uploaded'

FROM LogReports l

JOIN Files f

ON l.EntityID = f.FileID

JOIN Projects p

ON f.ProjectID = p.ProjectID

AND l.EventType ='FileUpload'

--OPTIONAL: Uncomment the YEAR and MONTH if you want results for a specific time period

--ANDYEAR(l.UsageDate)= 2014

--ANDMONTH(l.UsageDate)= 5

GROUPBY p.Name

ORDERBY p.Name

 

 

 

Number of Files Downloaded Per Project (OPTIONAL: Time Period)

 

 

SELECT p.Name AS Project,COUNT(l.LogReportID)AS'# Files Downloaded'

FROM LogReports l

JOIN Files f

ON l.EntityID = f.FileID

JOIN Projects p

ON f.ProjectID = p.ProjectID

AND l.EventType ='FileDownload'

--OPTIONAL: Uncomment the YEAR and MONTH if you want results for a specific time period

--ANDYEAR(l.UsageDate)= 2014

--ANDMONTH(l.UsageDate)= 5

GROUPBY p.Name

ORDERBY p.Name

 

 

 

Total Size of Files Uploaded Per Project

 

 

SELECT p.Name AS Project,(SUM(f.Filesize))AS Megabytes

FROM Projects p

JOIN Files f

ON p.ProjectID = f.ProjectID

GROUPBY p.Name

ORDERBY p.Name

 

 

 

Total Size of Files Uploaded Per User

 

 

SELECT u.FirstName AS'FirstName', u.LastName AS'LastName',u.Email AS'Email',(SUM(f.Filesize)/ 1024)AS Megabytes

FROM Users u

JOIN Files f

ON u.UserID = f.AuthorID

GROUPBY u.LastName, u.FirstName, u.Email

ORDERBY u.LastName

 

 

Revised 02-18-2015

 

 

When modifying the SSRS reports, make sure, before checking them in, that they export correctly in PDF. Some of the reports are not easily printed on 8.5x11 sheet of paper, make sure they all export into one page width in PDF. This means, no extra blank page and no grids that get cut off and put on extra pages.

Here how to prevent that:

1. Click on Report > Report Properties > Layout tab (Page Setup tab in SSDT-BI)
2. Make a note of the values for Page width, Left margin, Right margin
3. Close and go back to the design surface
4. In the Properties window, select Body
5. Click the + symbol to expand the Size node
6. Make a note of the value for Width

To render in PDF correctly Body Width + Left margin + Right margin must be less than or equal to Page width. When you see blank pages being rendered it is almost always because the body width plus margins is greater than the page width.

Remember: (Body Width + Left margin + Right margin) <= (Page width)

To get the body width, click on the white part of the body of the report and in the properties screen on the right you’ll see the measurements. To get the report width, click on anywhere outside, not on the report, and in the properties screen on the right you’ll see the measurements.

 

Contributed by: Chris Wright

Contributed on: 2/27/17                    

Description

What is an "orphan project"? It is the result of a project OWNER being deleted therefore, when you attempt to add a project, it fails and you probably don't see all the projects on your list that you should.

 

Solution

Run the first script to confirm the fact that you have an orphan.

SELECT *

FROM Projects

WHERE OwnerID NOT IN (SELECT UserID FROM Users)

Note the ProjectID(s) in the results pane, for our example below it was identified as 130.

 

Determine the UserID you want to assign it to by running this snippet.

SELECT * FROM Users WHERE FirstName = 'Lora'

Note the UserID(s) in the results pane, for our example below it was identified as 2.

 

Run the following script after you determined the ProjectID and new Project Owner's UserID.

UPDATE Projects

SET OwnerID = 2

WHERE ProjectID = 130

 

Revised 02-26-2015

 

All SQL Server ADO.NET Connection String Properties

 

This table shows all connection string properties for the ADO.NET SqlConnection object. Most of the properties are also used in ADO. Using this table will give you a better understanding of the options available when setting the connection string values in the ProjectDox Services config files.

 

Keyword	Default	Explanation
Application Name		The name of the application, or '.Net SqlClient Data Provider' if no applicationname is provided.
Async	'false'	When true, enables asynchronous operation support. Recognized values are true, false, yes, and no.
AttachDBFilename -or- extended properties -or- Initial File Name		The name of the primary database file, including the full path name of an attachable database. AttachDBFilename is only supported for primary data files with an .mdf extension. The attachment will fail if the primary data file is read-only. The path may be absolute or relative by using the DataDirectory substitution string. If DataDirectory is used, the database file must exist within a subdirectory of the directory pointed to by the substitution string. Note that remote servers, HTTP, and UNC (\\server\sharename\folder\) path names are not supported. The database name must be specified with the keyword 'database' (or one of its aliases) as in the following: "AttachDbFileName=|DataDirectory|\data\YourDB.mdf;integrated security=true;database=YourDatabase". An error will be generated if a log file exists in the same directory as the data file and the 'database' keyword is used when attaching the primary data file. In this case, remove the log file. Once the database is attached, a new log file will be automatically generated based on the physical path.
Connect Timeout -or- Connection Timeout	15	The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
Connection Lifetime	0	When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online. A value of zero (0) causes pooled connections to have the maximum connection timeout.
Context Connection	'false'	true if an in-process connection to SQL Server should be made.
Connection Reset	'true'	Determines whether the database connection is reset when being removed from the pool. Setting to 'false' avoids making an additional server round-trip when obtaining a connection, but the programmer must be aware that the connection state is not being reset.
Current Language		The SQL Server Language record name.
Data Source -or- Server -or- Address -or- Addr -or- Network Address		The name or network address of the instance of SQL Server to which to connect. The port number can be specified after the server name: server=tcp:servername, portnumber. When specifying a local instance, always use (local). To force a protocol, add one of the following prefixes: np:(local), tcp:(local), lpc:(local) ADO.NET 2.0 does not support asynchronous commands over shared memory for SQL Server 2000 or earlier. However, you can force the use of TCP instead of shared memory, either by prefixing tcp: to the server name in the connection string, or by using localhost.
Encrypt	'false'	When true, SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed. Recognized values are true, false, yes, and no.
Enlist	'true'	When true, the pooler automatically enlists the connection in the creation thread's current transaction context. Recognized values are true, false, yes, and no.
Failover Partner	N/A	The name of the failover partner server where database mirroring is configured. The Failover Partner keyword is not supported by .NET Framework version 1.0 or 1.1.
Initial Catalog -or- Database		The name of the database.
Load Balance Timeout	0	The minimum time, in seconds, for the connection to live in the connection pool before being destroyed.
MultipleActiveResultSets	'false'	When true, an application can maintain multiple active result sets (MARS). When false, an application must process or cancel all result sets from one batch before it can execute any other batch on that connection. Recognized values are true and false. The keyword is not supported by .NET Framework version 1.0 or 1.1.
Integrated Security -or- Trusted_Connection	'false'	Whether the connection is to be a secure connection or not. Recognized values are 'true', 'false', and 'sspi', which is equivalent to 'true'.
Max Pool Size	100	The maximum number of connections allowed in the pool.
Min Pool Size	0	The minimum number of connections allowed in the pool.
Network Library -or- Net	'dbmssocn'	The network library used to establish a connection to an instance of SQL Server. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol, Windows RPC), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmslpcn (Shared Memory, local machine only) and dbmsspxn (IPX/SPX), dbmssocn (TCP/IP) and Dbmsvinn (Banyan Vines). The corresponding network DLL must be installed on the system to which you connect. If you do not specify a network and you use a local server (for example, "." or "(local)"), shared memory is used.
Packet Size	8192	Size in bytes of the network packets used to communicate with an instance of SQL Server.
Password -or- Pwd		The password for the SQL Server account logging on. Not used with (the strongly recommended) 'Integrated Security=true' option.
Persist Security Info	'false'	When set to 'false' (strongly recommended), security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values including the password.
Pooling	'true'	When true, the SQLConnection object is drawn from the appropriate pool, or if necessary, is created and added to the appropriate pool. Recognized values are true, false, yes, and no.
Replication	'false'	true if replication is supported using the connection.
Transaction Binding	Implicit Unbind	Controls connection association with an enlisted System.Transactions transaction. Possible values are: Transaction Binding=Implicit Unbind; Transaction Binding=Explicit Unbind; Implicit Unbind causes the connection to detach from the transaction when it ends. After detaching, additional requests on the connection are performed in autocommit mode. The System.Transactions.Transaction.Current property is not checked when executing requests while the transaction is active. After the transaction has ended, additional requests are performed in autocommit mode. Explicit Unbind causes the connection to remain attached to the transaction until the connection is closed or an explicit SqlConnection.TransactionEnlist(null) is called. An InvalidOperationException is thrown if Transaction.Current is not the enlisted transaction or if the enlisted transaction is not active.
TrustServerCertificate	'false'	When set to true, SSL is used to encrypt the channel when bypassing walking the certificate chain to validate trust. If TrustServerCertificate is set to true and Encrypt is set to false, the channel is not encrypted. Recognized values are true, false, yes, and no.
Type System Version	N/A	A string value that indicates the type system the application expects. Possible values are: Type System Version=SQL Server 2000; Type System Version=SQL Server 2005; Type System Version=SQL Server 2008; Type System Version=Latest; When set to SQL Server 2000, the SQL Server 2000 type system is used. The following conversions are performed when connecting to a SQL Server 2005 instance: XML to NTEXT UDT to VARBINARY VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) to TEXT, NEXT and IMAGE respectively. When set to SQL Server 2005, the SQL Server 2005 type system is used. No conversions are made for the current version of ADO.NET. When set to Latest, the latest version than this client-server pair can handle is used. This will automatically move forward as the client and server components are upgraded.
User ID		The SQL Server login account.
User Instance	'false'	A value that indicates whether to redirect the connection from the default SQL Server Express instance to a runtime-initiated instance running under the account of the caller.
Workstation ID	The local computer name	The name of the workstation connecting to SQL Server.

 

SQL Server Service Account

On some occasions where the SQL Server Service Account has been modified after the initial installation to use a different account for starting up the SQL Services, users will experience slowness and poor performance within the ProjectDox application due to insufficient permissions and security policy settings being granted to the new SQL Service Account.  Please modify the account's permissions as indicated below.

Windows security policy and permissions

The account assigned to start SQL service needs the Start, stop and pause permission for the service. The SQL Server Setup program automatically assigns this.

SQL Setup account adds the SQL service account as part of below security policies:

• Log on as a service
• Replace a process-level token
• Bypass traverse checking
• Adjust memory quotas for a process
• Permission to start SQL Writer
• Permission to read the Event Log service
• Permission to read the Remote Procedure Call service

If SQL Service account need to be changed, always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings. To change Reporting Services options, use the Reporting Services Configuration Tool.

SQL Server Setup does not open ports in the Windows firewall. Connections from other computers may not be possible until the Database Engine is configured to listen on a TCP port, and the appropriate port is opened for connections in the Windows firewall. To access an instance of the SQL Server through a firewall, configure appropriate inbound and outbound firewall rules on the SQL Server machine.

Lock Pages In memory

Lock pages in memory privilege to SQL is recommended in Windows Server 2008 or above only when there are signs of paging. Locking pages in memory may boost performance when paging memory to disk is expected.

To enable the lock pages in memory option, add the SQL Service account to "Lock pages in memory" security policy and restart the SQL database engine service.

Before enabling LPIM for SQL server, review the Performance monitor data for other applications running on the server and check if check working set memory of SQL is trimmed and accordingly setup LPIM.

 

File System Permissions Granted to SQL Server service SID's or Local Windows Groups and registry permissions are documented here: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions