This is probably an easy solution for some of you seasoned DBA Vets but here is my problem.
I have to take production data and scramble certain sensitive columns such as SSN, DOB, Address, First Name so that our Management team can use it as demo material. Is there a quick solution to this issue?
Thanks,
JC
We have a bunch of data that includes things like DNS names and host names. For testing purposes, we want to give people access to our data but we do not want them to see the real names of the items I mentioned prior. What I would like to do is a letter replacement for each character in the name, thus A could become T, B could become U, etc. It's not a big deal if the end user can unscrable these, they probably won't anyways and it's not a big deal if they do, our client just wants something to make it not so obvious.
Is this possible to do through a SQL query and if so, can you give my pointers on how to accomplish this?
Otherwise I will probably use a VB solution, but it would be similar if I could just write a SQL query that I could send to anyone that needs to do this.
I'm looking for a way to "secure" a column of MONEY values. The idea is to hide the value without resorting to actual SQL encryption services that would require converting the MONEY to a VARBINARY. In short, I want to keep the column type as MONEY, but I want to scramble the value, so its still a valid MONEY type.
The goal is to have a pair of UDF's SCRAMBLE() and UNSCRAMBLE() that work on MONEY.
I've done some searching, but have not found anything along this topic.
Does anyone have any ideas on how to do this?
I'd like to move data from my prod. env. to my dev. env. The data in dev would be replaced by the prod one. I cannot do a detach/attach or backup and restore due to some already existing dev objects located in the dev. env.
Any lead on how I can do this?
Thanks Marcel
Hi all, I've been assigned a task of refreshing data from theproduction env to development env.what i got is a backup file of a db in the prod env, i now need tomake that into the development env.I can restore it to the dev env no problem, but the warnings i got arethat the tables owner in prod and dev env need to be different, thatis, owner is A in prod env and owner is B in dev env.So I need to:1) restore the db in dev env2) change table owner from A to B3) change related triggers4) change related viewsCan anyone suggest me an approach that is most efficient?Thanks a lot.
View 2 Replies View RelatedHello All,I have been searching for a published document for Best Practicesconcerning access levels based on roles. Should developers have morethan (if at all) select level access to production data? If Iunderstand (from multiple postings) that it is best to have:1. Development (developers have extensive access levels)2. Test (developers have restriced access levels)and3. Production (developers have none or select level access)Our environment and budget only allows for items 1 and 3.If any body could point me to a document from a 'reputable' source, Iwould greatly appreciate it.TIABill
View 3 Replies View Related
Hi All,
I have to delete 135 Million records from our production server, keeping only last three months data & the table doesnot have any index.
Can you please suggest the best possible approach to perform this activity.
Approach i am thinking :-
1)I will rename the existing table & create a new table with the same name (By this my application wont be interrupted)
2)then i am planning to create a nonclustered index on the date column sort order desc so that i can get the last three months data.
3)Once the index is created i can transfer the required records to the new table created in step 1.
Please sugeest your valuable feedback or suggestions to perform the task ASAP.
Thanks
Here is my requirement.
There is a production database which has ever increasing data. For testing purposes though, I would like to build a test database with exactly the same schema but only a subset of data copied from the production database . I'll specify the criteria (something like a where clause in select query) for copying the data from the production database.
Is there a tool that anyone has come across to do this job ?
I am trying to insert data from staging table to production table. In the staging table I only have period or date but no primary key.
This is my staging table
Create stagingtable(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)
Create Production(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)
I get this every month. What can I do to make sure only unique record are loaded into production table with no duplicate from previous month.
Hi, we are using sql 2000, what would be the good way to validate different databases current data with seconday server's data after logshipping applied? thank you
View 3 Replies View RelatedHello,
I am trying to refresh a test database with data from a production database. Both database structures are identical, e.g. constraints, stored procs, PK, etc. I am trying to create a package in SSIS that accomplishes this task and I am having extensive problems. The import export wizard is out of the question because the constaints are not carried over, plus when I try to refresh the data using the import export wizard, it fails on 1 specific table because of a column in that table named "Error code". I think "Error code" is a micrsoft keyword, so it fails on this column. Does anyone know a workaround that I can do to accomplish this simple task, that could be completed in minutes using DTS. I understand that SSIS is not as straight forward as DTS, but this task is something that DBA's do on a regular basis and therefore should not be this difficult.
Any help would be appreciated,
David
I have question regarding SQL Transactional Replication methodology
1. Let's say successfully created SQL Transactional Replication and running / transferring data from publisher to subscriber
2. Now one day the source production / publisher SQL Server is down and the remaining DR SQL Server is up (subscriber)
3. Next day, we fixed and bring up the production / publisher SQL Transactional Replication server, then how can we sync back all existing data records from subscriber into publisher side ?
(SQL 2012 Standard)
I have a database with one 320 .mdf file and one 200GB .ndf file.
I would like to reconfigure my database to have four 200GB .mdf files. How do I get from here to there?
I am debugging one of our programs and ran the fix in Test. I would liketo compare table 1 between Production and Test. I want the query to outputcolumn 1 if Production <> Test output.What is the best way to achieve this?jeff--Message posted via http://www.sqlmonster.com
View 2 Replies View RelatedI am in problem that I have delete data in my production table.
Now how to retrieve it?
I have only Yesterdays Full and Transaction Backup files.
I used the following query for deleting all data.
delete from t1;
I setup data collection on a production server to capture growth rates.
When I run the dis usage report, it shows a daily growth rate of over 500 megs. This seems excessive to me.
As a troubleshooting step I then ran sp_space used and got these results:
database_namedatabase_sizeunallocated space
rgc_prod 273442.63 MB3648.48 MB
reserved data index_size unused
265345488 KB164385384 KB99826072 KB1134032 KB
What should my next steps be to try and determine why there is so much growth? And isn't the index size rather large?
I built a SSIS(writing out to a flat file ) in 32 bit machine and it woks fine . But however when I deploy to the produciton server(64 bit) the SSIS writes out garbage data . After some research I found out that the problem with the 32 bit OS and 64 bit OS problem.What is my next step. Am I out of luck that now I will have to redesing the SSIS in 64 bit?
View 5 Replies View RelatedThe following statement fails when using SQL Analyzer under sa but works on all of our development and staging server. All are SQL Server 2005 SP1. We upgraded production over the weekend from SQL Server 2000, creating a new instance machinenameSQL2005.
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:data est.xls',
'SELECT * FROM [Sheet1$]')
The error we are getting only in prodcution is:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Any help seriously appreciated,
Tronn
I have to install MDS on a production server without testing on test server (there is none test/dev server) On the production server each day are rendering SSRS reports which cannot be interrupted.
What risk is by installing MDS on a production server, (the SSRS, SSIS and engine may not go down,well can for some hours)Â SQL2012Enterprise.
What do I have to do first, steps taken, to install as save as possible for the current running BI environment?
We are setting up a new Reporting Services 2005 enterprise reporting tier that will support multiple developers, applications, and end users. We will have mirrored environments including development, test, and production each with their own database cluster, and reporting server.
We have multiple report developers who share a single Visual Studio solution which is saved in SourceSafe and is setup to have separate report projects for each business unit in the orgainzation. Each report project is mapped to a specific deployment folder matching the business unit. Using the Visual Studio Configuration Manager, we can simply flip to the envirnoment we want to deploy to and the reports are published to the correct environment and folder structure.
My problem lies with the common data sources. We are using a single master Common Data Sources folder to hold all of the data sources. The trick is that each and every reporting folder seems to have to have it's own copy of the data source in visual studio. There does not seem to be an easy way to change the data sources for the reports when you publish to various environment, i.e. development, test, production etc.
Ideally, we would have a single project for the common data sources that all reporting projects and associated folders would map to, and we would have a way to associate the appropriate data source for each environment when we deploy.
I'm looling for best practices on how to setup data sources for development and deployment in an enterprise environment that uses Visual Studio to develop and publish reports. We have 3 environments, and 6 data sources per environment and about 20 reporting folder / project in Visual Studio. That's 360 changes that have to be manged when deploying reports. Is there a best practices way to do this?
There has got to be a better way? Can anyone give me some insite into how to set this up?
Thanks!
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies View RelatedWe have both a production SQL 7 server, QA, and Development. From time to time, I want to move just the data from the production server to the other 2 servers without modifing the objects that may have been changed such as stored procedures and rights. Is there a way using the SQL tools provided that we can just move the data. Becuase also what happens is that the rights to the objects change which means my developers no longer have access to the tables for selects in QA since the changes where overwritten by production where they do not have the rights.
Thanks
Ricky Kelley
Hi,
Is there any tool available to migrate the data from the SQL Server test database to SQL Server production database. Data Migration should be based on a condition which can be given as an input for a table by the user. The dependant tables also should be migrated based on the given condition. i.e data subsetting based on the matching conditions.
Ex : Salary > 2000
The rows of the table which matches the condition alone need to be migrated for the corresponding table. Also its dependant table's rows should be migrated based on the given condition. Please help me with a tool which can automate this.
Thanks,
MiraJ
How easy it is to deploy a solution that uses SQL Express on Development, but a MS SQL database on the production server (host)I am using the membership provider of ASP.net 2.0 Many thanks.
View 2 Replies View RelatedSomeone please explain what is a Production Database. Is it a database to which data is keyed in from many data-entry operator terminals?
TIA
Where are all of the Production DBAs?
Is there such a thing as a Production DBA in the SQL Server world or is this an outdated concept? Is there a market for people who specialize in database administration as distinct from software development or general network admin? I believe there are such people in the Oracle world, is the SQL Server world different in this regard? Can anyone give me suggestions about how to go about filling such a position?
I am a New York City area employer seeking to fill a position that I think would be ideal for a DBA with 3-5 years of experience in a production environment. The salary would be somewhere between $50K and $60K.
The position involves administering a SQL Server cluster and SAN running our core business application, plus several ancillary SQL Server installations. The application that we are implementing is a third-party product, so no real development is being done at our site. It is, however, fairly complex and requires customization and we do have significant reporting and EDI requirements, so strong T-SQL skills are required.
We are a medium-sized operation, with 900 employees and an eight person IT Dept; the organization’s main focus is insurance not IT, so candidates with a lot of experience might not find it challenging enough, but I think it would be an excellent opportunity for a candidate who might be stuck in the back of a large IT Dept. who wants to move up to a more visible position in a smaller organization.
I would think that there would be a lot of people in this category and, indeed, we have received a lot of resumes, but few of them are from the type of candidate that I had in mind. Most of the resumes are from people whose main focus is development or, if they have had experience in a production environment, have had 10+ years and are seeking salaries way beyond what we can pay. As for the former, I would not automatically reject someone whose prior experience was as a developer—and these skills would certainly be useful to us--I would just be worried that they wouldn’t be happy if they weren’t spending all of their time developing.
http://jobsearch.monster.com/getjob.asp?JobID=14475846&AVSDM=2002%2D04%2D04+16%3A29%3A00%2E000&CCD=my%2Emonster%2Ecom&JSD=jobsearch%2Emonster%2Ecom&HD=company%2Emonster%2Ecom&Logo=1
Hello !
I just deleted a stored procedure in order to replace it with a corrected version (DotNetNuke).
Now I had the following problem : When I try to create the SP with the following :
IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}GetUserRolesByUsername') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetUserRolesByUsername
GO
CREATE PROCEDURE dbo.GetUserRolesByUsername
@PortalId int,
@Username nvarchar(100),
@Rolename nvarchar(50)
AS
IF @UserName Is Null
BEGIN
SELECTR.*,
U.DisplayName As FullName,
UR.UserRoleID,
UR.UserID,
UR.EffectiveDate,
UR.ExpiryDate,
UR.IsTrialUsed
FROM{databaseOwner}{objectQualifier}UserRoles UR
INNER JOIN {databaseOwner}{objectQualifier}Users U ON UR.UserID = U.UserID
INNER JOIN {databaseOwner}{objectQualifier}Roles R ON R.RoleID = UR.RoleID
WHERE R.PortalId = @PortalId
AND (R.Rolename = @Rolename or @RoleName is NULL)
END
ELSE
BEGIN
IF @RoleName Is NULL
BEGIN
SELECTR.*,
U.DisplayName As FullName,
UR.UserRoleID,
UR.UserID,
UR.EffectiveDate,
UR.ExpiryDate,
UR.IsTrialUsed
FROM{databaseOwner}{objectQualifier}UserRoles UR
INNER JOIN {databaseOwner}{objectQualifier}Users U ON UR.UserID = U.UserID
INNER JOIN {databaseOwner}{objectQualifier}Roles R ON R.RoleID = UR.RoleID
WHERE R.PortalId = @PortalId
AND (U.Username = @Username or @Username is NULL)
END
ELSE
BEGIN
SELECTR.*,
U.DisplayName As FullName,
UR.UserRoleID,
UR.UserID,
UR.EffectiveDate,
UR.ExpiryDate,
UR.IsTrialUsed
FROM{databaseOwner}{objectQualifier}UserRoles UR
INNER JOIN {databaseOwner}{objectQualifier}Users U ON UR.UserID = U.UserID
INNER JOIN {databaseOwner}{objectQualifier}Roles R ON R.RoleID = UR.RoleID
WHERE R.PortalId = @PortalId
AND (R.Rolename = @Rolename or @RoleName is NULL)
AND (U.Username = @Username or @Username is NULL)
END
END
GO
I get the following error :
"Syntax error, permission violation, or other nonspecific error"
Thanks a lot for any advice !
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
I looked around microsoft.com and here, but I don't see a target date for the production version. Is that info posted anywhere?
Thanks,
Ned
We have developed a project with many SSIS packages. Now we are in a stage where we are deploying this into production.
The operaitons team has asked us what are the things they need to known and do to make sure that the production system of SSIS keeps running.
Can you help me in comming up with a detailed list which I can give to the operations/admin folks so that they can ensure that this project keeps running?
I would appriciate any help in this matter.
regards,
Abhishek.
I must release a new Db on 4/1 into an isolated small network (pier workgroup) and my choices are MSDE or Express. I'm new to both (MSDE yesterday, Express today). I am familiar with SQL Server and prefer using the tools with Express, but is it stable enough to use in this type of production environment?
View 5 Replies View RelatedHi,
I€™ve got an issue concerning setting the background color of a particular cell of my matrix.
This is how my report is designed and should be shown:
My Report
Public
Private
Total
Category
SubCategory
x
x
x
SubCategory
x
x
x
Total
x
x
X
The expression of the background color of my count cell is
=iif( InScope("SubCategory"),"Transparency","#c0ffc0")
Everything works well in development mode when I click on Preview, the report is shown as I want.
But In Production this is how my report is shown
My Report
Public
Private
Total
Category
SubCategory
x
x
x
SubCategory
x
x
x
Total
x
x
X
I don€™t understand why the Total column returns false for the InScope("SubCategory")," statement and that only when my report is deployed on the production server.
Thanks in advance
Hi all,
I have a asp .net 1.1 application running on the intranet which uses SQL Server 2000.
The application is in production and everytime I want to do some changes, i do the changes on my
development machine then I copy the application dll on the server.
The problem is that I'm using Stored Procedures for all my Select, Insert and Delete statements.
These stored procedures are live on the server so I can't do the modifications locally and test them then copy to the server.
How can I do modifications without affecting the production server and the users ???
thanks.
I will be hosting my website in the next few months and doing more testing and I was having trouble finding a place to host the site that allows enough space for my sqlserver2005 database. I am estimating that the database will be around 40-50GB. Most hosting companies only give about 500MB for the database. Does anyone know of a company that provides more space? Also, is SQL Express production ready, or is it just for developing applications? If it is production ready, is there any "major" performace impact in using it over the full version. The reason I am asking is because hosting companies only give 500MB for a database, but alot more space for the web page. If I can put the SQLExpress file in the App_Data folder, then I will have more space. Does this even sound like it will work? Opinions are appreciated!
View 9 Replies View Related