Hey Gang, I got a question about authentication. I have a Just loaded SQL Server on my virtual box and loaded Microsofts bpa and microsoft security anlyzer. I get a funny reading. The security scan work but the bpa scan does not. I also look at the database that I can get access to and notice a new database schema. I was thinking if I remove this database will it have any affect on the present database.
View 1 RepliesWhen I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
I am hoping someone can give some advice on the following things:
I have read a few times about a data access layer in an n-tier application. I am assuming that this should be done
using sprocs. Is there an advantage of using sprocs instead of views ( in situations where the same thing could
be accomplished using either)? Will a sproc run faster than a view? Can any share any info?
Are sprocs best suited for data access and to enforce business rules?
I know SQL Server has reserved words that shouldn't be used. I am wondering what the best thing to do is
in the following situation? What is the best way to handle storing a customer or clients address? I am working from a book that shows the name of a column as "Address". I have found that with SQL Server 2005
Express that this is a reserved word(it is shown in blue in the query window). I want to keep my names short. I am trying to avoid a name like "StreetAddress". Is my book teaching bad habits?
...........................................thanks...........................................................
Greetings,
I'm developing a trading application in C# that processes streaming data that can be very heavy at times. Transactions are occuring, logging information is stored, etc., often at a very rapid pace. Up until recently, I had been storing all of this information in memory in DataSets -- upon a graceful exit, the application would call DataSet.WriteXml() to a file, and then next time the application was opened, it would consequently call ReadXml() to obtain its last state. This is all great in theory because it is super fast, there is negligible lag when I add a row to a DataTable that already has 12,000 rows (at a rate of 300 per second), however if the program were to crash, without a chance to write the data to file, then I'm screwed.
My solution is to have the various DataSets bound to a SQL Server database -- I've created strongly typed DataSets and TableAdapters to help aid this process. Because often I'm adding rows VERY quickly and in large numbers to these tables, having an INSERT command execute on the database for EVERY transaction is prohibitively slow.
What I would like is to have some mechanism in place where I only affect the local DataTables on the fly, and then occasionally make calls to TableAdapter.Update (on their respective TableAdapters) during slow periods (or lulls in the message traffic) so that any changes to the in-memory data is persisted on the database. I'm looking for general "best practices" in this regard -- nothing specific, just advice from people who have dealt with this type of application/environment before and might have some tips.
The first thing I thought about doing would be a relatively simple algorithm that, upon receiving a new transaction, sets a timer (for, say 500 ms). When this timer is triggered, it calls the Update command on the DataSet that was updated. If another update comes in before the 500 ms, it first checks to see if there's an active timer for this DataSet, if so, it cancels it, and sets a new timer for 500 ms. This way, if I have a very rapid set of transactions that all occur within a few ms of each other, it will not make any calls to the database during the "peak" of data -- only when there's a 500 ms gap will it make a call.
Does that seem like a viable approach?
Any help/direction is greatly appreciated.
Rick
Re: Best Practices (security): Should SQL Server (2005) *not* be installed on the same physical HD as the Windows OS (Server 2003 R2) ?
Hi,
We're setting up some new servers, and today I'm looking into best practices for the SQL Server Setup portion of it.
The servers have include 2 x 250G HD, and from what I've read, where IIS is concerned, it should not be installed on the drive that has the OS on it, for security reasons. I was wondering if the installation of SQL Server should be on the non-OS drive as well ?
Thanx,
Barry O'Neill
Hi,
I wanted know the Microsoft SQL Server Best Practices Analyzer tool feedback?.
Thanks,
Ravi
Does anybody have a link to either of these two documents. My company is getting ready to go through an audit and we need some firepower and to know what is expected. Any help with obtaining microsoft SQL Server 2005 best practices documents is appreciated.
-Kyle
Dear All,Please suggest some of the best practices for writing SQL serverstored procedures?I'm writing a business function (stored procedure), which callsmany-stored procedure one after another.I want this to be best optimized, so that speed can be very good.Suggestion in this regard will be appreciated.Thanks in advance,T.S.Negi
View 1 Replies View Related
Hello all,
I'm finding the documentation online to upgrading from 2000 to 2005 fairly poor.
What are the upgrading options and which one is the best option for a web server database.
Also what are best practices pre-upgrade, during upgrade and post-upgrade.
Cannot find much online.
Also since this question will be asked often might as well make it a sticky.
Thanks
I'm an experienced SQL Server and .NET developer, but I wanted to expand the way I look at things and see how other developers approach the situation I'm going to outline in this post. I'm going to be engineering a large, new project soon and I want to examine how I approach this and see if there is a better way.
I work in a small development group with two developers (myself and another). We pretty much wear all the design, testing ,and development hats during the course of a system's development. I had a discussion today with the other developer about creation of stored procedures.
I like to create small specific stored procedures for whatever I'm doing. I will usually have at least 4 stored procedures for each table; Insert, Delete, Update, and Select. Frequently I'll have more Select procedures for special cases. I do this for several reason. One I can get Visual Studio to generate the basic procedures for me and utilize them in a typed dataset. Secondly I can keep all my SQL code server side, and in small maintainable chunks. It is also fairly obvious what my stored procedures do from the name. The main drawback is that the list of stored procedures gets huge.
The developer I work with likes to create a single stored procedure for Insert, Update, and Deletes. Based on the passed primary key, the procedure determines what it should do. For example:
Code Snippet
CREATE PROCEDURE udp_users_processing
@key int output,
@name varchar(200),
@status int
AS
IF IsNull(@key,0)=0
BEGIN
INSERT INTO ut_users(key, name, status) VALUES (@key, @name, @status)
SET @key = SCOPE_IDENTITY()
END
ELSE
IF KEY > 0
UPDATE ut_users SET key = @key, name = @name, status = @status
ELSE
BEGIN
DELETE FROM ut_users WHERE key = @key
END
This has the advantage of being compact, but it has issues with VS.NET and designer support. Loss of designer support isn't a huge problem, but it can be handy to have. I'm also not certain how this approach would work when using typed dataset and the table adapter to do updates.
What is YOUR opinion? How would YOU approach this in your situations? Are there other alternatives that might work just as well?
Evening,
I am very well versed in the proper way to set up a SQL Server server prior to installation.
In this I mean, the proper process in placing your MDF, LDF and NDF(s) on seperate spindles/discs and also to place TempDB on its own spindle/disc and such.
There are numerous other points to cover in setting up the server based on memory, security, processor and such but I am sure you understand.
What I am looking for is the link(s) to the whitepapers discussing these Best Practices methodologies for pre-installation setup.
I looked on the Best Practices page but did not seem to find a doc that contains all the Best Practices that should be followed, if possible of course, in setting up a server prior to the SQL Server 2005 installation process.
Can anyone please point me to a link(s)/doc(s) that describe what I am looking for.
I need to pass this information down to other members of my team.
Thanks and have a great day!
what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.
i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?
thanks.
Jeff
I'm building a hosted website and I am using SQL 2005.
The DBA for the host has told me that i can not encrypt a symmetric key with a certificate, when using that symmetric key for encryption. As i read that this method provided optimum performance/ security for encrypting columns of data.
The DBA told me i can use a cert or a symmetric key for encryption.
I have searched for comparisons and found a blog entry by Laurentiu Cristofor comparing certs with asymmetric keys. Which leads me to believe that certs and asymm are very different than symmetric keys.
My question is which is the best choice in a hosted environment for column encryption, a cert or symmetric key.
Which is more secure? Does one offer a significant performance (dis)advantage?
TIA
I would like to know best practices for setting up my environment. To date, I've had everything running on a single server. That would include the database engine, SSIS, SSAS and SSRS. The box configuration is dual hyperthreaded 3.6GHz Xenon with 4GB of RAM on Windows Server 2003. I just received a much larger server and want to configure it to maximize our environment. The new box contains four 2.6GHz Quad Core processors with 16GB of RAM. I would like to know if I should split the ETL and database engine from SSAS and SSRS, or should this box have enough horse power to house it all and use my other box as a dev environment. Also, we are planning to purchase Performance Point 2007 primarily for PAS and Scorecard Manager so please take that into consideration as well. Any comments are greatly appreciated.
Thanks.
Microsoft recommends using Windows authentication instead of SQL Server authentication in SQL Server 2005 for improved security. What are the Microsoft best practices for implementing this? Will be helpful if someone also provides some links that talks about this....
View 5 Replies View RelatedHi everyone,
I wonder if somebody here could recommend a good article about MS Service Broker. I'm looking for some advice and tips in designing applications using SQL Service Broker, mainly QN. For instance, maintenance routines and common faulty scenarios I might find later when my solution is implemented. I have googled for a while but all I can find are recopied examples of QN.
Thanks
Hi I use data presentation controls like gridview, formsview in my application. In many of the webforms i also use multiple datasources mainly for the purpose of 2 way data binding for controls within data presentation controls.I am concerned about the performance issues this might cause as users using these pages increase.What is the likely performance impact ?Once the databind is done and values are populated in the respective controls, does the database connection of datasource control get closed, or is it open?What are the best practices while implementing datasource controls?
View 2 Replies View RelatedHi all. Thanks for taking the time to read this.
I'm looking for some documentation on SQL 2K Installation tips on a Windows 2000 Member Server platform as well as best practices for ongoing maintenance .
Real world experience as well as Microsoft propaganda are all welcome.
Thanks again.
Could some one give best SQL 2000 install practices such as -
1) SQL app, should this be in OS drive or not ( pros, cons )
2) Should OS be on Raid 1
etc
Thanks,
What are the best pactices to optimize performance accsiing an SQLServer DB ?
commands, mantenance plan...
Thanks
I am looking for some examples of how to manage DDL scripts amongvarious versions of a production db and development and testing. Ihave tried a few things in the past, and it always gets very muddledand cumbersome.I need to be able to build any version of the database from scratch,BUT I also need to maintain an upgrade path from any version to anylater version. So it is not enough to just maintain a master buildscript, but I don't want to maintain 2 different things (modify themaster build scripts AND create a new "ALTER" script for each versionchange).I thought I had seen an article somewhere that layed out a process formanaging this, but I can't find it now (I thought it was in SQL ServerMag). Does anybody know of this article or have a resource they couldpoint me to that outlines best practices in this area?Thanks,Jason Wood, DBA in training.
View 1 Replies View RelatedHi All,My question is what are the best practices for administering largeDBs. (My coworker is the DB administrator. I'm more of thedeveloper. But slowly being sucked in.) My main concern is that wehave some DBs that take approx 3 hrs a night just to rebuild theindexes. I know that with MSSQL 2000, I can use partitioned views tobreak out the table(s) into smaller databases and tables. But we alsohave an older server that runs MSSQL 7. Lastly how do you handledrive space issues? Do you spread out the DB across multiple MDFfiles on different drives? Thanks in advance.
View 1 Replies View RelatedPlease forgive me if I have overlooked a thread that answers this question, but I assure you that I have looked.
I would really appreciate a guide of sorts that would tell me the correct steps to take to properly secure a column in my database. I don't need specifics on how to do each step, I either have those already or can find them myself. In fact, I have already successfully encrypted and decrypted some data. I just want to make sure that I create the right keys and certificates and that I follow best-practices as far as backups and stuff is concerned.
Thanks,
Todd Sparks
Environment is SQL Server 2005 x64 Enterprise running under Windows Server 2003 x64 Enterprise with four processors and 16GB of ram.
I have 28 data copy routines I would like to add to a SSIS package. They use the Data Reader Source to an ODBC database (InterSystems Cache) and copy the table contents to a SQL2005 database for reporting needs. The data rows in these 28 routines range from only 100 rows to over 6 million rows depending on the table. I have tested these individually and they work fine. My question is, is it a good practice to have all of these routines in a single package or can I expect performance degragation?
I've got a table that has frequent updates to it. I want 100% change tracking on this table though, so we can rollback to any previous version, or just see any changes people make.
Is there a best practice for things like this? Currently, I'm using a trigger on UPDATE to take the previous values and store them in a history table. This keeps track of who changes what, and when. Plus the most recent data is seperate and more performant to access.
I've also heard about putting an 'IsActive' flag on the main table and any changes that are made just get marked as In-Active and a new record gets added.
Any input?
Thanks!
I am new to SSIS, but done alot of DTS 2000 development.
What is the concensus for developing SSIS packages? Do you just place objects and change the properties of each object, having multiple objects basically doing the same thing, with different properties? Or do you set object's properties and then change properties by code in scripts? Ie Execute SQL, setting connections and SQL Statement by code in a script? Is this even possible? With Microsoft OOP I assume this is possible.
Script> Set properties of ExecuteSQL > set flow to ExecuteSQL.
Thanks!
Is this this only way to do it in SSIS?
http://sqljunkies.com/WebLog/ashvinis/archive/2005/06/15/15829.aspx
For some reason I figured that SSIS would have this kind of stuff built into it, it seems a function that many would use.
Hi,
I wonder if anyone knows what would be the best case scenario for the property 'maxinsertcommitsize' for the sql destination task if I want to load 6m records into a target. Is the best setting 0 (try loading all in one batch) or should I choose a different value for example 1000000 per batch?
Thanks,
Marc
Hi,
i am newbie in ASP.net world. i am using 3 tier application architechture for my web based application. data base is sql server 2000. i have looked at object and sql datasource objects but i think they are not suitable for my requirements. so i am planning to directly use ado.net to access data from database.( i.e. creating connection, then creating commands n executing them)
now what i am looking for is the best known practices for the above task. i have following solutions in my mind please let me know if i am missing some or which could be the best aproach.
careate one class which will handle all the database requests so that all the pages and business objects request that class to to do all the db related stuff. (creating connection, command n execution)
Hey guys,
Little bit of a newbie question here...I have a database with about 20
or so tables in a relational model. I am now working on an output
scheme and had a quick question regarding best practices for
outputting. Would it be best to
1) Set up a view that basically joins all of these tables together, then bind a DataSet/DataTable to it and output as needed?
2) Setup individual views for each table and run through them?
Thanks for the help!
e...
I was writing a simple sign up class the other day and coded
a Register method with a SqlCommand and Parameters like this:
SqlCommand sqlCmd =
new SqlCommand("Regester", sqlConn);
sqlCmd.CommandType =
CommandType.StoredProcedure;
sqlCmd.Parameters.Add("@UserName", SqlDbType.VarChar, 55);
sqlCmd.Parameters["@UserName"].Value = userName;
sqlCmd.Parameters.Add("@Firstname", SqlDbType.VarChar, 55);
sqlCmd.Parameters["@Firstname"].Value = fName;
etc...
Seems pretty straight forward right? A senior coder writes his params like this
SqlParameter pUserName
= new SqlParameter("@UserName", SqlDbType.VarChar, 55);
pUserName .Value =
address2;
sqlCmd.Parameters.Add(pUserName );
SqlParameter pFName =
new SqlParameter("@FName", SqlDbType.VarChar, 55);
pFName .Value = city;
sqlCmd.Parameters.Add(pFName );
etc...
What is the benefit, if any, of creating a new instance of SqlParameter for
each value passed to the sp?
How could I easily test the code for speed and resource use besides tracing?
We are preparing to stand up a server with an HP tape library backup system using Data Protector software.
Our intention is to copy backup files from each of our db servers to the file system of the server with the tape library. There will be two tape rotation schemes where there will be a daily offsite tape and one with a monthly offsite rotation for less critical applications. Our systems group isn't happy about that configuration but are willing to implement what we want.
My questions are:
Is there any one out there using one of these and if so can you give me a 10,000 foot view of your process?
Addtionally, is this the best way to utilize this resource?
Any serious suggestions and comments are greatly welcomed and appreciated.
Sidney Ives
Database Administrator
Sentara Healthcare.
What is the best way to design your tables in cases where field values change?
Example:
CREATE TABLE Product (ProductID INT, Description VARCHAR(32), Price SMALLMONEY...);
CREATE TABLE Purchase (PurchaseID INT, ProductID INT, Quantity INT);
Since price obviously change over time, I was wondering what the is the best table schema to use to reflect these changes, while still remembering previous price values (like for generating reports on previous sales...)
is it better to include a "Price SMALLMONEY" field in the purchases table (which kind of de-normalizes it) or is it better to have a separate ProductPrice table that keeps track of changing prices like so:
CREATE TABLE ProductPrice (ProductID INT, Price SMALLMONEY, CreationDate DATETIME...);
and have the Purchase table reference the ProductPrice table instead of the products table?
I have used both methods in the past, but I was wanted to get other peoples' take on it.
Thanks