i was pondering getting a MCDBA certification. i want to learn everything about the OS i'd use, so i just wanted to get some feedback on whether to go with NT or server 2003, etc. and anyone here recommend even getting or not bothering with the certification?
View 2 RepliesI have a couple of years of light experience with SQL server. I'd like to start studying to take the SQL 2000 exams. I have a good test environment set up and I'm reading through the Books Online. Can anyone recommend a book or books that might be helpful for me? My end goal here is to pass the test in the near future, but I want to really learn SQL rather than just learn to pass the test.
Thanks,
Allie
Hi,
Could anyone recommend a good book on the SQL-Server, please? I need to understand how to retrieve data with select statements and commands. It is urgent!
TIA
Hi,
I'm new to SSIS.
Is there any recommended reading material that you suggest to learn more how SSIS works?
Thank you!
Good morning,
Say you have an application for a Retail operations. You have an XP computer in the back that hosts the SQL Server 2005 database and computers out front running transactions....
What would all of you recommend as the maximum amount of registers that could connect to the back database at one time before you start to notice slow performance? 10 computers? 20 computers? 30 computers? 100??
Specs of XP cpu will be:
Core 2 Duo 3.0 ghz
3 MB Ram
I have a 6.5 database running on NT 4.0 that is approximately 43GB in used space. I am making a case to management for some sort of upgrade to the whole system.
What sizes of 6.5 databases would anyone consider more "risky"? Is 43GB large for 6.5 (my thought is that it is)?
Thanks,
Kurt Symanzik
Handleman Company
Let's say for instance that you have a group of tables that stores address information for different groups (i.e. Doctors, Patients, Providers, etc.) Would it be better to create each table to store the address information or create an Address table that would store this information with an Address type and a link back to each table?
I prefer the second choice, but am having a hard time convincing other devlopers to follow this route. Maybe if I have some input from a more experienced users group I can stress my point a little more effectively. Thanks in advance for any input you can provide.
I need to import a CSV file with a few million records and 50 fields into a table. Only 1 column in the file needs to be transformed and a second column needs to be checked for data validity (e.g. don't want to let someone pass in 'CA' for an integer field.). Two approaches come to mind:
1. Use SSIS to read the file directly into the table, then apply t-sql to do a mass update to the single field that needs to be transformed. (with this approach it is not clear how to check the data valdity in each row via t-sql, though).
2. Use SSIS to import the file, 1 line at a time, transforming the data and checking its validity.as it goes. I suspect this approach will be much slower than that in 1) but I haven't tried it yet.
Which way do you think would be the fastest?
TIA,
barkingdog
Can somebody recoommend me books for sql server 2005. I am interested specially in CLR inside sql and Business Intelligence.
View 4 Replies View Related
In a situation where one may have a single master SQL Server that ultimately needs to communicate information back down to 1000's of downstream servers, what is the recommended architectural approach?
It doesn't feel right to have to add 1K-5K routes to the master SQL Server. Is there a way to have the dowstream servers "broadcast" their existence to the master, so that new servers can be added and updates can happen seamlessly? Does this fall into a pub-sub scenario or is there a better way? And, if so, how to ensure an open conversation (so that one server doesn't miss information that all the other servers received)? Should the master dynamically create routes or better to rely on an open conversation initiated by the downstream server?
I know I've seen documentation on this but I can't find it at the time. What's the recommended file locations for a SQL install.. System and Data on a RAID drive and logs on a separate drive that's mirrored..? Oh and if anyone has links to this info let me know also.
Thx!
Hi,
What are current thoughts about who should own a Database?
I see 3 possibilities:
1. The DOMAINAdministrator (person wo starts up the Server at Bootup)
2. 'sa', or
3. a person/user closely tied to the database.
reasons for each?
Thanks for your opinions.
MichaelG
We are replicating data from server1 to server2. We expect the connection between servers to be reliable, but we can not always guarantee uptime on both ends. We do not need real-time data access on server2. What type of replication would be best? The downside we see to snapshot is that the data will be growing over time and that means the amount replicated will continue to grow. Can we setup transaction replication and then schedule the updates so it only replicates transactions since the last update? Does this present any problems if the connection is lost at any time between the servers? At this time, we will not be making any changes to the data on server2 so it does not need to be updated on server1.
View 7 Replies View RelatedHi,
I have a database app that deploys with sql 2005 express to each end-user. I would like to install sql 2005 express using Windows Authentication only. In this case, should I bother to set an sa password? And if I do set the sa password, how would I go about making sure that the sa password is different for every installation of sql express? Would it be recommended to save every end-user's sa password (possibly tens of thousands of passwords) just in case sql maintenance needs to be done on their computer? Any help would be greatly appreciated. Thanks!
I'm reading a book 'Professional SQL Server 2000 Programming' by RobertVieirathere is a recommendation: "stay away from building views based onviews"Why? What's so wrong with nested views?
View 2 Replies View RelatedI have been using the Microsoft Oracle Provider (MSDAORA) up until I needed to work with CLOB data types in Oracle. As much as I hate to switch providers at this phase in the project, I can't use MSDAORA due to the CLOB limitation.
So, what other providers are available?
I know that there is a native Oracle provider (OraOLEDB.Oracle.1) that is supported in SSIS. Does anyone have any comments on this?
Are there other options for Oracle?
Any comments, feedback, etc appreciated.
Thanks,
Rick
I was wondering what everyones preferred way to install a database in an automated fashion is.. IE:
You have a webapp. It sdriven by SQL Server. You need to prompt the user for a server, username, password, and database. Once you have those, you execute thge scripts against the DB.
I've been using osql.exe. but heres the situation. The installer may be run from a system, which does not have the sql server client tools installed. Which will be a problem.
So, given the situation that the machine the application is being installed on, does not have the client tools installed. How would YOU execute the provided SQL script against a remote server.
Hi,Our company is an independent Voice applications solution provider withnumber clients using our suite. We have a CT application suite which isrunning with Application Server and SQL Server 7 / 2000 as DB Enginesat the back end.The SQL server has two databases configured:Logging Database - Massive updates every second, the data growsrapidly,Configuration Database - Generally small-sized and updatedoccasionally.Now we want to have the reslience implemented on the server. We have tosynchronize the two databases 'real-timely' and in 'efficient'manner, so that if Primary server or its Databases gets unavailable,the users are seamlessly switched over to the Secondry server that willhave its own set of data updated and well synchronized.Typically, it can be explained as follows:1. We will have 2 database servers A - Primary (acting as publisher)and B - Secondary (acting as subscriber). Our application will beinitially connected to A.2. When A becomes unavailable (for whatever reason), the applicationwill fail-over to B.3. All the users will be switched to server B and the updates are beingdone accordingly without being replicated on Server A temporarily.4. When A is back on-line, A needs to be brought up-to-date with Bautomatically (In other words, I shouldn't have to manually export allthe data from B to A ).Our requirements are:- The system should support Bi-directional Synchronizationbetween both the servers for their set of databases (the logging andconfiguration).- There will be constant and heavy activity in LoggingDatabase, thus if one server gets down the data should be logged andmaintained as it is on second server and on fail-back no data-lossshould occur with minimum latency time.- There could be a scenario when a server fails-over for aweek's time, there will be constant logging each second! Once itfails-back the system should rapidly synchronize the data withoutnoticeable delay among the two server database sets.- The system should also work fine if certain amount ofrecords are purged over a time period.Our concern is, observing the above scenario, how any of your SQLserver replication strategy can help us achieve the requirements.ThanksJohn
View 4 Replies View RelatedI know the default data path on a SQL7 server is defined in theregistry key SQLDataPath. I want to be able to determine the defaultdata path in a VB.NET application on the local machine and remotemachines. Is using the Registry Class the best way to do that or isthere a SQL command that can tell me? I have read about xp_regread butI can't find it documented anywhere and I do not know what parameterlist it is expecting. I thought this path may be in an InformationSchema view, but I can't find it.Thanks for any help.
View 2 Replies View RelatedJust wondering which scenarios is suitable to use SQLCLR. Any kind of data access is not recommended I guess. Only things that cannot be easily done in TSQL should be done in SQLCLR but why? Can't those things be done in app layer itself? Scenarios recommended for SQL CLR:
- External data access like filesystem, registry etc
- Complex calculation
- Recursion without data access (this can be implemented with CTE for data access)
If data access with SQL CLR is not recommended why should CLR should be even used and logic reside in database layer.. it makes no sense to me. Any thoughts??
Some of our databases have many transactions (a million or more) a day. I have read that every so often I need to rebuild indexes, update statictics for all tables (however that is done), and shrink the transaction logs.
I'm confused by all this. What are the daily recommended database maintennace steps steps for database "health" and how can they be done?
TIA,
barkingdog.
I am trying to create a dimension table and I am pulling in data from two tables to create it. I need all records from table A, any records from table B that are not in table A, and I need to use the fields from B for those records that do match. What would be the best way to approach this, merge join + derived columns, union all + aggrigation? Any suggestions?
It seems like it's harder to do this in ssis rather then just doing it in the database.
I did a search (google and on the forums) and found a few suggestions here and there, but I'd like something more complete to follow as far as naming conventions are concerned.
I wrote my first DB based on MySQL/Ruby/Active Record type naming convention...
- plural table names
- all lower cased
- underscores between words
- "id" is auto incrementer for each table
- something+"_at" is for datetime fields
- something+"_on" is for date fields
- referencing the primary id in another table is "tablename (singular)" + "_id".
This worked great in Ruby/MySQL, but in C#/SQL Server, its an ambiguity nightmare! All of my "id" fields conflict and alot of my tables have "added_at" datetime fields and they all conflict with each other. Essentially, any field that's named the same in one table as in another conflict on joins.
For example: users post comments to stories submitted by users...
table = articles
field 1 = id
field 2 = title
field 3 = body
field 4 = user_id
table = comments
field 1 = id
field 2 = title
field 3 = body
field 4 = user_id
field 5 = article_id
Trying to join these two tables is an ambiguity nightmare but I'd like to not have to name every field uniquely or start adding table prefixes to them all...
I guess I just need some good suggestions or links to recommended table structure/naming conventions for SQL Server. Thanks in advance!
We have just started using SQL 2005 and released our first few projects to prodcution. We are currently using msdb storage for SSIS packages in production using the 'rely on server storage' for protection level and separating each subject areas by folders under msdb in the management studio.
However some of our DBA's feel that this is not the right approach and we should be storing as XML.
Anyone has any recommendation for either or considerations to be taken when deciding what storage to use?
Thanks!
Would anyone have a suggestion on how to setup a partner to partner NIC configuration for heartbeats/mirroring traffic? I've been told this is the recommended setup but have not found much on how to do it. We currently have a teamed NIC config for redundancy, but would like to have a separate set of NICs on each partner so that mirroring traffic is not interrupted by any regular network traffic.
We also have a witness running in full safety mode. Does this mean partnerA and partner B both need NICs with a crossover cable between them AND is it recommended for the witness to also have extra NICs to both partnerA and partnerB (w/ crossover cables)?
Any suggestions/help/links on properly configuring this would be appreciated.
what is the recommended data type i should use if i want to have a price field that can include "TBA". i can't use smallmoney i suppose, so i should use VARCHAR then validate the String with Visual Studio?
View 1 Replies View RelatedI have an app that will have up to 13 PC's. Each machine will be logging data to the 200X Express Server every 5 seconds. The data size sent each time will be around 1K each.
1. Is this within the limits of 200X Express?
2. At what point do you decide that Express is bottlenecked and Standard is needed?
Thanks for any help!
Ron Lindsey
As stated in the subject I have a situation where if database mirroring is employed for either manual or automatic failover, all the client (including web connections) connections use ODBC not ADO, or OLEDB etc... so what methods are recommended? Client side redirect is not available so I could not employe the "Data Source =A; Failover Partner=B..." option.
Right now the method employed (pre database mirroring and basically employing log shipping on SQL 2000) is to have a DNS alias for the ODBC connection so that if the server were to change in a failover situation the DNS record would have to be altered, so that all the client connections would not have to be reconfigured.
Regards,
Dominic Baines
What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:
CREATE DATABASE Dummy
ON
PRIMARY
( NAME = Dummy_data,
FILENAME = 'D:....DATADummy.mdf',
SIZE = 250MB,
FILEGROWTH = 25MB )
LOG ON
( NAME = Dummy_log,
FILENAME = 'D:....DATADummy_log.ldf',
SIZE = 50MB,
FILEGROWTH = 5MB ) ;
GO
Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.
View 5 Replies View RelatedHi All,
I would like to know, how the datetime will be stored in the sqlserver datetime column.
Because some time i am giving the date in dd/mm/yyyy and sometime mm/dd/yyyy.
while give the date in mm/dd/yyyy works fine but not in the another case. and also while i execute a query on query analyser it shows the datetime in
yyyy/mm/dd format.
So anyone can please tell me how the dates will be stored in the datetime column of sqlserver database?
Thanks in Advance.
Regards,
Dhanasekaran. G
I am currently running SQL Server 2000 Standard on my production system, and I am looking to upgrade the system to Windows 2000 Adv. Server. I would also like to upgrade SQL Server 2000 Standard to SQL Server 2000 Enterprise to utilize more than 2GB of memory. Can anyone tell me what is the best way to upgrade the system, and please provide some feedback on your experiences with the upgrade. Thanks in advance.
View 2 Replies View RelatedWe are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.
View 1 Replies View Related