With the current web app that I am writing, I am starting to plan ahead to the scalability problems that I am planning on encountering with the number of users that I may have...
My hosting plan now allows for a 50MB SQL Server database, but, I know that will not last long, each user will be using 3-5MB each of the database, so I am going to outgrow my space fast.
Would looking into (until i have enough subscribers to get a dedicated host), SQL Hosting be a good idea? Atleast to start off with something like http://www.alentus.com/hosting/sqlserver.asp ?
But then again, would a SQL Database growing to large get bad? Within a few years, i expect to have at max 5,000 users, so that could grow to a 25GB database... with millions of rows.
Would breaking it up into smaller databases for each N amount of users be a wise idea? Or would it not really matter?
A few years ago I had a problem(think it was 6.5 might have been 7) that was causing weird errors. We found out that the write-ahead cache on our RAID controller was causing problems the way sql server utilizes it's write process with the transaction logs. Now have another problem that is similar on sql 2000 (we have the db server attached to a dell SAN). Has anyone encountered this type of problem with sql 2000 and a RAID or SAN cache?
While a stored procedure was running DBCCs against a specific database, the following error was encountered. 'WARNING: No read-ahead slots available...' The SQL Server service hung upon encountering this error and would not respond and the box had to be rebooted. I re-configured the 'RA worker thread' setting from 3 to 8 as a preliminary action. I need you to find out if this setting is 'auto-managed' within SQL Server 7.0, or whether this is something we will need to watch continuously (we had another instance of this failure about 1.5 months ago). Also what else can be done to avoid this problem in the future.
Hello, We are establishing a database server for our product suite containg 14 products- these products is basically intended to be used by CAs and Accountants.
my databaseserver will have 14 databases- 4 are very small, 4 are complex and 6 are Very complex- there is a close intraction between all these databases. at any time there will be around 500 users using these products but since these products are seasonal there is a chance that all 500 users are connected to one database only.(but there is also a possiblity that load is equally distributed on all the databases) My product will run on intranat.
My requirement is: 1. What is best server configuration for hosting such type of database.(sqlserver 2005) 2. How many concurrent users sqlserver supports(MSaccess supports 15 concurrent users) 3. I want that if user has license for 10 users my Database should bear load of only 10 user.
HiI joined a project where 100,000 rows were added everyday. Now due toadditional customers the expectation is 2 million reocrds/day ie 10 GB worthof textfiles. We have to estimate the hard disk, memory, # of CPUs etc.Wewill have one yearworth of data in the db. Rest will be in tapes etc.We will be using WIN2000, SQL Server2000.- Any comparable server sizing willbe appreciated.1. Tohandle every day load, I thought that we will have a table for each day(pre created in the database )and have a view with union all selectingfromall these 365 tables. (This is the only way to partition in MSSQL Serverright?).2. The requirement is to populate datawarehouse tables with all the data.However there will be only inserts mostly but there can be updates too whichhappenned in the past 12 days.Hence we have to use the data from the last12 days and massage it etc and populate into datawarehouse tables.How can I do this so that I will have the datawarehouse tables with n-12days of data and I will alwys add the last 12 days data to it.Do you have any suggestions?Ragu
I have been working on SQL Server Capacity Planning for a few weeks now and have gathered a lot of materials, but non of thes materials contain recommended best practices on SQL Server capacity planning and also they do not contain operational guidelines.
I would be glad if anyone can recommend a website or book that contain information on SQL Server Capacity Planning Best Practices.
I was browsing Microsoft's SQL Server site, looking forsome details about SQL Server 2005. Didn't find whatI was looking for...I'm thinking about moving an existing SQL Server 2000workload to a new box, using SQL Server 2005, andmaybe the 64-bit version.My questions are:1. What is the current target date for release of SQL Server 2005?Will 64-bit ship when 32-bit ships?2. Will 64-bit SQL Server 2005 require a special versionof Windows Server 2003 (e.g. Windows Server 2003 Enterprise x64)?Will it work with both Intel and AMD processors?3. How many CPUs, and how much memory, will be supported bySQL Server 2005, 32-bit and 64-bit, on each OS that can runSQL Server 2005.I'm looking for a chart here, something like the chart onpage 117 of Kalen Delaney's "Inside SQL Server 2000" book.SQL Server 2005 SQL Server 2005Feature Enterprise 32-bit Enterprise 64-bit------------------- ----------------- -----------------CPUs supportedWin Srvr 2003:Win Srvr 2003 Adv:Win Srvr 2003 Ent x64:Physical memorysupportedWin Srvr 2003:Win Srvr 2003 Adv:Win Srvr 2003 Ent x64:Has Microsoft published this info, and I just can find it?
We have a project already developed iusing VS 2003 Enterprise architect edition using sql server 2000(have lots of table and stored procs etc.) Now planning to switch to Sql server 2005 are there any issues associated with the switch.
I mean front end programming wise asp.net and vb.net front end codes do they work straight away or need to make any changes to all the front end codes, we are using datasets, data readers calling stored procs and also using lot of hash tables and XML object to make bulk loads to sql server via sqlxml3.0 sp3. Please help thank you very much for the information.
I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X: Resource: 100 Starting date: 2012-03-01 (from this date the schedule will be effective) Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30 Record 2: same but daynumber is 2 Record 3: same but daynumber is 3 etc. ... Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00 Record 9: same as record 2 but daynumber is 9. etc. ... Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber) 2014-05-02 = day 5 2014-05-03 = day 6 ... 2014-05-10 = day 13 2014-05-11 = day 14 2014-05-12 = day 1 2014-05-13 = day 2 2014-05-14 = day 3 ... 2014-05-24 = day 13 2014-05-25 = day 14 2014-05-26 = day 1 2014-05-27 = day 2 ... 2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
I was told that my company is thinking about inplementing a "backup" server for out content loading server (aka load server)... this server loads files from several companies and then replicates the data out to our production environment to be seen. This server has serveral DTS/IS packages that do all this data movement and manipulation... I am not too up to date on technology for what they are asking so I thought I would ask here for some recommendations to head in the right direction...
I am not sure if clustering will work or maybe mirroring the database...
The server now is a publisher so the backup server would have to pick up if the #1 failed and then give command back to #1 once it is good... this is the part I have no idea about...
The #2 server would not have to really do anything but keep its data up to date unless #1 fails so you could say it would be fine to be a passive server...
I am planning to install SP3, anyone have suggestion before installation what are the precaution I need to take. please post your comments. Thanks in advance.
hi, can any one tell me what is the meaning of capacity planning and where do I get info about it,and how does this help me in sql server admin role thanks
my company wants implements transactional replication. We have three servers one in chicago(production) second server in chicago, third server in kansas city.Can give me plan for implementing the replication.
We are planning for a new SQLSERVER for one of our application. The database will receive around 58 million events/year is roughly 160,000 messages/day
Making the assumption that an average message size is 2K. The messages comes to database through edi processes.
Any suggestion about database server configuration, and any other steps that need to be take care of before even thinking about creating a database??
I'm very new to SQL and am learning via books and whilst on the job. I'm putting together my logical design at the moment and was wondering if anyone could suggest any websites that might give me an insight into the best way to go about this in an effective manner.
HelloBased on three tables (Projects, Tasks and UserCalender) I would liketo work out the total amount of available resources (UserCalendertable contains a entry for each user for each day, day being 7.5hours) and total required effort (sum of Tasks.EstimateLikley) splitover 12 months.For example:Jan:Available Resources: (4 Users, 7.5 hours per day, 5 working days perweek, 23 Working days in Jan) = (23 x 4) = (92 * 7.5) = 690 AvailableHoursRequired Resources:Project Start Date: 1/1/2007Project End Date: 1/6/2007Total Required effort (Sum of Tasks.Hours for above project): 500Hours Average over 6 months = 83.33 Hours per month, so in Jan I needto deduct 83.33 from 600 = 516.67 Hours.etcHow could I do this, I have tried several ways but finding it hard.Thanks
I've read a few posts on the stored procedure vs dynamic sql debate. I ran a few performance test for myself and it appears to be a wash.
Given that, I'm leaning toward dynamic sql mostly because it would mean one fewer place to have things.
But, before we go that route we wanted to ask the question:
Is there any compelling reason why we shouldn't abandon all of our stored procs and just write the SQL inside inside our functions in our business layer (essentially our data access layer)?
Or, is it just preference these days?
I was leaning toward procs, but I have to admit it would be nice not to have to keep up with all of them per all of our functions that call them.
hallo we have a sql2000 in the headquarter, and 3 new remote branches opening in few weeks with no sql servers installed yet. We plan to setup a merge replica. The headquarter will have the highest activity on the replicated tables. Shall we use all SQL2000 in the remote branches? Or could we use SQL2005, using a 2005 as a publisher and distributor? TIA
Dear Experts, now i was allotted as a complete DBA for one client and i've to take care from the creation itself.
we've understood the data will be around 20 GB initially, and from there onwards, 1GB data will grow in the database. total number of tables are around 1400. and we have primary key on each table.
Now you Experts, please guide me that how can i take the Fragmentation levels for best performance? or i need to give you more information regarding that.....
I'm cleaning data which involves updating ~12 million rows with threedifferent models, progressively. First clean values using the modelwith finest granularity, then the remainder with the next model,finally what's left using the last model. The first model sets ~1/2 ofthe rows, the second ~1/4, the third ~1/5, and the remaining 5% don'tget updated.It's something like this:UPDATE t SET value=value*m.AdjustmentFactor, updateFlag='updated'FROM Table t JOIN Models m ON ....WHERE m.ModelID='first model' AND t.updateFlag IS NULLStart with 'first model' then 'second model' etc.I'm wondering what happens if I submit all three queries together, oras three separate submissions, waiting for the one before to complete.If I do them all as one group, the query planner might plan for thesecond and third updates based on the initial distribution of values.However, the first update removes half of the rows from consideration,so it seems to me a new plan should be prepared for the second query,based on the distribution at that time. If I highlight the queries inQuery Analyzer and execute, are all three plans created at thebeginning? Does putting GO between them (which I do) make anydifference?This is SQL Server 2000.Thanks,Jim
I've done a fair amount of hardware planning for the SQL Server Database Engine. Now I'm doing so for SSRS which I don't have as much experience with. I haven't been able to find much information, especially around RAM capacity and have a few questions. Any responses or pointers to resources are appreciated.
Scenario: 2 load-balanced SSRS 2005 App servers with an independent back-end SQL DB for the config database. Total userbase is about 2000 (not simultaneous), and I'm still trying to get statistics for per hour requests. May also install multiple SSRS instances on these boxes with one of them in Sharepoint Integrated mode (don't anticipate high volume here). Load-balancing is for both performance and availability goals.
I'm looking at the SSRS App servers only and am analyzing amount of RAM, 32 vs 64 bit, and perhaps CPU core count.
1. Does 32-bit Enterprise SSRS benefit from more than 4 GB on a server, using AWE or other memory extension? Or is anything > 4 GB unused at 32 bit? 2. Are there material benefits to going to 64-bit OS / SSRS and increasing memory above 4 GB? (does IIS handle this?), Perhaps to 8 or 16 GB. 3. The boxes will be 2-way, dual core, with possibility of going to quad core. Is increasing core count a better use of budget than increasing RAM above 4 GB?
I've read the white paper, "Planning for Scalability and Performance with Reporting Services" (http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx) which briefly (but incompletely) discusses 32 vs 64 bit, and really doesn't touch on RAM at all.
I cannot think of any reason, in our environment, why I would recover the model database. Change framework has all databases coming from DEV & QA before landing on PROD. We have never used the model database as framework of new databases either.
So, if I discontinued backup of the database, what is my recovery method if it become corrupt? Since mine is not used, can I simply copy it from another server?
I am in the process of formulating recommendations with respect to the purchase of additional storage for our current SQL 2012 SharePoint (2013) instance. My recommendation is to purchase separate storage (i.e, 15k disks) for the TempDB and Tlogs respectively (two sets of raid 10 disks). Currently, this server is hosting several instances, including SP, using two arrays (one for database and the other for Tlogs).
I am attempting to find information/recommendations on how to go about projecting the amount of storage for each of these while factoring in for growth.
Additional Details:
how to best formulate a reasonable estimate. Our largest content database belongs to IT and is currently ~80GB. That said, this is currently an outlier. The remaining content databases are less than 10GB (most are less than 2-3 GB). However, SharePoint will be used for digital document imaging in addition to, eventually, replacing file shares as our primary document storage medium once we roll it out.
Our current tempDB is ~400MB, but the instance was recently started a few days ago, as we had to failover to our backup server for hardware maintenance. I do not have any historical data on TempDB growth at this time. Also, I don't know how useful this would be given we have not fully deployed yet.
Is there any way to copy my Data of 2015 to the Planning/Forecasting Value of 2016?
My question is based on that we use INFOR ION BI right now and there we can just add an Button in our reports wich physically copies the value from one year to the next year based on some other rules in the cube.
Now I need to make this example work with SSAS and Excel PivotTables but I cant figure out how.
I have absolutely no clue where and how to accomplish it. Do I use Calculations, do I use Actions, do i make it in the Dataview, Cube or directly in Excel?
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
