Database Consulting Services . . . some plain talk.
SQL Server Database Audits
SQL Server Database Conversions and Migrations
SQL Server Database Tuning and Optimization
SQL Server Database Reporting, Business Intelligence, and Analytics
The SQL Server 2008 R2 documentation focuses on the SQL Server Agent account as must being a domain account. However, a “stand alone” Utility Control Point (UCP) can be setup on Window 7 without a domain account. Of course, the UCP will only function for local SQL Server instances but this is sufficient for a learning/test environment.
The snag is when the SQL Server Agent service account is specified in the UCP setup wizard. On a local box, the SQL Server Agent service account is stored in the form “.\ServiceAccount” vice “HostName\ServiceAccount”. This will return an error like this:
The SQL Server Agent service account must be a valid Windows domain account on the specified instance. Validation failed. The operation failed to create a login on the specified instance of SQL Server. Possible reasons include invalid username and password, no permission to create a login, or a policy on the specified instance of SQL Server that prevents login creation. To continue, verify that the specified username and password are correct, then verify that Windows permissions and SQL Server PBM policies allow the specified account to run the Windows CREATE LOGIN operation. The error reported for this operation was:
Windows NT user or group ‘.\SQLServiceAccount’ not found. Check the name again.
For more information, see the Help topic for this operation.
The workaround is to use the option to manually specify an account. My preference is to use the SQL Server Agent service account for everything. By manually specifying the account, it can be input in the correct form, e.g. “HostName\ServiceAccount”.
The Federal Reserve Bank has established a pattern of introducing quirks to the data or the XML schema. These quirks break down their entire technical approach to their data download service. Fortunately, the sound architecture of FRB_SR greatly aids in compensating. However, traces of the Federal Reserve Bank’s missives still appear in the data. For example, the Federal Reserve Bank added about two dozen series code numbers to the “Flow of Funds Accounts of the United States (Z.1)” statistical release without the code names. Those series code numbers were manually added but the code names were added like “UNKNOWN . . . temp fix for 123456”. When the Federal Reserve Bank gets around to updating the XML schema or publishing the series names, this issue will be corrected.
We designed FRB_SR in a very compact form to facilitate distribution. Like the Federal Reserve Bank, other data providers offer very segmented and limited access to the data. FRB_SR contains every statistical release the Federal Reserve Bank publishes. To make distribution possible, the schema is very simple with only three tables. Seven data views provide a basic breakout and access to the statistical releases and their sub-levels. While the FRB_SR design has performance features, the schema is a more compact data warehouse rather than a high performance database. The initial execution of a view will likely yield slower performance which improves significantly thereafter. We have found this adequate for our development and testing purposes. If you find performance is an issue in your data environment, we recommend extracting the data out to fully optimized data tables. We are currently working on “expansion” scripts to do this.
We publish FRB_SR version updates monthly around the fifteenth for the prior month’s data. Federal Reserve Bank dictates the schedule.
Actually, FRB data is, in fact, free.
Background: Test data was needed for another database project and the Federal Reserve Bank is a good source. We didn’t need everything they have. However, it turned out all FRB statistical releases are fairly consistent being distributed on top of the SDMX standard. Since we already built an automated ETL (Extract, Transform, and Load) process to download and handle FRB data according to that standard, we figured why not do all of them.
Here are the main reasons FRB_SR is a good value:
1) The FRB website limits downloads in a fashion that is a bit cumbersome and disorganized.
2) The FRB data downloads are not in a readily usable format.
3) The FRB and SDMX folks, being (quasi) government agencies, have a tendency to be a little careless about making “unannounced changes” . . . four times in the last six months . . . which are show stoppers. Adjusting for this is a relatively easy fix for us since it is a simple code change . . . and we are use to it.
)4 ROI . . . FRB_SR is cost effective. Over the past many years we have designed, developed, maintained, debugged and reverse engineered countless data applications and systems . . . based on our experience an organization will spend thousands PER MONTH to built and administer a similar process.
So, the fee is not for the data but for doing the ETL process, packaging it in a real database, and distribution.
This license pertains to all editions and versions of any MacroTrenz licensed Software Product. This license is online at MacroTrenz.Com
Copyright (c) 2010 – 2017 Geary M. McIver, Data Consultant.
All rights reserved.
All software products created by and wholly & privately owned by Geary M. McIver, Data Consultant, are licensed exclusively through MACROTRENZ CORP. and are subject to the terms of the following licensing agreement:
IMPORTANT PLEASE READ THE TERMS AND CONDITIONS OF THIS LICENSE AGREEMENT CAREFULLY BEFORE CONTINUING WITH THE INSTALL OR USE OF ANY SOFTWARE PRODUCT CREATED BY AND WHOLLY & PRIVATELY OWNED BY GEARY M. MCIVER, DATA CONSULTANT.
MACROTRENZ CORP.’s End-User License Agreement (“EULA”) is a legal agreement between you (either an individual or a single entity) and MACROTRENZ CORP. for the MACROTRENZ CORP. software product(s) which may include associated software components, media, printed materials, and “online” or electronic documentation (“SOFTWARE PRODUCT”). By installing, copying, or otherwise using the SOFTWARE PRODUCT, you agree to be bound by the terms of this EULA. This license agreement represents the entire agreement concerning the program between you and MACROTRENZ CORP., (referred to as “licenser”), and it supersedes any prior proposal, representation, or understanding between the parties. If you do not agree to the terms of this EULA, do not install or use the SOFTWARE PRODUCT.
The SOFTWARE PRODUCT is protected by copyright laws and international copyright treaties, as well as other intellectual property laws and treaties. The SOFTWARE PRODUCT is licensed, not sold.
1. GRANT OF LICENSE.
The SOFTWARE PRODUCT is licensed as follows:
(a) Installation and Use.
MACROTRENZ CORP. grants you the right to install and use a copy of the SOFTWARE PRODUCT on your computer.
(b) Backup Copies.
You may also make copies of the SOFTWARE PRODUCT as may be necessary for backup and archival purposes.
2. DESCRIPTION OF OTHER RIGHTS AND LIMITATIONS.
(a) Maintenance of Copyright Notices.
You must not remove or alter any copyright notices on any and all copies of the SOFTWARE PRODUCT.
You may not distribute copies of the SOFTWARE PRODUCT to third parties.
(c) Prohibition on Reverse Engineering, Decompilation, and Disassembly.
You may not reverse engineer, decompile, or disassemble the SOFTWARE PRODUCT, except and only to the extent that such activity is expressly permitted by applicable law notwithstanding this limitation.
You may not rent, lease, or lend the SOFTWARE PRODUCT.
(e) Support Services.
MACROTRENZ CORP. may provide you with support services related to the SOFTWARE PRODUCT (“Support Services”). Any supplemental software code provided to you as part of the Support Services shall be considered part of the SOFTWARE PRODUCT and subject to the terms and conditions of this EULA.
(f) Compliance with Applicable Laws.
You must comply with all applicable laws regarding use of the SOFTWARE PRODUCT.
Without prejudice to any other rights, MACROTRENZ CORP. may terminate this EULA if you fail to comply with the terms and conditions of this EULA. In such event, you must destroy all copies of the SOFTWARE PRODUCT in your possession.
All title, including but not limited to copyrights, in and to the SOFTWARE PRODUCT and any copies thereof are owned by Geary M. McIver or his suppliers. All title and intellectual property rights in and to the content which may be accessed through use of the SOFTWARE PRODUCT is the property of the respective content owner and may be protected by applicable copyright or other intellectual property laws and treaties. This EULA grants you no rights to use such content. All rights not expressly granted are reserved by Geary M. McIver.
5. NO WARRANTIES
MACROTRENZ CORP. and Geary M. McIver expressly disclaim any warranty for the SOFTWARE PRODUCT. The SOFTWARE PRODUCT is provided ‘As Is’ without any express or implied warranty of any kind, including but not limited to any warranties of merchantability, noninfringement, or fitness of a particular purpose. MACROTRENZ CORP. and Geary M. McIver do not warrant or assume responsibility for the accuracy or completeness of any information, text, graphics, links or other items contained within the SOFTWARE PRODUCT. MACROTRENZ CORP. and Geary M. McIver make no warranties respecting any harm that may be caused by the transmission of a computer virus, worm, time bomb, logic bomb, or other such computer program. MACROTRENZ CORP. and Geary M. McIver further expressly disclaims any warranty or representation to Authorized Users or to any third party.
6. LIMITATION OF LIABILITY
In no event shall MACROTRENZ CORP. or Geary M. McIver be liable for any damages (including, without limitation, lost profits, business interruption, or lost information) rising out of ‘Authorized Users’ use of or inability to use the SOFTWARE PRODUCT, even if MACROTRENZ CORP. or Geary M. McIver have been advised of the possibility of such damages. In no event will MACROTRENZ CORP. or Geary M. McIver be liable for loss of data or for indirect, special, incidental, consequential (including lost profit), or other damages based in contract, tort or otherwise. MACROTRENZ CORP. and Geary M. McIver shall have no liability with respect to the content of the SOFTWARE PRODUCT or any part thereof, including but not limited to errors or omissions contained therein, libel, infringements of rights of publicity, privacy, trademark rights, business interruption, personal injury, loss of privacy, moral rights or the disclosure of confidential information.