New to datawarehousing...and want to know how the Adventurework and AdventureworkDW has been developed..
how the OLTP data has been extracted to AdventureworksDW..datawarehouse system and need some design documents....what to know how the DIM tables are created...and everything
sqlserversamples.codeplex.com
I think the original question requested information on Licensing as well, which none of the answers contain. I agree on the HW configuration part - they majorly depend on application size including data volume.
With SQL Server 2012 we can use Standard (Core / Server-CAL), BI (Server-CAL) and Enterprise (Core) editions, - among them which edition to choose will depend on what features the solution needs. On high level, if the solution needs canned reporting on moderate sized data, Standard edition will do. If it requires ad-hoc / self-service BI then we need BI or Enterprise edition. If the data volume is high requiring columnstore indexing, we need Enterprise edition.
So far so good, all these are options for the Prod environment.
But for Dev and QA, can we use Developer edition license:
1. Will that license be able to access data from some other SQL Server database (which might be copy of Prod data for Dev and QA)
2. Will that license enable us to move SSIS/RS/AS/PowerView codes to Prod environment without any issues?
Can some expert from Microsoft answer this question - which I think would answer a good part of the original question as well.
Souvik Banerjee. MSBI Lead, Zensar Technologies
social.technet.microsoft.com/Forums
[how to] SUSPENDED in Index Rebild sql server 2012 |
| SUSPENDED in Index Rebild sql server 2012 Posted: 11 Apr 2013 08:06 PM PDT In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job. In monitoring SQL task: Thanks in advance for any helpful info. |
| When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have? Posted: 11 Apr 2013 08:10 PM PDT Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed) I have a table that's got 19 million rows with about 30 indexes (so basically non trivial). How much of a factor would the disk speed of the LOG & tempdb than if I was to have the same disk speeds as the data disk. Edit: Some more stats. We have moved the LOG to the RAID data drive as test and unfortunately there was not improvement in the speed of the the REBUILD. It still too 1 hour.
Note this is not a transactional system, rather a Data Warehouse. |
| Insert performance deterioration over network Posted: 11 Apr 2013 05:37 PM PDT i have a strange issue with a windows application (in delphi) running on a win7 client. The application has a loops and inserts rows on a SQL Server table. It's simply an insert statement, repeated on the same table. For the first 9-10 inserts, the performance is good (approx 10 milliseconds per insert) From the 10th insert, the time suddenly increase to approx 400-500 ms. It's not a deterioration... if you go ahead, the performance is constant, always 400-500 ms. The performance changes only from the 9th to 10th insert. And it pass from 10 to 500 ms. During this time, the CPUs of client and server seems not to be overloaded, and the network too. The server is an HP ML 370 G6 with Windows Server 2008R2, and multiple sql installed: 2005, 2008 and 2008R2 If i attach the DB to 2005, 2008 or 2008R2 it's the same. If i move the DB files from the SAN to a local SAS disk, it's the same If i change client, it's the same but.... If i run the application directly on the server, the performance is excellent, straight to end of the loop. So, i decided to use another server... well, it's not a server, at all. It's just a pc (dual core).. but has the same operating system (srv2008r2), and SQL server 2008 installed. If i move the Database to this "server", and launch the application from the same client, i have an excellent performance, not so different from running the application directly on the HP server. What else can i do? Am i missing something? Thanks. Andrea |
| Tuning advisor with Extended events? Posted: 11 Apr 2013 01:36 PM PDT With SQL traces I was able to analyze them with Database Engine Tuning Advisor to obtain basic recommendations for perf. tuning(missing indexes, statistics,...). Now, with SQL 2012 and Extended Events, how can I do something similar? Thanks |
| SQL Server bulk insert performance Posted: 11 Apr 2013 12:30 PM PDT I'm running sql server 2012 and the installed ram on that machine is 128 GB. The performance of the bulk insert is very low. The bulk insert of 10 million records takes about 2 hours. We have checked the cpu, disk io, memory and everything seems to be ok. I've just checked the wait_type and found out the CXPACKET wait_time_ms is 79346890761 and max_wait_time_ms is 2164694 and signl_wait_time_ms 2849080871. Are these numbers too high and could it be the reason for slow performance? The degree of parallelism is set to 0. |
| Would using timestamps solve consistency issues with snapshot isolation? Posted: 11 Apr 2013 01:53 PM PDT I'm considering using Snapshot Isolation for some large queries being send from an Access 2007 frontend. These queries are occasionally causing I know that one major concern with using snapshot isolation is the problem of updating dirty records, such as expressed in this blog post. In my database, all of the tables have EDIT: Max Vernon asked for an example. In the article I linked to, Craig Freedman uses this as an example:
This is an issue without using any sort of rowversioning. My question is, do timestamps solve this problem? Do they leave similar issues outstanding? Or does SQL Server's rowversioning when you enable Snapshot Isolation solve the problem and make timestamps unnecessary? I'm assuming it doesn't, based on Freedman's example. |
| Types of cursor in sql server [closed] Posted: 11 Apr 2013 11:38 AM PDT How many types of cursor in SQL SERVER, please anyone explain all type of cursor with examples. Any help appreciated !! |
| Posted: 11 Apr 2013 06:43 PM PDT We are an email marketing company and we recently switched our setup to MySQL. We need to configure MySQL (my.cnf) for extreme performance. We have tried to configure my.cnf but heavy queries got stuck for many hours without any result. Some queries take one or two hours to complete on our SQL server (8GB RAM). It's a dedicated MySQL server. We have very large tables (a few tables having more then 70 million rows). Almost every time new tables to match so indexes won't help that much, although the necessary indexes exist. We do not have any application to connect our database, just 2 users who write adhoc queries directly. Below are some variables we have changed from the default values. Below is the MySQLTuner recommendation: Any help would be greatly appreciated. |
| Posted: 11 Apr 2013 02:10 PM PDT I am working on a student management system for a private school. In the table "student" I need to indicate which public school the student came from. Can I just keep this information as a column "school" in the student table or do I need to make a new table "school"? NB: The school has just one attribute which is its name. |
| Oracle: Full database (user) restore requires drop? Posted: 11 Apr 2013 05:03 PM PDT In our development environment (Oracle 11g), we often have to restore databases (users) to previous versions to test upgrades, verify defects, etc. We are using expdp/impdp to perform our backup/restores. For back-ups we run... For restores we have been dropping the user (database) manually, and then running We are now trying to automate this process and have a question. Do we have to drop the current user (database) prior to the restore or is there some option/flag on impdb that can be used to force a complete restore? I looked at TABLE_EXISTS_ACTION=REPLACE, but I noticed I still get the same "errors" during restore if I leave the option off the command-line. Thoughts? |
| MySQL tuning (my.cnf) for very large tables and ad hoc queries Posted: 11 Apr 2013 06:48 PM PDT We are an email marketing company and recently switched our setup to MySQL. We need to configure mysql (my.cnf) for extreme performance. We have tried to configure my.cnf but heavy queries can get stuck for many many hours without any result and same queries take one or two hours to complete on sqlserver (8GB Ram). It's a dedicated MySQL server.
We have very large tables, (few tables having more then 70 million rows), almost every time new tables to match so indexes wont help that much, although there exist necessary indexes. We do not have any application to connect our database, just 2 users who writes ad hoc queries directly. Below are some variables we have changed from the default values. Below is the mysqltuner recommendation Any help would be greatly appreciated. |
| Posted: 11 Apr 2013 05:45 PM PDT I have a table that stores available appointments for teachers, allowing two kinds of insertions:
Since I have to return all available teachers in a search, I need all slots to be saved in the same table as the order of arrival ranges. This way I can order by date_from ASC, showing the first available slots first on the search results. Current table structureSearch queryI need to filter by: actual datetime, city_id, subject_id and if a slot is available (status = 0). For hourly based I have to show all available slots for the first closest available day for every teacher (show all time slots of a given day and can't show more than one day for the same teacher). (I got the query with the help from mattedgod). For range based (order_of_arrival = 1), I have to show the closest available range, just one time per teacher. First query runs individually in around 0.10 ms, second query 0.08 ms and the UNION ALL an average of 300ms. QuestionIs there a way to optimise the UNION, so I can get a reasonable response of a maximum ~20ms or even return range based + hourly based in just one query (with an IF, etc)? SQL Fiddle: http://www.sqlfiddle.com/#!2/59420/1/0 EDIT:I tried some denormalization by creating a field "only_date_from" where I stored only the date, so I could change this... ... to this It already saved me 100ms! Still 200ms on average. |
| how to resolve ELFCLASS32 error in mysql for UDF lib_mysqludf_sys.so Posted: 11 Apr 2013 07:31 PM PDT I'm trying to install lib_mysqludf_sys.so in mysql to create sys_exec function which will run an external java programm .. first of all i issued this command : CREATE FUNCTION sys_exec RETURNS INT SONAME 'lib_mysqludf_sys.so'; and i got this error : ERROR 1126 (HY000): Can't open shared library 'lib_mysqludf_sys.so' (errno: 0 /usr/lib/mysql/plugin/lib_mysqludf_sys.so: wrong ELF class: ELFCLASS32) this error is due to the 64 bit os... i'm using 64 bit Ubuntu and this library file is of 32 bit. I'm not getting the library of 64 bit.. I have gone trough this : gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so -fPIC this is also giving error that file not present ..how should i resolve this |
| Why is a "non-single-group group function" allowed in a subselect but not on it's own? Posted: 11 Apr 2013 01:27 PM PDT Why does the first query not fail with the same error as the second: edit: version info added: edit: non-default params added: |
| With SQL Server 2008, how can I roll back a transaction done some time before? Posted: 11 Apr 2013 12:36 PM PDT I am coding up a script to merge some accounts (and other tables) as an out-of-band customer request. All the updates will be done in a single transaction. They are all updates (no inserts or deletes) - effectively changing the "account_id" of some entities to achieve the account merge. That's all fine, but my boss wants to be able to roll back the affected accounts after other transactions, i.e. DAYS down the road on a very active server. My ideas are: -
Many thanks! |
| Posted: 11 Apr 2013 05:01 PM PDT I have the following MDX tuple calculation for my KPI in Dashboard Designer: This works perfectly when, for instance, I view it by Team name. However, when I view it by the |
| Breaking Semisynchronous Replication in MySQL 5.5 Posted: 11 Apr 2013 03:44 PM PDT I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7. My application is running and updating the database of the master server and same is being updated in the slave database server. But due to some unknown reasons sometimes, Replication breaks. On running the command: It gives this status: Ideally, in semi synchronization, when the sync breaks the status should come as OFF since master is not able to receive any acknowledgement from the slave. Please help us in this regard. |
| Public Database for Mobile Application Posted: 11 Apr 2013 11:01 AM PDT What are the proper steps to hosting a public database. Currently I a building a mobile application with Adobe Flash Builder. I have used Microsoft SQL Server, My SQL as well as WAMP Server. All of these I have been told are private databases and thus will only allow my application to how the information as long as they are on the same internet connection. I have also been told that port forwarding my database isn't safe either. In this case what is the best approach to making my database public or what other source should I use to make my application accessible to world wide users. |
| Posted: 11 Apr 2013 04:01 PM PDT At work we keep receiving the following DataException seemingly at random when one of our processes tries to write/access a table for one of our clients: Has anyone encountered this? I'm not the person who primarily does administrative tasks on our databases, but even they seem to be having difficulty finding the root of this problem. Any suggestions? Anyone encounter this before? This error comes up for only one of our clients at a time, and it generally seems to rotate. We have Rackspace service but they wont be of much help unless we can provide screenshots, etc at the exact moment this occurs. Apologies if this post may be too vague, please let me know what information to supply to clarify things more. I'm one of the developers in my office, but I don't primarily handle the administrative tasks on our databases. edit: We spoke with IBM and this could possibly be caused by some sort of virus scan being run by IBM/Rackspace as a part of maintenance? They said this kind of dubiously though, so I am doubting this is the culprit because tables remained locked for variable amounts of time. |
| unable to login oracle as sysdba Posted: 11 Apr 2013 07:01 PM PDT I just got 11gR2 running and was able to conn as sysdba. I shutdown and started up the database to mount a new pfile. Now, I cannot login as sysdba. My parameter for password file is: I am using sqlplus within the server. This is not a remote connection. Here's some more information: |
| "connectivity libraries needed are not found" error in IBM Data Studio Posted: 11 Apr 2013 03:01 PM PDT UPDATE I am getting the following error when I try to create a new database in IBM Data Studio v3.1.1.0. I have already started the instance using command. After searching exhaustively, I am not able to find any help on the internet regarding this error. |
| mysql replication delay very serious Posted: 11 Apr 2013 12:01 PM PDT We use a ssd disk for master database and SATA disk for slave. On high write load (300 writes/s), slave delay is very serious. I have set:
on the slave, but the delay continues. the slave is for backup only, no read or write request. Seconds_Behind_Master: 92265, continue increasing. some conf: innodb_buffer_pool_size=15G innodb_log_file_size=1G innodb_log_buffer_size=32M |
| How to insert into junction table using triggers Posted: 11 Apr 2013 02:01 PM PDT Sorry in advance if this is "basic SQL." I wanted to know how to update my junction tables automatically. For example, these are my tables. Artist and Song are base tables and SongArtist is the junction table. Everything in SongArtist is PK and FK. I created some triggers like this, but they don't seem to work as I can't do INSERT INTO and add a new row when I only know one field of the junction table because I have two columns that are PK. What should I do? |
| Data dictionary best practices in SQL Server 2008 r2 Posted: 11 Apr 2013 08:01 PM PDT We are interested in sharing the meta data and data dictionary among the team. I know that we can use the Extended Properties for this purpose, but based on my experience I've seen it gets out of date easily, because team members tend to forget to update them or skip this step. I'm wondering if there is a more convenient way to create the data dictionary which can be maintained with the least amount of effort and time. Thank you. |
| What GUI tools can be used for PostgreSQL in Mac OS X? Posted: 11 Apr 2013 06:06 PM PDT I have recently started using PostgreSQL with my rails app. I was looking for a SQL GUI to the PostgreSQL database (for Mac). Anything better than pgadmin3? |
| Why use both TRUNCATE and DROP? Posted: 11 Apr 2013 11:50 AM PDT In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them. Many of my colleagues (almost all of whom are much more experienced than I am) typically do this: I typically use a single Is there any good reason for doing a |
| You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
[SQL Server] how to select top 5 records per category. |
| how to select top 5 records per category. Posted: 11 Apr 2013 07:10 AM PDT [code="sql"]IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;create table #priceData(symbol varchar(10), quote_date [datetime],close_price [decimal](6,2))INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('AAPL','20091026', 555.75)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091027', 550.97)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091028', 547.87)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091029', 543.01)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091030', 550.00)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091102', 537.08) INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091103', 535.48) INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091104', 534.80) INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('MSFT','20091026', 555.75)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091027', 550.97)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091028', 547.87)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091029', 543.01)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091030', 523.00)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091102', 537.08) INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091103', 535.48) INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091104', 585.80) CREATE CLUSTERED INDEX ix_goog on #priceData(quote_date)SELECT * FROM #priceData ORDER By Symbol, quote_dateIF OBJECT_ID('tempdb..#CountInfo') IS NOT NULL DROP TABLE #CountInfo;[/code]CATEGORY: SymbolSo how does one SELECT the top 5 records for AAPL and MSFT (in DATE DESC order) for eachThat means 5 records for AAPL and 5 records for MSFT??Any ideas:-) |
| How calc change in value over several records for different categories Posted: 10 Apr 2013 03:35 PM PDT Test data[code="sql"]IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;create table #priceData(symbol varchar(10), quote_date [datetime],close_price [decimal](6,2))INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('AAPL','20091026', 555.75)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091027', 550.97)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091028', 547.87)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091029', 543.01)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091030', 550.00)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091102', 537.08) INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091103', 535.48) INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091104', 534.80) INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('MSFT','20091026', 555.75)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091027', 550.97)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091028', 547.87)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091029', 543.01)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091030', 523.00)INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091102', 537.08) INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091103', 535.48) INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091104', 585.80) CREATE CLUSTERED INDEX ix_goog on #priceData(quote_date)SELECT * FROM #priceData ORDER By Symbol, quote_dateIF OBJECT_ID('tempdb..#CountInfo') IS NOT NULL DROP TABLE #CountInfo;[/code]I am trying to get this outcome...AAPL -0.68 1 day Change -15.2 3 day change -16.17 6 day changeMSFT 50.32 1 day Change 62.8 3 day change 34.83 6 day change1 day change is AAPL is -0.68 is [2091104] 534.80 less [20091103] 535.48 = -0.683 day change is AAPL is -15.2 is [2091104] 534.80 less [20091030] 550.00 = -15.2Any ideas how to do this?:-) |
| Count how many times contains a single value Posted: 11 Apr 2013 06:13 AM PDT Hello everyone, I need your help.This is my table:[code]+------+----+-----------------------+| Att | q | x |+------+----+-----------------------+| Att | 9 | 5,4,4,4,5,3,5,4,5 || Lav | 3 | 5,5,3 || Pred | 11 | 4,5,5,5,5,5,5,4,5,5,4 || RGdM | 3 | 5,3,5 |+------+----+-----------------------+[/code]I need count how many times contains a single value, I mean:The row *Att* contains 4 times the value 5 and 4 times the value 4 and 1 time the value 3, etc.Can you help me?Thank you in advance. |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
An unexpected error has occured.
I'm trying to install Adventureworks sample dB and i get an error.
I successfully downloaded the mdf file however when attach, add and click OK from SSMS I get the error:
Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\AdventureWorks2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
sqlserversamples.codeplex.com
CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG;
This is the error I get:
Msg 5172, Level 16, State 15, Line 1
The header for file 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
Thanks,Dan
CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG;
This is the error I get:
Msg 5172, Level 16, State 15, Line 1
The header for file 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
Thanks,Dan
sqlserversamples.codeplex.com
Eileen,
Your response simply quotes BOL. I know what the valid values are, and in some way that's the point. In that BOL article, it says,
"If the specified LSN range does not fall within the change tracking timeline for the capture instance, the function returns error 208 ("An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes."). "
However, in the above code fragment, I am indeed sending in a value that is not in the correct range (a null for the second parameter), and I'm not getting error 208. Instead, I'm getting an empty result set. I expect error 208, per BOL, and I'm not getting it.
Thanks,
Jason
social.technet.microsoft.com/Forums
